MySQL左外连接麻烦

| 这是一个按小时将交易按价格点分组的查询： 
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点），有些小时仅有一个价格点的交易（例如6am，只有价格点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”放在给定时间内没有交易的价格点旁边。 您的建议将受到欢迎！谢谢。     
2020-02-05 3 条评论
分享

没有找到相关结果

    已邀请:

    瞥同忙接

            
    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
    我目前没有时间尝试该操作，因此请告知它是否无效，然后尝试进行调整。     
    2020-02-05 0 0
    分享

    咳累录酬

            有人可能会比这有更好的解决方案，但我将使用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)
        
    2020-02-05 0 0
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册

    或代码 OrCode.com 备案号：粤ICP备15020848号-1
    Escape time: 0.036984920501709