MySQL:左连接计数总数低于范围

Question

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

期望的输出:

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”，分别为 1 和 2 人。

我几乎已经接近解决方案，但我无法绕过 table-pax 的每个类别的聚合并不像我想要的那样，即对于“Data_table”下的所有 pax 1 和 2，我希望将它们聚合到 table-pax 1-2 的输出下，并将所有 pax 3 和 4 聚合到 table-pax 3-4 下，依此类推。

Answer 1

您可以尝试这个，但我不确定最后一列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;