MySQL:范围内的左连接计数总数
MySQL:范围内的左连接计数总数
提问于 2015-02-15 08:16:22
我正在尝试从我的数据库中的下表生成下表输出。
期望产出：
Table-pax Q-Length Avg wait time Cust ID
===== ======== ====================== =======
1-2 7 5 minutes 4
3-4 2 6 minutes 12
5-6 0 7 minutes NULL
7-8 0 7 minutes NULL因此，我使用以下查询来尝试生成所需的输出，这是在我的数据库表上执行的：
SELECT concat(s.min_pax, "-", s.max_pax) as Table-pax, d.CustCount as Q-Length, s.avg_q_time, d2.cust_id
FROM `Stat_table` as s
LEFT JOIN (
SELECT est_id, COUNT(cust_id) as CustCount, pax, q_id
FROM `Data_table`
WHERE STATUS = "QQQ")
GROUP BY est_id, pax) d
on d.pax >= s.min_pax AND d.pax <= s.max_pax
LEFT JOIN (
SELECT est_id, pax, cust_id, q_id
FROM `Data_table`
WHERE status = "QQQ")
GROUP BY pax
ORDER BY time ASC) d2
on d2.q_id = d.q_id
WHERE s.est_id = 1
GROUP BY concat(s.min_pax, "-", s.max_pax)数据库表1- Stat_table
est_id min_pax max_pax avg_q_time
====== ======= ======= ==========
1 1 2 5
1 3 4 6
1 5 6 7
1 7 8 7
2 1 4 4数据库表2- Data_table
est_id cust_id pax status q_id time
====== ======= === ====== ==== ====
1 13 3 QQQ 22 12:32:01
1 12 3 QQQ 21 11:00:41
1 1 2 QQQ 20 12:12:33
1 11 1 QQQ 19 11:12:10
1 1 1 CXL 18 12:11:07
1 10 1 QQQ 17 12:59:45
1 7 1 QQQ 16 11:05:30
1 6 1 QQQ 15 12:18:32
1 5 1 QQQ 14 12:22:12
1 4 1 QQQ 13 10:15:02但是，我从上述查询中得到的信息是
Table-pax Q-Length Avg wait time Cust ID
===== ======== ====================== =======
1-2 1 5 minutes 1
3-4 2 6 minutes 12
5-6 0 7 minutes NULL
7-8 0 7 minutes NULL尽管有7个条目，状态为"QQQ“的pax为1和2。
我几乎接近了解决方案，但我无法绕过每一类表的聚合-- pax不是我想要的，也就是说，对于'Data_table‘下的所有pax 1和2，我希望将它们聚合到表-pax 1-2的输出下，而所有的pax 3和4都聚集在表-pax 3-4下，等等。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-02-15 09:38:21
你可以试试这个，但我不确定上一篇专栏的cust_id，也许你能解释一下吗？
SELECT
concat(s.min_pax, '-', s.max_pax) AS Table_pax,
sum(d.CustCount) AS CustCount,
s.avg_q_time,
d2.cust_id
FROM Stat_table AS s
LEFT JOIN (
SELECT
COUNT(cust_id) AS CustCount,
pax,
q_id
FROM Data_table
WHERE status = 'QQQ'
GROUP BY pax) d
ON d.pax >= s.min_pax AND d.pax <= s.max_pax
LEFT JOIN (
SELECT
est_id,
pax,
cust_id,
q_id
FROM Data_table
WHERE status = 'QQQ'
GROUP BY pax
ORDER BY time ASC) d2
ON d2.q_id = d.q_id
WHERE s.est_id = 1
GROUP BY Table_pax;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28524206
复制相关文章
相似问题
腾讯云开发者
