php - MySQL 连接 2 个不匹配的查询

Question

我的数据库存在 2 个不同的表，名为

Wardrobe_CloakTable & Wardrobe_ServiceTable

它们都有一些不同的值，但我试图收集每个小时“价格”列的总和。

此查询为我提供了 Wardrobe_CloakTable 的正确结果

SELECT DATE(delivered) as date, sum(price) as sum, HOUR(delivered) AS hour
          FROM Wardrobe_CloakTable
         WHERE payingcustomerID = '$payingcustomerID'
           AND delivered >= CONCAT(CURDATE() , ' 07:00:00')
           AND delivered <  CONCAT(CURDATE() , ' 07:00:00') + INTERVAL 1 DAY
         GROUP BY DATE(delivered), HOUR(delivered)

这给了我类似的东西

---------------------------------
|    date    |  sum  | hour     |
| 2016-09-15 |  100  |  9       |
| 2016-09-15 |  200  |  10      |
| 2016-09-15 |  100  |  12      |
| 2016-09-15 |  100  |  18      |
---------------------------------

因此，只有在实际有数据的时间里，我想做的就是得到如下输出:

-----------------------------------------------------------------
|    date    |  sum(CloakTable)  | sum(ServiceTable) | hour     |
| 2016-09-15 |  100              |  200              |  9       |
| 2016-09-15 |  200              |  500              |  10      |
| 2016-09-15 |                   |  400              |  11      |
| 2016-09-15 |  200              |                   |  14      |
| 2016-09-15 |  100              |  400              |  15      |
| 2016-09-15 |  100              |  200              |  18      |
------------------------------------------------------------------

到目前为止，我尝试使用 UNION 和 FULL JOIN 创建 is，但我注意到 MySQL 中不可能实现完全连接。除非你先左后右。

我最接近有用的东西是:

SELECT  DATE(delivered) as date, 
    sum(price) as sum,
    HOUR(delivered) as hour

          FROM Wardrobe_CloakTable  
          WHERE payingcustomerID = 2
           AND Wardrobe_CloakTable.delivered >= CONCAT(CURDATE() , ' 07:00:00')
           AND Wardrobe_CloakTable.delivered <  CONCAT(CURDATE() , ' 07:00:00') + INTERVAL 1 DAY
           GROUP BY DATE(delivered), HOUR(delivered)
    UNION ALL
SELECT  DATE(time) as date,
    sum(price) as sum,
    HOUR(time) as hour
    FROM Wardrobe_ServiceTable           
         WHERE payingcustomerID = 2
         AND Wardrobe_ServiceTable.time >= CONCAT(CURDATE() , ' 07:00:00')
         AND Wardrobe_ServiceTable.time <  CONCAT(CURDATE() , ' 07:00:00') + INTERVAL 1 DAY
         GROUP BY DATE(time), HOUR(time)

这给了我:

---------------------------------
|    date    |  sum  | hour     |
| 2016-09-15 |  100  |  9       | (Cloaktable)
| 2016-09-15 |  200  |  10      | (Cloaktable)
| 2016-09-15 |  100  |  9       | (service)
| 2016-09-15 |  100  |  11      | (service)
---------------------------------

架构

CREATE TABLE `Wardrobe_CloakTable` (
  `ID` int(64) NOT NULL,
  `payingcustomerID` int(11) NOT NULL,
  `deviceID` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `terminalnumber` int(11) NOT NULL,
  `qrcode` varchar(64) CHARACTER SET latin1 COLLATE latin1_danish_ci NOT NULL,
  `cloakroomsection` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cloakroomnumber` int(11) NOT NULL,
  `isbag` tinyint(1) NOT NULL,
  `price` int(11) NOT NULL,
  `delivered` datetime NOT NULL,
  `collected` datetime DEFAULT NULL,
  `reservedtime` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `Wardrobe_ServiceTable` (
  `ID` int(11) NOT NULL,
  `payingcustomerID` int(11) NOT NULL,
  `qrcode` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `time` datetime NOT NULL,
  `price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Answer 1

桑尼，

我收到了一个工作请求。不能 100% 确定这是处理它的最佳方法，但至少它有效:

SELECT date_buy, hour_buy, sum(sum_cloak) AS `sum(CloakTable)`, sum(sum_service) AS `sum(ServiceTable)` FROM (
    SELECT DATE(wct.delivered) as date_buy, HOUR(wct.delivered) AS hour_buy, sum(wct.price) AS sum_cloak, 0 as sum_service FROM Wardrobe_CloakTable wct
    WHERE wct.payingcustomerID = 2 
        AND wct.delivered >= CONCAT(CURDATE(), ' 07:00:00')
        AND wct.delivered < CONCAT(CURDATE(), ' 07:00:00') + INTERVAL 1 DAY
    GROUP BY date_buy, hour_buy

    UNION ALL

    SELECT DATE(wst.time) as date_buy, HOUR(wst.time) AS hour_buy, 0 AS sum_cloak, sum(wst.price) as sum_service
    FROM Wardrobe_ServiceTable wst
    WHERE wst.payingcustomerID = 2
        AND wst.time >= CONCAT(CURDATE(), ' 07:00:00')
        AND wst.time < CONCAT(CURDATE(), ' 07:00:00') + INTERVAL 1 DAY
    GROUP BY date_buy, hour_buy
) fullTab
GROUP BY date_buy, hour_buy