MySQL左外连接问题

Question

这里有一个查询，按小时基础将交易按价格点进行分组：

SELECT hour(Stamp) AS hour, PointID AS pricepoint, count(1) AS counter
FROM Transactions
GROUP BY 1,2;

样例输出：

+------+------------+---------+
| hour | pricepoint | counter |
+------+------------+---------+
|    0 |         19 |       5 |
|    0 |         20 |      14 |
|    1 |         19 |       3 |
|    1 |         20 |      12 |
|    2 |         19 |       2 |
|    2 |         20 |       8 |
|    3 |         19 |       2 |
|    3 |         20 |       4 |
|    4 |         19 |       1 |
|    4 |         20 |       1 |
|    5 |         19 |       4 |
|    5 |         20 |       1 |
|    6 |         20 |       2 |
|    8 |         19 |       1 |
|    8 |         20 |       4 |
|    9 |         19 |       2 |
|    9 |         20 |       5 |
|   10 |         19 |       6 |
|   10 |         20 |       1 |
|   11 |         19 |      10 |
|   11 |         20 |       2 |
|   12 |         19 |      10 |
|   12 |         20 |       3 |
|   13 |         19 |      10 |
|   13 |         20 |      10 |
|   14 |         19 |       8 |
|   14 |         20 |       3 |
|   15 |         19 |       6 |
|   15 |         20 |       8 |
|   16 |         19 |      11 |
|   16 |         20 |      10 |
|   17 |         19 |       7 |
|   17 |         20 |      17 |
|   18 |         19 |       7 |
|   18 |         20 |       9 |
|   19 |         19 |      10 |
|   19 |         20 |      12 |
|   20 |         19 |      17 |
|   20 |         20 |      11 |
|   21 |         19 |      12 |
|   21 |         20 |      29 |
|   22 |         19 |       6 |
|   22 |         20 |      21 |
|   23 |         19 |       9 |
|   23 |         20 |      23 |
+------+------------+---------+

如您所见，有些小时没有交易（例如早上7点），而有些小时只有一个价格点的交易（例如早上6点，仅有价格点20，但没有价格点19的交易）。

我希望在没有交易时显示“0”的结果集，而不是像现在一样根本不存在。

尝试使用左外连接进行操作。inHour表包含值0..23。

SELECT H.hour, PointID AS Pricepoint, COALESCE(T.counter, 0) AS Count
FROM inHour H
LEFT OUTER JOIN
(
 SELECT hour(Stamp) AS Hour, PointID, count(1) AS counter
 FROM Transactions
 GROUP BY 1,2
 ) T
ON T.Hour = H.hour;

这将产生以下输出（为简洁起见而截断）：

|    5 |         19 |     4 |
|    5 |         20 |     1 |
|    6 |         20 |     2 |
|    7 |       NULL |     0 |
|    8 |         19 |     1 |
|    8 |         20 |     4 |

实际上，我想要的是：

|    5 |         19 |     4 |
|    5 |         20 |     1 |
|    6 |         19 |     0 |
|    6 |         20 |     2 |
|    7 |         19 |     0 |
|    7 |         20 |     0 |
|    8 |         19 |     1 |
|    8 |         20 |     4 |

在我期望的输出中，对于在某个小时内没有交易的价格点，将值“0”放在其旁边。

欢迎您提出建议！谢谢。

- emx

---

也许可以在子查询上进行左连接，并选择所有不同的价格点？否则，我会使用一个维度表来表示价格点。 - user610650

确实，这就是我最终采取的做法，受到安德鲁的回答的启发。 - emx

Answer 1

SELECT h.Hour, p.Pricepoint, COUNT(t.*) AS Count
FROM inHour h,
(SELECT DISTINCT PointId AS Pricepoint FROM Transactions) p
LEFT OUTER JOIN Transactions t
ON h.Hour = hour(t.Stamp) AND p.Pricepoint = t.PointID
GROUP BY h.Hour, p.Pricepoint
ORDER BY h.Hour, p.Pricepoint

我现在没有时间尝试这个，如果不行的话请告诉我，我会尽力调整。

- Andrew

---

谢谢你，安德鲁。你的示例真的让我受到了启发，我成功地实现了它。 - emx

Answer 2

可能有比这更好的解决方案，但我会使用 UNION 来简化事情：

SELECT hour(Stamp) AS hour, PointID AS pricepoint, count(1) AS counter
FROM Transactions
GROUP BY 1,2

UNION

SELECT hour,0 AS pricepoint,0 AS counter FROM inHour WHERE hour NOT IN (SELECT hour(Stamp) FROM Transactions)

- hross