我有一個資料集，我想在其中使用 SQL Server 對兩個不同的查詢進行分組、過濾和求和。
資料
name stack life date avail
aa unused remove 9/1/2021 5
aa hi keep 9/1/2021 1
aa hey stay 9/1/2021 1
aa hey fix 9/1/2021 2
aa hey keep 9/1/2021 4
aa hi no 9/1/2021 1
aa hi fix 9/1/2021 1
bb ok fix 9/1/2021 1
bb yes remove 9/1/2021 1
bb ok keep 9/1/2021 1
bb ok fix 9/1/2021 1
bb ok yes 9/1/2021 3
bb unused remove 9/1/2021 3
bb ok keep 10/1/2021 1
期望 （邏輯）
- 按名稱分組并篩選到 2021 年 9 月 1 日的日期
- WHERE堆疊！='未使用'
- 以及“保持”和“留下”的生活
- 按名稱分組并篩選到 2021 年 9 月 1 日的日期
- WHERE堆疊！='未使用'
- 以及“保留”的生活
- 匯總效用列
輸出應該是：
name date sum_avail
aa 9/1/2021 11
bb 9/1/2021 2
我試過的：
SELECT name, date, SUM(avail) AS sum_avail
FROM table
(WHERE stack NOT LIKE 'unused' AND WHERE life IN ('keep','stay') AND WHERE date == '9/1/2021')
(WHERE stack NOT LIKE 'unused' AND WHERE life IN ('keep') AND WHERE date == '9/1/2021')
GROUP BY name
任何建議都是有幫助的。
uj5u.com熱心網友回復：
您可以使用select case statement來實作這一點。
select [name], sum(case when life in ('keep','stay') then avail else 0 end)
sum(case when life in ('keep') then avail else 0 end)
from test
where date = '9/1/2021' and stack!='unused'
group by [name]
見 dbfiddle http://sqlfiddle.com/#!18/6895b/26
uj5u.com熱心網友回復：
SELECT name,date, SUM(avail) sum_avail FROM
(SELECT * from Table_1 WHERE stack != 'unused' AND life IN ('keep','stay') AND date='2021-09-01'
UNION ALL
SELECT * from Table_1 WHERE stack != 'unused' AND life IN ('keep') AND date='2021-09-01') A
GROUP BY name, date
uj5u.com熱心網友回復：
根據邏輯使用單個 SUM() 和多個案例運算式。作為按單個日期搜索，然后使用 MAX() 作為日期，但如果需要日期范圍搜索，則使用日期列來選擇和按部分分組。
-- SQL Server (v2017)
SELECT name, MAX(date) "date"
, SUM(CASE WHEN life IN ('keep','stay') THEN avail ELSE 0 END
CASE WHEN life = 'keep' THEN avail ELSE 0 END) sum_avail
FROM test_tbl
WHERE date = '9/1/2021'
AND stack != 'unused'
GROUP BY name;
請從網址https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=98f4dcee17dfe8c54edf7ab3e6fed561檢查
uj5u.com熱心網友回復：
SELECT [name]
,[date]
,SUM(avail) as sum_avail FROM
(SELECT * from Test WHERE life IN ('keep','stay')
UNION ALL
SELECT * from Test WHERE life IN ('keep') ) as Test1
WHERE stack != 'unused' AND date='2021-01-09'
GROUP BY [name],[date]
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/415068.html
標籤：