mysql - MySQL:左连接总数低于范围

我正在尝试从数据库中的以下表格生成以下表格输出。

期望的输出：

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

尽管有1位和2位用户的状态为“ QQQ”的条目有7条。

我几乎要解决该问题了，但是我无法绕开table-pax的每个类别的汇总，而不是我想要的，即对于“ Data_table”下的所有pax 1和2，我希望那些在table-pax 1-2的输出下聚合，所有pax 3和4在table-pax 3-4的聚合下，依此类推。

最佳答案

您可以尝试一下，但是我不确定最后一栏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;

关于mysql - MySQL:左连接总数低于范围，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28524206/