mysql 分组内排——group_concat

超、凢脫俗 2021-06-24 15:58 519阅读 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)