Oracle wm_concat -> mysql group_concat

##기존에 위와같은 종류의 함수를 사용해서 쿼리값을 한줄에 다 넣는게 있었는데 마리아DB에선 다른방법을 지원하기때문에 변환을 위해 찾아보았던 부분들이다##

필요에 의해 서로 다른 결과를 한줄로 합쳐서 보여줘야 할 경우가 있다.

 

전체 결과값을 가져와서 java 와 같은 프로그램 언어에서 for 문을 돌며 문자열을 붙여도 되긴 하지만

Select 쿼리를 던질때 결과값으로 합쳐져 있는 문자열을 받는게 더 편하다.

 

select * from test ;

 

type	name
fruit	수박
fruit	사과
fruit	바나나
fruit	사과

 

select type, group_concat(name) from test group by type ;

type	name
fruit	수박,사과,바나나,사과

 

 

group_concat을 기본적인 형태로 사용했을경우 문자열 사이에 쉼표(,)가 붙게 된다.

구분자를 변경하고 싶을때는 아래와 같이 SEPARATOR '구분자' 를 붙여 준다.

select type, group_concat(name separator '|') from test group by type ;

type	name
fruit	수박|사과|바나나|사과

 

합쳐지는 문자열에 중복되는 문자열을 제거 할때는 distinct 를 사용한다.

select type, group_concat(distinct name) from test group by type ;

 

type	name
fruit	수박,사과,바나나

 

 

문자열을 정렬하여 나타내고 싶으면 order by 를 이용한다.

select type, group_concat(distinct name order by name) from test group by type ;

 

type	name
fruit	바나나,사과,수박

 

[정리]

MySQL에서 group by 로 문자열을 합칠땐 group_concat 을 이용한다.

1. 기본형 : group_concat(필드명)

2. 구분자 변경 : group_concat(필드명 separator '구분자')

3. 중복제거 : group_concat(distinct 필드명)

4. 문자열 정렬 : group_concat(필드명 order by 필드명)

 

[참고]

MySQL Group BY function

Name	Description
AVG()	Return the average value of the argument
BIT_AND()	Return bitwise and
BIT_OR()	Return bitwise or
BIT_XOR()	Return bitwise xor
COUNT(DISTINCT)	Return the count of a number of different values
COUNT()	Return a count of the number of rows returned
GROUP_CONCAT()	Return a concatenated string
MAX()	Return the maximum value
MIN()	Return the minimum value
STD()	Return the population standard deviation
STDDEV_POP()	Return the population standard deviation
STDDEV_SAMP()	Return the sample standard deviation
STDDEV()	Return the population standard deviation
SUM()	Return the sum
VAR_POP()	Return the population standard variance
VAR_SAMP()	Return the sample variance
VARIANCE()	Return the population standard variance

URL : http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

 

[TIP]

오라클의 경우 mysql의 group_concat을 아래와 같이 대체하여 사용이 가능하다.

오라클 10G : WM_CONCAT()

오라클 11G : LISTAGG()

 

##order by 사용하는방법##

group_concat(CG_NAME(컬럼명) order by CG_NAME separator ',')

 

##수정중 발견한 사항##

listagg -> group_concat

(SELECT LISTAGG(CG_NAME, ',') WITHIN GROUP (ORDER BY CG_NAME) AS CG_NAME FROM (SELECT DISTINCT CG_NAME FROM t_total_tb WHERE DELYN ='N')) AS cgArr

 

해당부분을 워크벤치해서 확인하던중

select group_concat(CG_NAME order by CG_NAME separator ',') from (select distinct CG_NAME from t_total_tb ttt where DELYN='N');

동일하게 작동하는 쿼리로 변경하였으나 from쪽에 서브쿼리형태로 들어가면 오류를 발생시켰음.

 

아래와 같은오류.  Limit 값을 따로 적용안했으나 자동으로 적용되고있음.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 0, 200' at line 2

 

찾아본결과

phpMyAdmin과 MySQL Workbench는 모두 쿼리 끝에 "LIMIT"절을 자동으로 추가하므로이 잘못된 메시지가 표시됩니다.

그러나 문제의 근본 원인은 하위 쿼리에 대한 별칭을 제공해야한다는 것입니다.

위 문구를 찾음. 하위쿼리에 대한 별칭이 없는경우 limit을 자동으로 제공하는듯함.

 

##별칭만 넣어줬더니 정상작동함##

select group_concat(CG_NAME order by CG_NAME separator ',') from (select distinct CG_NAME from t_total_tb ttt where DELYN='N') as a;

 

출처: https://fruitdev.tistory.com/16 [과일가게 개발자]