[mysql] Tip - group_concat, if/ifnull/case

1. group_concat(column_value)

mysql 에서 컬럼의 값을 , 단위로 수집하기

B_idx    job                shool          name 
------------------------------------------- 
  2        enginner          high          데이터1,데이터3

group_concat를 이용하면 된다. 

http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=190021

group_concat 안에서 order by와 seperator를 진행할 수 있다. 

http://blackbull.tistory.com/3

* 주의할점

mysql의 group_concat은 길이 제한과 관련된 설정정보가 따로 있다. (성능이슈가 있을수 있으니.. 대용량쪽에서는 유의해야 한다.)

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

•  group_concat_max_len

  Command-Line Format	--group_concat_max_len=#
  Option-File Format	group_concat_max_len
  System Variable Name	group_concat_max_len
  Variable Scope	Global, Session
  Dynamic Variable	Yes
  	Permitted Values
  	Platform Bit Size	32
  	Type	numeric
  	Default	1024
  	Range	4 .. 4294967295
  	Permitted Values
  	Platform Bit Size	64
  	Type	numeric
  	Default	1024
  	Range	4 .. 18446744073709547520

  The maximum permitted result length in bytes for the GROUP_CONCAT() function. The default is 1024.

• 
  

2. ifnull(column_value, default_value)

case

if

http://www.spatium.co.kr/languages/content.php?chno=5&bno=34

예)

select r.title, ifnull(l.hourly, -1) as hour, concat('\"', group_concat(l.errorcode ORDER BY l.errorcode SEPARATOR '","'), '\"') as errorstatus

From 

(select urlid, hour(FROM_UNIXTIME(date)) as hourly, errorcode 

from url_check_result 

where errorcode like 'e%' 

group by urlid, hourly, errorcode order by urlid, hourly, errorcode

) l

right join  

(SELECT  x.id, x.title, x.url 

FROM url_check_list as x, service_list as y

WHERE y.id = x.service_id and y.service_name = "googledoc" 

GROUP BY x.id) r

on l.urlid = r.id GROUP BY r.id, r.title, l.hourly  order by r.id,  l.hourly, l.errorcode