过滤并执行多个计算

Question

我有一个数据集，我想使用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

理想(逻辑)

1. Group的名称和筛选日期为9/1/2021
2. ，其中堆栈!=“未使用的”
3. ，以及生命在“保存”和“停留”的

中。

+

按名称和筛选到9/1/2021

• WHERE堆栈!=“未使用”的

• 的日期和'keep'

• Summing

• 列

中的生存期

输出应该是：

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

任何建议都是有用的。

Answer 1

您可以使用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

Answer 2

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

Answer 3

按照逻辑使用带有多个case表达式的单SUM()。如果按单个日期进行搜索，则使用MAX()进行日期搜索，但如果需要搜索日期范围，则可以使用日期列( select )和按部分分组。

-- 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;

请从url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=98f4dcee17dfe8c54edf7ab3e6fed561查询