mysql 分组内排——group_concat 超、凢脫俗 2021-06-24 15:58 484阅读 0赞 在大多数应用中,group\_concat函数通常用来做行列转换。其实group\_concat函数还有一个很重要的功能,就是分组内排序。 group\_concat完整语法: group\_concat(\[DISTINCT\] 要连接的字段 \[Order BY ASC/DESC 排序字段\] \[Separator '分隔符'\]) 1、基本用法: 1)通常,使用GROUP\_CONCAT()用来做列行转换: mysql> selectfid,name from test; +-----+------+ | fid | name | +-----+------+ | 1 | a | | 1 | b | | 1 | b | | 2 | A | | 2 | Z | +-----+------+ mysql> selectfid,group_concat(name) from test group by fid; +-----+--------------------+ | fid |group_concat(name) | +-----+--------------------+ | 1 | a,b,b | | 2 | A,Z | +-----+--------------------+ 2)GROUP\_CONCAT()函数是支持指定分隔符的,默认是逗号,可以使用SEPARATOR指定; selectfid,group_concat(name separator ';') from test group by fid; +-----+----------------------------------+ | fid |group_concat(name separator ';') | +-----+----------------------------------+ | 1 | a;b;b | | 2 | A;Z | +-----+----------------------------------+ 3) GROUP\_CONCAT() 函数是支持 排序 的,默认通过 GROUP\_CONCAT() 函数把列转成行的时候顺序是不确定的,可以通过 ORDER BY 指定; selectfid,group_concat(name) from test group by fid; +-----+--------------------+ | fid |group_concat(name) | +-----+--------------------+ | 1 | a,b,b | | 2 | A,Z,C | +-----+--------------------+ mysql> selectfid,group_concat(name order by name desc) from test group by fid; +-----+---------------------------------------+ | fid |group_concat(name order by name desc) | +-----+---------------------------------------+ | 1 | b,b,a | | 2 | Z,C,A | +-----+---------------------------------------+ 4) GROUP\_CONCAT() 函数是支持 去重 功能的,通过 distinct 关键字可以实现; mysql> selectfid,group_concat(name) from test group by fid; +-----+--------------------+ | fid |group_concat(name) | +-----+--------------------+ | 1 | a,b,b | | 2 | A,Z | +-----+--------------------+ selectfid,group_concat(distinct name) from test group by fid; +-----+-----------------------------+ | fid |group_concat(distinct name) | +-----+-----------------------------+ | 1 | a,b | | 2 | A,Z | +-----+-----------------------------+ 2、分组内排序: 有数据表 comments ------------------------------------------------ | id | newsID | comment | theTime | ------------------------------------------------ | 1 | 1 | aaa | 11 | ------------------------------------------------ | 2 | 1 | bbb | 12 | ------------------------------------------------ | 3 | 2 | ccc | 12 | ------------------------------------------------ newsID是新闻ID,每条新闻有多条评论comment,theTime是发表评论的时间.现在想要查看每条新闻的最新一条评论。 1)下面这两种方法显然不行: select * from comments group by newsID 这种方式,newId=1的新闻,组内评论没有进行排序,mysql会默认展示出第一个出现的评论。 select * from comments group by newsID order by theTime desc 这种方式也不行,这是组外排序。 2)使用group\_concat函数实现组内排序: select newsID,group_concat(comment,Order BY theTime DESC),theTime from comments as tt group by newsID 3)其他方法: selet tt.id,tt.newsID,tt.comment,tt.theTime from( select id,newsID,comment,theTime from comments order by theTime desc) as tt group by newsID 或者 select id,newsID,comment,theTime from comments as tt group by id,newsID,comment,theTime having theTime=(select max(theTime) from comments where newsID=tt.newsID)
还没有评论,来说两句吧...