一個查詢找到了按國家匯總的小計：
SELECT
customer.country,
SUM(i.subtotal) AS total
FROM[/span
發票i
LEFT JOIN customer ON i.customer_id = customer.id
WHERE[/span
狀態 = 'Paid'
and datepaid BETWEEN '2020-05-01 00:00:00' and '2020-06-01 00:00'
AND customer.billing_day <>/span> 0
and customer.register_date < '2020-06-01 00:00:00'
and customer.account_exempt = 'f'
customer.country <> ''
GROUP BY
customer.country。
另一個查詢是按州匯總美國客戶的小計：
SELECT
customer.state。
SUM(i.subtotal) AS total
FROM[/span
發票i
LEFT JOIN customer ON i.customer_id = customer.id
WHERE[/span
狀態 = 'Paid'
and datepaid BETWEEN '2020-05-01 00:00:00' and '2020-06-01 00:00'
AND customer.billing_day <>/span> 0
and customer.register_date < '2020-06-01 00:00:00'
and customer.account_exempt = 'f'/span>
and customer.country = 'US'
and customer.state <> ' '
GROUP BY
customer.state。
是否有可能寫一個查詢，回傳每個國家的總數，如果國家是美國，也回傳州的總數？我讀到子查詢可以用來結合兩個聚合函式，但我不確定在這里如何做。
。uj5u.com熱心網友回復：
你可以在group by中使用兩個鍵：
SELECT c.country,
(CASE WHEN c.country. country = 'US' THEN c.stateEND) as state,
SUM(i.subtotal) AS total
FROM發票 i JOIN
客戶c
ON i.customer_id = c.id
WHERE i.status = 'Paid' AND
i.datepaid >= '2020-05-01'/span> AND
i.datepaid < '2020-06-01' AND
c.billing_day <>/span> 0 AND
c.register_date </span> '2020-06-01' AND
c.account_exempt ='f'
c.country <> ''
GROUP BY c.country, (CASE WHEN c. country = 'US' THEN c.state END）。)
注意查詢的其他變化：
WHERE子句把外連接變成了內連接，所以LEFT JOIN是誤導性的。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/307990.html
標籤：
下一篇：<p>晚上好，親愛的Stackoverflow社區。 這是我在這里的第一個問題。 <p>我有以下問題。我需要一個查詢來計算每個國家的滾動日期范圍（3天）內的不同值。 <p