TSQL对Exists执行聚合

Question

我的示例数据源如下

declare @t1 as table
(
sn int,
site varchar(max),
cond varchar(max),
val int
)
insert into @t1
select *
from
(values
(1, 'site1', 'X', 100),
(2, 'site1', 'Y', 200),
(3, 'site1', 'Z', 300),
(1, 'site2', 'A', 100),
(2, 'site2', 'B', 200),
(3, 'site2', 'C', 300),
(1, 'site3', 'X', 100),
(2, 'site3', 'P', 200),
(3, 'site3', 'Q', 300),
(1, 'site4', 'A', 100),
(2, 'site4', 'Y', 200),
(3, 'site4', 'Q', 300),
(1, 'site5', 'E', 100), 
(1, 'site5', 'E', 1000),
(2, 'site5', 'F', 200),
(3, 'site5', 'G', 300)
) t (a, b, c, d)

我希望SQL检查是否存在cond=X或cond=Y的任何行，如果是，不执行任何聚合;如果不存在，则对val进行求和。

我目前正在通过以下方式实现它

Method1

select  
ax.sn, ax.site, ax.cond,
sum(ax.revisedVal) as revisedTotal
from
(select distinct 
a.sn, a.site, a.cond, a.val, t.one, m.revisedVal
from 
@t1 a
outer apply
(select * 
from 
(select *, 1 as one 
from @t1 b 
where b.cond = 'Y' or b.cond = 'X') x 
where a.site = x.site) t
outer apply 
(values (case when t.one = 1 then null else a.val end)) m(revisedVal))ax
group by 
ax.sn, ax.site, ax.cond
order by 
ax.site, ax.sn

Method 2

SELECT a.sn,
a.site,
a.cond,
Sum(CASE
WHEN a.site = x.site THEN a.val
ELSE NULL
END) AS revisedVal
FROM   @t1 a
OUTER apply (SELECT b.site
FROM   (SELECT site
FROM   @t1
WHERE  cond <> 'X'
OR cond <> 'Y'
EXCEPT
SELECT site
FROM   @t1
WHERE  cond = 'X'
OR cond = 'Y') b
WHERE  a.site = b.site) x
GROUP  BY a.site,
a.sn,
a.cond 

我想知道是否有更好的方法来实现这一点。

编辑

想要的结果

| sn | site  | cond | revisedTotal |
|----|-------|------|--------------|
| 1  | site1 | X    | NULL         |
| 2  | site1 | Y    | NULL         |
| 3  | site1 | Z    | NULL         |
| 1  | site2 | A    | 100          |
| 2  | site2 | B    | 200          |
| 3  | site2 | C    | 300          |
| 1  | site3 | X    | NULL         |
| 2  | site3 | P    | NULL         |
| 3  | site3 | Q    | NULL         |
| 1  | site4 | A    | NULL         |
| 2  | site4 | Y    | NULL         |
| 3  | site4 | Q    | NULL         |
| 1  | site5 | E    | 1100         |
| 2  | site5 | F    | 200          |
| 3  | site5 | G    | 300          |

Answer 1

…

select ax.sn, ax.site, ax.cond, sum(case when ax.one = 1 then null else ax.val end) as revisedTotal
from
(
select *, max(case when cond in ('Y', 'X') then 1 else 0 end) over(partition by site) as one
from @t1
) as ax
group by ax.sn, ax.site, ax.cond
order by ax.site, ax.sn;

Answer 2

这听起来像是聚合查询上的having子句:

select site, sum(val)
from @t t
group by site
having sum(case when cond = X or cond = Y then 1 else 0 end) = 0;

编辑:

with agg as (
select site, sum(val) as val
from @t t
group by site
having sum(case when cond = X or cond = Y then 1 else 0 end) = 0
)
select site, val
from agg
union all
select site, val
from @t t
where not exists (select 1 from agg where agg.site = t.site);

Answer 3

This:

SELECT t1.site, SUM(t1.val)
from @t1 t1
left join @t1 t2 on t2.site=t1.site and t2.cond in ('X','Y')
where t2.site is null
group by t1.site;

在不存在值为'X'或'Y'的val的情况下，对每个站点的val的所有值求和。

看到:DBFIDDLE

或者，加上其他列:

SELECT t1.sn, t1.site, t1.cond, SUM(t1.val)
from @t1 t1
left join @t1 t2 on t2.site=t1.site and t2.cond in ('X','Y')
where t2.site is null
group by t1.sn, t1.site, t1.cond;