我想使用 UNION ALL 和 GROUP BY 從兩個相同的表中進行選擇。但是，Group BY 不起作用。這是我的查詢：
SELECT type , COUNT(subscription.id) as number ,SUM(subscription.amount) as total
FROM subscription
WHERE DATE(subscription.timestamp) BETWEEN '2022-10-18' AND '2022-10-18'
UNION ALL
SELECT type , COUNT(archive_subscription.id) as number ,SUM(archive_subscription.amount) as total
FROM archive_subscription
WHERE DATE(archive_subscription.timestamp) BETWEEN '2022-10-18' AND '2022-10-18'
GROUP BY type
結果如下：
| 型別 | 數字 | 數量 |
|---|---|---|
| 1 | 2 | 180000 |
| 1 | 1 | 80000 |
我想要做的是兩個使用 GROUP BY 合并兩個表，但它不起作用：
| 型別 | 數字 | 數量 |
|---|---|---|
| 1 | 3 | 260000 |
請問，有什么建議嗎？謝謝
uj5u.com熱心網友回復：
聯合中的第一個子查詢缺少其GROUP BY子句。相反，請嘗試先合并然后聚合：
SELECT type, COUNT(*) AS number, SUM(amount) AS total
FROM
(
SELECT type, amount
FROM subscription
WHERE timestamp BETWEEN '2022-10-18' AND '2022-10-18'
UNION ALL
SELECT type, amount
FROM archive_subscription
WHERE timestamp BETWEEN '2022-10-18' AND '2022-10-18'
) t
GROUP BY type;
請注意，您的WHERE子句中的范圍是微不足道的。如果您真的想限制為單個日期，請WHERE timestamp = '2022-10-18'改用。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/518123.html