mysql 字符拼接
《TiDB v2.0 中文技术文档》
一、CONCAT
- 功能：无缝拼接列或者字符 ；
- 语法：CONCAT(string1,string2,...)
- 说明：此方法在拼接的时候如果有一个值为NULL，则返回NULL
二、CONCAT_WS
- 功能：可以多个字符串用指定的字符串进行拼接 ；
- 语法：CONCAT_WS(separator,string1,string2,...)
三、GROUP_CONCA
- 功能：行转列 合并显示(返回连接的字符串) ；
- 语法：GROUP_CONCAT([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']）
- 说明：mysql group_concat( order by 可以正常排序) ,但在 TiDB 中存在排序异常 ；
1 select id,group_concat( score order by score desc SEPARATOR ',') as unionStr,school 2 from ( 3 select '1' as id,90 as score , 'a' as school union all 4 select '1' as id,91 as score , 'a' as school union all 5 select '1' as id,92 as score , 'a' as school union all 6 select '2' as id,94 as score , 'a' as school union all 7 select '2' as id,95 as score , 'a' as school union all 8 select '2' as id,96 as score , 'a' as school union all 9 select '3' as id,97 as score , 'a' as school union all 10 select '3' as id,98 as score , 'a' as school union all 11 select '3' as id,99 as score , 'a' as school 12 )t 13 group by 1,3 ;
