declare @test table(item int, store int, line int, dsc varchar(20),n1 varchar(2), n2 varchar(2))
insert @test select 0001, 381001, 20, 'Success', 'OP', 'XR' union all
select 0001, 381001, 20, 'false', 'OF', 'XT' union all
select 0002, 381001, 30, 'Success', 'OP', 'XR' union all
select 0002, 381001, 30, 'false', 'OF', 'XT' union all
select 0003, 381001, 10, 'Success', 'OP', 'XR' union all
select 0003, 381001, 10, 'false', 'OF', 'XT' union all
select 0004, 381001, 40, 'Success', 'OP', 'XR' union all
select 0004, 381001, 40, 'false', 'OF', 'XT'
--------------------------------------------------------------
item store line dsc n1 n2
---------------------------------------------------------------
1 381001 20 Success OP XR
1 381001 20 false OF XT
2 381001 30 Success OP XR
2 381001 30 false OF XT
3 381001 10 Success OP XR
3 381001 10 false OF XT
4 381001 40 Success OP XR
4 381001 40 false OF XT对于同一个item,store,line 来说一定对应两个dsc,现在希望输出Success的dsc,和false后面的字段n1,n2
即结果如下
--------------------------------------------------------------
item store line dsc n1 n2
---------------------------------------------------------------
1 381001 20 Success OF XT
2 381001 30 Success OF XT
3 381001 10 Success OF XT
4 381001 40 Success OF XT
,dsc=max(case when dsc='Success' then dsc else null end)
,n1=max(case when dsc='false' then n1 else null end)
,n2=max(case when dsc='false' then n2 else null end)
from @test group by item,store,line
insert @test select 0001, 381001, 20, 'Success', 'OP', 'XR' union all
select 0001, 381001, 20, 'false', 'OF', 'XT' union all
select 0002, 381001, 30, 'Success', 'OP', 'XR' union all
select 0002, 381001, 30, 'false', 'OF', 'XT' union all
select 0003, 381001, 10, 'Success', 'OP', 'XR' union all
select 0003, 381001, 10, 'false', 'OF', 'XT' union all
select 0004, 381001, 40, 'Success', 'OP', 'XR' union all
select 0004, 381001, 40, 'false', 'OF', 'XT'
--select * from @test--1 381001 20 Success OF XT
--2 381001 30 Success OF XT
--3 381001 10 Success OF XT
--4 381001 40 Success OF XT
select t.item,t.store,t.line,t.dsc,m.n1,m.n2
from @test t ,(select * from @test where dsc ='false' ) m
where t.item = m.item and t.dsc = 'Success'
and t.store = m.store
insert @test select 0001, 381001, 20, 'Success', 'OP', 'XR' union all
select 0001, 381001, 20, 'false', 'OF', 'XT' union all
select 0002, 381001, 30, 'Success', 'OP', 'XR' union all
select 0002, 381001, 30, 'false', 'OF', 'XT' union all
select 0003, 381001, 10, 'Success', 'OP', 'XR' union all
select 0003, 381001, 10, 'false', 'OF', 'XT' union all
select 0004, 381001, 40, 'Success', 'OP', 'XR' union all
select 0004, 381001, 40, 'false', 'OF', 'XT' select t.item,t.store,t.line,t.dsc,t1.n1,t1.n2
from
(
select * from @test where dsc='Success'
)
t
left join
(
select * from @test where dsc='false'
)
t1
on t.store=t1.store
and t.line=t1.line
and t.item=t1.itemitem store line dsc n1 n2
----------- ----------- ----------- -------------------- ---- ----
1 381001 20 Success OF XT
2 381001 30 Success OF XT
3 381001 10 Success OF XT
4 381001 40 Success OF XT(4 行受影响)
declare @test table(item int, store int, line int, dsc varchar(20),n1 varchar(2), n2 varchar(2))
insert @test select 0001, 381001, 20, 'Success', 'OP', 'XR' union all
select 0001, 381001, 20, 'false', 'OF', 'XT' union all
select 0002, 381001, 30, 'Success', 'OP', 'XR' union all
select 0002, 381001, 30, 'false', 'OF', 'XT' union all
select 0003, 381001, 10, 'Success', 'OP', 'XR' union all
select 0003, 381001, 10, 'false', 'OF', 'XT' union all
select 0004, 381001, 40, 'Success', 'OP', 'XR' union all
select 0004, 381001, 40, 'false', 'OF', 'XT' select t1.item,t1.store,t1.line
,n1=(select n1 from @test t2 where t1.item=t2.item and t1.store=t2.store and t1.line=t2.line and dsc='false')
,n2=(select n2 from @test t2 where t1.item=t2.item and t1.store=t2.store and t1.line=t2.line and dsc='false')
from @test t1 where t1.dsc<>'false'--1 381001 20 OF XT
--2 381001 30 OF XT
--3 381001 10 OF XT
--4 381001 40 OF XT
insert @test select 0001, 381001, 20, 'Success', 'OP', 'XR' union all
select 0001, 381001, 20, 'false', 'OF', 'XT' union all
select 0002, 381001, 30, 'Success', 'OP', 'XR' union all
select 0002, 381001, 30, 'false', 'OF', 'XT' union all
select 0003, 381001, 10, 'Success', 'OP', 'XR' union all
select 0003, 381001, 10, 'false', 'OF', 'XT' union all
select 0004, 381001, 40, 'Success', 'OP', 'XR' union all
select 0004, 381001, 40, 'false', 'OF', 'XT'
select * from @test where dsc = 'Success'/*
item store line dsc n1 n2
----------- ----------- ----------- -------------------- ---- ----
1 381001 20 Success OP XR
2 381001 30 Success OP XR
3 381001 10 Success OP XR
4 381001 40 Success OP XR(所影响的行数为 4 行)
*/
,(select n1 from @test where store=t.store and line=t.line and dsc='false') as n1
,(select n2 from @test where store=t.store and line=t.line and dsc='false') as n2
from @test t where t.dsc='Success'