GROUP_CONCAT函数切换实例

GROUP_CONCAT为MySQL提供的函数，MaxCompute对应为wm_concat。

数据：
table t (a,b)
a b c

zhang 1 10
wang 2 20
wang 3 30
guo 6 60
wang 2 30

场景一.将a进行合并，不去重

MySQL:
select a, group_concat(b) from t group by a;
MaxCompute:
select a, wm_concat(',', b) as b from t group by a;

|a| b |

|zhang|1|
|wang | 2,3,2|
|guo | 6 |

场景二.将a进行合并，去重

MySQL:
select a, group_concat(distinct b) from t group by a;
MaxCompute:
select a, wm_concat(distinct ',', b) as b from t group by a;

|a| b |

|zhang|1|
|wang | 2,3|
|guo | 6 |

场景三.将a进行合并且排序，不去重

MySQL:
select a, group_concat(b order by b desc) 
from t 
group by a;
MaxCompute:
select a, wm_concat(',', b) as b 
from (select a, b from t order by a, b desc) 
group by a;

|a| b |

|zhang|1|
|wang | 2,2,3|
|guo | 6 |

场景四.将a进行合并且排序，去重

MySQL:
select a, group_concat(distinct b order by b desc) from t 
group by a;
MaxCompute:
select a, wm_concat(',', b) as b 
from (
    select distinct a, b 
    from t 
    order by a, b asc) 
group by a;

|a| b |

|zhang|1|
|wang | 2,3|
|guo | 6 |

场景五.将b、c进行合并

MySQL:
select a, group_concat(concat_ws(':', b, c)) 
from t 
group by a;
MaxCompute:
(1).
select a, wm_concat(',', concat_ws(':',b,c)) as b from t 
group by a;
(2).
select a, wm_concat(',', concat(b,':',c)) 
from t group by a;

|a| b |

|zhang|1:10|
|wang | 1:20,2:30,3:30 |
|guo | 6:60 |