时间戳

首页 > 解决方案 > TSQL to perform aggregation with Exists

sql - TSQL to perform aggregation with Exists

问题描述

My sample data source is following

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)

I want SQL to check if there exists any row with cond=X or cond=Y by site and if yes, don't perform any aggregation; if no, perform a sum of val.

I am currently achieving it by following

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

I was wondering if there is any better way of achieving this.

Edit

Desired Result

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

标签： sqlsql-servertsql

解决方案

…小提琴

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;

推荐阅读