按组内连续排序 [英] order by within group concat
本文介绍了按组内连续排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要先订购DNAID然后DNBID
第一个查询它的订单为：
111221
第二个查询它的订单为：
112112
获取更多信息和详细信息，了解我正试图完成的任务
https://stackoverflow.com/questions/5082880/database-query-group-union-find-latest
mysql>从metarun中选择*;
+ ---- + ------------ + ------- + ------- + ----------- --- + ---------- +
| ID | RunGroupID | DNAID | DNBID | CONFIGTYPEID |日期时间|
+ ---- + ------------ + ------- + ------- + ----------- --- + ---------- +
| 1 | 1 | 1 | 1 | 2 | NULL |
| 2 | 1 | 1 | 2 | 2 | NULL |
| 3 | 1 | 2 | 1 | 2 | NULL |
| 4 | 2 | 1 | 1 | 4 | NULL |
| 5 | 2 | 3 | 2 | 4 | NULL |
| 6 | 3 | 3 | 2 | 1 | NULL |
| 7 | 3 | 1 | 2 | 1 | NULL |
| 8 | 3 | 3 | 1 | 1 | NULL |
| 9 | 4 | 1 | 1 | 3 | NULL |
| 10 | 4 | 2 | 1 | 3 | NULL |
| 11 | 4 | 1 | 2 | 3 | NULL |
| 12 | 5 | 1 | 1 | 2 | NULL |
| 13 | 5 | 2 | 1 | 2 | NULL |
| 14 | 5 | 1 | 2 | 2 | NULL |
| 15 | 6 | 3 | 2 | 4 | NULL |
| 16 | 6 | 1 | 1 | 4 | NULL |
+ ---- + ------------ + ------- + ------- + ----------- --- + ---------- +
mysql> SELECT RunGroupID，GROUP_CONCAT（DNAID，DNBID SEPARATOR''），ConfigTypeID，DateTime
- > FROM metarun
- > GROUP BY RunGroupID
- > ORDER BY RunGroupID，DNAID ASC，DNBID ASC;
+ ------------ + -------------------------------- --------- + -------------- + ---------- +
| RunGroupID | GROUP_CONCAT（DNAID，DNBID SEPARATOR''）| ConfigTypeID |日期时间|
+ ------------ + -------------------------------- --------- + -------------- + ---------- +
| 1 | 111221 | 2 | NULL |
| 2 | 1132 | 4 | NULL |
| 3 | 123132 | 1 | NULL |
| 4 | 111221 | 3 | NULL |
| 5 | 111221 | 2 | NULL |
| 6 | 1132 | 4 | NULL |
+ ------------ + -------------------------------- --------- + -------------- + ---------- +
设置6行（0.00秒）
mysql> SELECT RunGroupID，GROUP_CONCAT（DNAID，DNBID SEPARATOR''），ConfigTypeID，DateTime
- > FROM metarun
- > WHERE configtypeid = 2
- > GROUP BY RunGroupID
- > ORDER BY RunGroupID;
+ ------------ + -------------------------------- --------- + -------------- + ---------- +
| RunGroupID | GROUP_CONCAT（DNAID，DNBID SEPARATOR''）| ConfigTypeID |日期时间|
+ ------------ + -------------------------------- --------- + -------------- + ---------- +
| 1 | 111221 | 2 | NULL |
| 5 | 112112 | 2 | NULL |
+ ------------ + -------------------------------- --------- + -------------- + ---------- +
2行（0.02秒）
解决方案
我相信你可以指定一个 ORDER 子句： GROUP_CONCAT 函数中的BY
SELECT RunGroupID，GROUP_CONCAT（DNAID，DNBID ORDER BY DNAID，DNBID SEPARATOR''）
The order by is not working in the second query. I need to order by first DNAID then DNBID
First Query its ordered as: 111221
Second Query its ordered as: 112112
for more info and details about what im trying to accomplish https://stackoverflow.com/questions/5082880/database-query-group-union-find-latest
mysql> select * from metarun;
+----+------------+-------+-------+--------------+----------+
| ID | RunGroupID | DNAID | DNBID | CONFIGTYPEID | DateTime |
+----+------------+-------+-------+--------------+----------+
| 1 | 1 | 1 | 1 | 2 | NULL |
| 2 | 1 | 1 | 2 | 2 | NULL |
| 3 | 1 | 2 | 1 | 2 | NULL |
| 4 | 2 | 1 | 1 | 4 | NULL |
| 5 | 2 | 3 | 2 | 4 | NULL |
| 6 | 3 | 3 | 2 | 1 | NULL |
| 7 | 3 | 1 | 2 | 1 | NULL |
| 8 | 3 | 3 | 1 | 1 | NULL |
| 9 | 4 | 1 | 1 | 3 | NULL |
| 10 | 4 | 2 | 1 | 3 | NULL |
| 11 | 4 | 1 | 2 | 3 | NULL |
| 12 | 5 | 1 | 1 | 2 | NULL |
| 13 | 5 | 2 | 1 | 2 | NULL |
| 14 | 5 | 1 | 2 | 2 | NULL |
| 15 | 6 | 3 | 2 | 4 | NULL |
| 16 | 6 | 1 | 1 | 4 | NULL |
+----+------------+-------+-------+--------------+----------+
mysql> SELECT RunGroupID, GROUP_CONCAT(DNAID, DNBID SEPARATOR ''), ConfigTypeID, DateTime
-> FROM metarun
-> GROUP BY RunGroupID
-> ORDER BY RunGroupID, DNAID ASC, DNBID ASC;
+------------+-----------------------------------------+--------------+----------+
| RunGroupID | GROUP_CONCAT(DNAID, DNBID SEPARATOR '') | ConfigTypeID | DateTime |
+------------+-----------------------------------------+--------------+----------+
| 1 | 111221 | 2 | NULL |
| 2 | 1132 | 4 | NULL |
| 3 | 123132 | 1 | NULL |
| 4 | 111221 | 3 | NULL |
| 5 | 111221 | 2 | NULL |
| 6 | 1132 | 4 | NULL |
+------------+-----------------------------------------+--------------+----------+
6 rows in set (0.00 sec)
mysql> SELECT RunGroupID, GROUP_CONCAT(DNAID, DNBID SEPARATOR ''), ConfigTypeID, DateTime
-> FROM metarun
-> WHERE configtypeid=2
-> GROUP BY RunGroupID
-> ORDER BY RunGroupID;
+------------+-----------------------------------------+--------------+----------+
| RunGroupID | GROUP_CONCAT(DNAID, DNBID SEPARATOR '') | ConfigTypeID | DateTime |
+------------+-----------------------------------------+--------------+----------+
| 1 | 111221 | 2 | NULL |
| 5 | 112112 | 2 | NULL |
+------------+-----------------------------------------+--------------+----------+
2 rows in set (0.02 sec)
解决方案
I believe you can specify an ORDER BY clause in the GROUP_CONCAT function:
SELECT RunGroupID, GROUP_CONCAT(DNAID, DNBID ORDER BY DNAID, DNBID SEPARATOR '')
这篇关于按组内连续排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
