Data:
id uid type
1 20 A
2 20 B
3 20 A
4 6 A
5 1 A
6 3 A
7 6 A
8 1 B
场景:
我想按type
分组并按id
排序。我正在使用分组来分组uid
.
当前查询:
SELECT
type,
GROUP_CONCAT(DISTINCT uid) AS users,
COUNT(type) AS typeCount
FROM
`test2`
GROUP BY
type
问题:
但是uid
的顺序不正确,它应该按照id
降序排列.
预期成果:
type users typeCount
A 6,3,1,20 6
B 1,20 2
我的结果:
type users typeCount
A 20,6,1,3 6
B 20,1 2
MySQL 的迷雾。
实际上,无论您是否按 ID 请求 DESC,引擎都以 ASC 顺序获取第一个值,因此首先"翻转"表,然后:
SELECT
type,
GROUP_CONCAT(DISTINCT uid ORDER BY id DESC) AS users,
COUNT(type) AS typeCount
FROM
(SELECT * FROM `test2` ORDER BY id DESC) test2
GROUP BY
type
SQLFiddleDemo
@mitkosoft的答案已经是对的。
我发布此内容只是为了分析正确的预期结果。
从下面的输出中,我们可以看到,对于类型"A"组,在 DISTINCT 生效之前,在 ORDER BY id DESC 之后,行是:
63 1 6 20 20
然后 DISTINCT 可以产生两种可能的结果:
6,3,1,20 或 3,1,6,20。生产哪一个是不确定的,与实现有关。否则,我们不能依赖它。
因此,组"A"的预期结果应为 6,3,1,20 或 3,1,6,20。两者都正确。
mysql> SELECT * FROM test2;
+------+------+------+
| id | uid | type |
+------+------+------+
| 1 | 20 | A |
| 2 | 20 | B |
| 3 | 20 | A |
| 4 | 6 | A |
| 5 | 1 | A |
| 6 | 3 | A |
| 7 | 6 | A |
| 8 | 1 | B |
+------+------+------+
8 rows in set (0.00 sec)
mysql> SELECT uid FROM test2 WHERE type='A' ORDER BY id DESC;
+------+
| uid |
+------+
| 6 |
| 3 |
| 1 |
| 6 |
| 20 |
| 20 |
+------+
6 rows in set (0.00 sec)
你可以像桑普森在这篇文章中建议的那样做一些事情:
MySQL:对GROUP_CONCAT值进行排序
这是指向MySQL文档的链接
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function%5Fgroup-concat
以下是他举的例子:
SELECT student_name,
GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name;
您只需要根据需要进行调整即可。
希望这有帮助
这不需要子查询。 从您的描述中,您只需要在GROUP_CONCAT()
中ORDER BY
:
SELECT type,
GROUP_CONCAT(DISTINCT uid ORDER BY uid DESC) AS users,
COUNT(type) AS typeCount
FROM `test2`
GROUP BY type;
在 MySQL 中,最好避免不必要的子查询,因为数据库引擎会具体化它们。
尝试类似操作:
SELECT
type,
GROUP_CONCAT(DISTINCT uid) AS users,
COUNT(type) AS typeCount
FROM
(SELECT type, uid
FROM `test2`
ORDER BY uid desc) mytableAlias
GROUP BY
type