MySQL 相关项目
| 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');
Select Language