GROUP_CONCAT更改GROUP BY订单 [英] GROUP_CONCAT change GROUP BY order

问题描述

我有一个VIEW(很多联接)，它输出按日期ASC排序的数据.可以正常工作.

I have a VIEW (lots of joins) that outputs data ordered by a date ASC. Works as expected.

输出类似于:

ID date         tag1   other_data
1  25-03-2011   blue   fff   <=
1  26-03-2011   red    ggg
1  27-03-2011   pink   yyy
2  25-03-2011   red    yyy   <=
2  26-03-2011   orange rrr

如果我应用GROUP BY ID.对于其他列，MySQL输出每个ID的第一个找到的行.我在te docs的某处阅读了此内容.

If I apply a GROUP BY ID. For the other columns MySQL outputs the first found row of each ID. I read this somewhere in te docs.

SELECT * FROM `myVIEW`  
GROUP BY `ID`  
  ID date         tag1  other_data  
  1  25-03-2011   blue   fff   <=
  2  25-03-2011   red    yyy   <=

现在让我们添加GROUP_CONCAT(tags1)

Now lets add a GROUP_CONCAT(tags1)

SELECT *,CONCAT_GROUP(`tag1`) AS `tags`  
FROM `myVIEW`  
GROUP BY `ID`

由于我应用了CONCAT_GROUP，结果变得奇怪.我期待中:

Since I apply the CONCAT_GROUP the results get odd. I was expecting:

ID date         tag1   other_data   tags
1  25-03-2011   blue   fff          blue,red,pink
2  25-03-2011   red    yyy          red,orange

查询正在返回，例如:

ID date         tag1   other_data   tags
1  26-03-2011   red    ggg          blue,red,pink
2  25-03-2011   red    yyy          red,orange

类似GROUP_CONCAT的视图不再保留VIEW顺序.这正常吗?

推荐答案

如何订购GROUP_CONCAT?

How about ordering your GROUP_CONCAT?

SELECT value1, GROUP_CONCAT(value1 ORDER BY date DESC)   
FROM table1  
GROUP BY value1;

这是您需要的语法.

这篇关于GROUP_CONCAT更改GROUP BY订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！