我有一個資料集，我想用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修復9/1/2021 1
bb是洗掉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未使用的移除9/1/2021 3
bb ok keep 10/1/2021 1
期望 (邏輯)
輸出應該是：
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陳述句來實作。
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 測驗
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 測驗WHERE生活 IN ('keep', 'stay')
UNION ALLSELECT * from 測驗 WHERE生活 IN ('keep') ) as Test1
WHERE stack ! = 'unused' AND date='2021-01-09'
GROUP BY [name], [date]
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/311091.html
標籤：
上一篇：用相同的值更新具有匹配列值的行的uniqueidentifier列
下一篇：濫用SQL中的聚合函式AVG()