V2EX = way to explore
V2EX 是一个关于分享和探索的地方
Sign Up Now
For Existing Member Sign In
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 1890 days ago, the information mentioned may be changed or developed.
| sid | eid | datetime |
|---|---|---|
| 73753 | 4 | 2021-02-23 09:00 |
| 73753 | 7 | 2021-02-23 09:00 |
| 73753 | 2 | 2021-02-23 09:00 |
| 73757 | 2 | 2021-02-23 10:00 |
| 73756 | 2 | 2021-02-23 11:00 |
| 73756 | 2 | 2021-02-23 11:00 |
| 73756 | 2 | 2021-02-23 11:00 |
| 73756 | 7 | 2021-02-23 11:00 |
| 73756 | 4 | 2021-02-23 11:00 |
| 73756 | 2 | 2021-02-23 11:00 |
| 73760 | 7 | 2021-02-23 11:00 |
| 73760 | 4 | 2021-02-23 11:00 |
| 73759 | 7 | 2021-02-23 11:00 |
| 73759 | 4 | 2021-02-23 11:00 |
| 73759 | 2 | 2021-02-23 11:00 |
| 73758 | 4 | 2021-02-23 11:00 |
| 73758 | 2 | 2021-02-23 11:00 |
以上是自查询后的临时表数据，现在我要使用 group by 对 eid 与 datetime 进行分组
这个是查询语句：
SELECT
`eid`,
MIN( `datetime` ) AS `datetime`,
COUNT(DISTINCT( `sid` )) AS `value`,
GROUP_CONCAT(`sid`) AS `sids`
FROM
table
GROUP BY
`eid`,
`datetime`
ORDER BY
`datetime`
得出对结果却是
| eid | datetime | value | sids |
|---|---|---|---|
| 2 | 2021-02-23 09:00 | 1 | 73753 |
| 4 | 2021-02-23 09:00 | 1 | 73753 |
| 7 | 2021-02-23 09:00 | 1 | 73753 |
| 2 | 2021-02-23 10:00 | 1 | 73757 |
| 2 | 2021-02-23 11:00 | 1 | 73756 |
| 2 | 2021-02-23 11:00 | 3 | 73758,73756,73759,73756,73756 |
| 4 | 2021-02-23 11:00 | 4 | 73756,73760,73759,73758 |
| 7 | 2021-02-23 11:00 | 3 | 73760,73759,73756 |
这里出现了两个 eid = 2 && datetime = 2021-02-23 11:00 我试了一下，知道是 DISTINCT 导致的问题，但是没想明白其中的原因，希望大佬讲解一下。
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73753, 4, '2021-02-23 09:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73753, 7, '2021-02-23 09:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73753, 2, '2021-02-23 09:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73757, 2, '2021-02-23 10:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73756, 2, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73756, 2, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73756, 2, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73756, 7, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73756, 4, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73756, 2, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73760, 7, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73760, 4, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73759, 7, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73759, 4, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73759, 2, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73758, 4, '2021-02-23 11:00');
INSERT INTO `temp_table`(`sid`, `eid`, `datetime`) VALUES (73758, 2, '2021-02-23 11:00');
7 replies • 2021-02-24 11:04:22 +08:00
 |
1
wxVIP OP 使用分组在临时表中查询会有这种情况，但是把临时表存入一个实表中就是可以正常使用
|
 |
2
mitsuizzz Feb 23, 2021
https://imgchr.com/i/yqU9rd 用的你的插入和查询 sql 和你查出来的数据不一样，我是 mysql5.7
|
 |
5
Rache1 Feb 24, 2021
group_concat 最好单独套一层用，不然会有意想不到的结果 😂
|
 |
6
Rocketer Feb 24, 2021 via iPhone
SQL 命令真正的执行顺序如下：
(1) FROM (2) JOIN (3) ON (4) WHERE (5) GROUP BY (6) WITH (7) HAVING (8) SELECT (9) DISTINCT (10) ORDER BY (11) LIMIT 每一步的输出就是下一步的输入，自己推演一下试试吧 |
 |
7
lyz0205 Feb 24, 2021
mysql 8.0 没能重现你说的问题，你的 mysql 是哪个版本？
|
Select Language