我在解決 MS SQL 查詢時遇到問題。總之，查詢應該將日期列作為兩列，年和月，其他列的計數，列的總和以及過濾的總和列。我掙扎的是添加過濾的總和列。
一個樣本資料，測驗：customerID, 1,2,3,4... InvoiceID, 1234551, 1234552... ProductID, A, B, C... Date, Datetime Income, int
| 客戶ID | 發票編號 | 產品編號 | 日期 | 收入 |
|---|---|---|---|---|
| 1 | 1234551 | 一種 | 2015 年 1 月 1 日 | 300 |
| 2 | 1234552 | 乙 | 2016 年 2 月 1 日 | 300 |
我有一個解決方案，但我確信有一個更簡單的解決方案。
WITH CTE_1 AS
(
SELECT Date,
COUNT(DISTINCT Test.customerID) AS customers,
COUNT(Test.InvoiceID) AS Invoices,
COUNT(Test.ProductID) AS Products,
Sum(Income) AS Total_Income,
ISNULL((SELECT Sum(Income) AS Income_A FROM Test ts WHERE ProductID = 'A' AND ts.Date = Test.Date),0) AS Total_Income_A
FROM Test
GROUP BY Test.Date
)
SELECT YEAR(Date) AS Year,
MONTH(Date) AS Month,
Sum(customers) AS customers,
Sum(Invoices) AS Invoices,
Sum(Products) AS Products,
Sum(Total_Income) AS Total_Income,
Sum(Total_Income_A) AS Total_Income_A
FROM CTE_1
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY YEAR(Date), MONTH(Date)
生產：Year, 2015, 2016... Month, 1, 2, ... customers, int Invoices, int Products, int Total_Income, int Total_Income_A, int
| 年 | 月 | 顧客 | 發票 | 產品 | 總收入 | Total_Income_A |
|---|---|---|---|---|---|---|
| 2015 | 1 | 3 | 4 | 4 | 1600 | 600 |
| 2015 | 2 | 1 | 1 | 1 | 1200 | 0 |
謝謝！尼爾
uj5u.com熱心網友回復：
您可以直接應用條件聚合，例如
SELECT YEAR(Date) AS Year,
MONTH(Date) AS Month,
COUNT(DISTINCT customerID) AS customers,
COUNT(DISTINCT InvoiceID) AS Invoices,
COUNT(ProductID) AS Products,
SUM(Income) AS Total_Income,
ISNULL(SUM(CASE WHEN ProductID = 'A' THEN Income END),0) AS Total_Income_A
FROM Test
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY YEAR(Date), MONTH(Date)
Demo
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/430074.html
上一篇：允許存盤程序接受值