基于列值中的一个条件选择SQL

Can someone help me with the SQL query using the below data?

有人可以使用以下数据帮助我查询SQL查询吗?

Whenever I see the type as deactivating irrespective of other types in same-ID I have to get only deactivate values and the rest of values should be null.

每当我看到类型为停用而不管同一ID中的其他类型时,我必须仅获取停用值,其余值应为空。

select     id,     
pid,    
type,    
case when type='deactivate' then null as value1 else value1 end,     
case when type='deactivate' then  null as value2 else value2 end,     
case when type='deactivate' then  null as value3 else value3 end,     
case when type='deactivate' then null as value4 else value4 end,     
case when type='deactivate' then null as value5 else value5 end,     
case when type='deactivate' then value6 end,     
case when type='deactivate' then deactivate_date end,     
case when type='deactivate' then status end     
from typetable

But whenever the pid changes I am getting values in value1 to 5. Is there any way in my data whenever I see type='deactivate' I have to ignore the values from value1 to 5 for all the types in the same ID? The output should be like nulls for ID- 1 & 3 mentioned below.

但是每当pid发生变化时,我都会得到value1到5的值。当我看到type ='deactivate'时,我的数据中是否有任何方法我必须忽略同一ID中所有类型的值1到5的值?输出应该类似于下面提到的ID-1和3的空值。

id  pid type    value1  value2  value3  value4  value5  value6  deactive date   Status
0   1   case    99  null    null    null    null    null    null    
0   2   test    null    101 null    null    null    null    null    
0   3   levels  null    null    CAAD    null    null    null    null    
0   4   package null    null    null    null    DSIT    null    null    

1   1   case    null    null    null    null    null    null    null    
1   2   test    null    null    null    null    null    null    null    
1   3   levels  null    null    null    null    null    null    null    
1   4   package null    null    null    null    null    null    null    
1   5   deactivate  null    null    null    null    null    9999999 9/1/2014    Void ID

2   1   case    CLR 6#  null    null    null    null    null    null    
2   2   test    null    6   null    null    null    null    null    
2   3   levels  null    null    MAA null    null    null    null    

3   1   case    null    null    null    null    null    null    null    
3   2   test    null    null    null    null    null    null    null    
3   3   levels  null    null    null    null    null    null    null    
3   4   package null    null    null    null    null    null    null    
3   5   deactivate  null    null    null    null    null    9999999 1/7/2016    Closed

3 个解决方案

#1

declare @t table (id int, pid int, col1 varchar(10), col2 varchar(10)) 
insert into @t 
       values (1, 1, 'askjdf', 'laskjdf')
            , (1, 2, 'asksfjdf', 'ljdf')
            , (2, 1, 'askjdf', 'laskjdf')
            , (2, 2, 'deact', 'laskjdf');

select t.* 
from @t t 
where not exists (select 1 from @t tm where tm.id = t.id and tm.col1 = 'deact') 
union all 
select t.id, t.pid, t.col1, null  
from @t t 
where     exists (select 1 from @t tm where tm.id = t.id and tm.col1 = 'deact') 
order by id, pid;

#2

Something like this would work:

这样的东西会起作用:

select
    id, pid,
    case when masked = 0 then value 1 else null end as value1,
    ...
from <table> t cross apply (
    select max(case when t2.type = 'deactivate' then 1 else 0 end)
    from <table> t2
    where t2.id = t.id
) m(masked)

Similar, and probably better:

类似,可能更好:

with data as (
    select *,
        max(case when type = 'deactivate' then 1 else 0 end)
            over (partition by id) as masked
    from <table>
)
select
    id, pid,
    case when masked = 0 then value 1 else null end as value1,
    ...
from data;

#3

Cleanest and easiest if you make a self left join on id AND type='deactivate' and check the joining table's type field rather than the left table's type field:

如果你在id AND type ='deactivate'上进行自我左连接并且检查连接表的类型字段而不是左表的类型字段,则最干净,最简单:

select t1.id,t1.pid,t1.type,
case when t2.type='deactivate' then null else t1.value1 end AS value1,
case when t2.type='deactivate' then null else t1.value2 end AS value2, 
case when t2.type='deactivate' then null else t1.value3 end AS value3, 
case when t2.type='deactivate' then null else t1.value4 end AS value4, 
case when t2.type='deactivate' then null else t1.value5 end AS value5, 
case when t2.type='deactivate' then t1.value6 end AS value6, 
case when t2.type='deactivate' then t1.deactivate_date end AS deactivate_date, 
case when t2.type='deactivate' then t1.status end from typetable t1 
LEFT JOIN typetable t2 ON t1.id=t2.id AND t2.type='deactivate'

#1

declare @t table (id int, pid int, col1 varchar(10), col2 varchar(10)) 
insert into @t 
       values (1, 1, 'askjdf', 'laskjdf')
            , (1, 2, 'asksfjdf', 'ljdf')
            , (2, 1, 'askjdf', 'laskjdf')
            , (2, 2, 'deact', 'laskjdf');

select t.* 
from @t t 
where not exists (select 1 from @t tm where tm.id = t.id and tm.col1 = 'deact') 
union all 
select t.id, t.pid, t.col1, null  
from @t t 
where     exists (select 1 from @t tm where tm.id = t.id and tm.col1 = 'deact') 
order by id, pid;

#2