mysql - 如何加入两个都有 COUNT 和分组的查询而不头疼？

Question

我一直在尝试将两个查询的结果合并到一张表中，以计算它们并以小时为单位进行排序。

我试过SELECTand UNION ALL，但没有运气。我搜索了所有 StackOverflow 和谷歌，但一直在头疼。如果我运行我的查询单 - 一切都很好，我得到我的输出。

查询一：

SELECT COUNT(*) as peopleCount, date_format( timestamp, '%H' ) as hours 
FROM unknownCheckin
WHERE timestamp > '2019-03-01 23' and eid = '222' 
GROUP BY hours 
order by timestamp ASC

查询 2：

SELECT COUNT(*) as peopleCount, date_format( timestamp, '%H' ) as hours
FROM checkin 
WHERE timestamp > '2019-03-01 23' and eid = '222' 
GROUP BY hours 
order by timestamp ASC

我的实验是：

SELECT COUNT(*) as peopleCount, date_format( timestamp, '%H' ) as hours FROM unknownCheckin
INNER JOIN checkin
ON unknownCheckin.eid = checkin.eid
WHERE timestamp > '2019-03-01 23' and eid = '222' 
GROUP BY hours order by timestamp ASC

1052 - 字段列表中的列“时间戳”不明确

运行单个查询 1 时的预期结果：

--------------------------------------------------
| peopleCount    | hours                         |
--------------------------------------------------
| 4              | 21                            |
--------------------------------------------------
| 1              | 22                            |
--------------------------------------------------
| 1              | 00                            |
--------------------------------------------------

查询 2：

--------------------------------------------------
| peopleCount    | hours                         |
--------------------------------------------------
| 10             | 22                            |
--------------------------------------------------
| 22             | 23                            |
--------------------------------------------------
| 12             | 00                            |
--------------------------------------------------
| 5              | 01                            |
--------------------------------------------------

我想看到的是：

--------------------------------------------------
| peopleCount    | hours                         |
--------------------------------------------------
| 4              | 21                            |
--------------------------------------------------
| 11             | 22                            |
--------------------------------------------------
| 22             | 23                            |
--------------------------------------------------
| 13             | 00                            |
--------------------------------------------------
| 5              | 01                            |
--------------------------------------------------

对不起，我的思维能力不足。帮助表示赞赏！

标签： mysqljoinunion

Answer 1

也许您想在这里使用联合：

SELECT
    hours,
    COUNT(*) as peopleCount
FROM
(
    SELECT DATE_FORMAT(timestamp, '%H') AS hours
    FROM unknownCheckin
    WHERE timestamp > '2019-03-01 23' AND eid = '222'
    UNION ALL
    SELECT DATE_FORMAT(timestamp, '%H')
    FROM checkin
    WHERE timestamp > '2019-03-01 23' AND eid = '222'
) t
GROUP BY hours
ORDER BY -FIELD(hours, '23', '22');