如何根据标志位选择数据
表结构和数据:
a b c ....flg
111 1 1 ..... 1
111 1 1 ..... 0
222 1 1 ..... 1
333 1 1 ..... 0
选择数据的规则:选出所有flg为0的数据,根据a,b,c3个字段,如果有flg为0的记录,则需要选出其所有flg为1的记录,希望的结果如下:
a b c ....flg
111 1 1 ..... 1
111 1 1 ..... 0
333 1 1 ..... 0
哪位这样的查询如何写呢?谢谢。
[解决办法]
create table #test(a int,b int ,c int, flg int)
insert into #test
select 111,1,1,1 union all
select 111,1,1,0 union all
select 222,1,1,1 union all
select 333,1,1,0
select * from #test where flg = 0
union
select * from #test t where flg = 1 and exists(select 1 from #test where #test.a = t.a
and #test.b = t.b and #test.c = t.c and #test.flg <> t.flg)
drop table #test
[解决办法]
这样就行了
select * from ta as a
where exists(select 1 from ta where a=a.a and b=a.b and c=a.c and flg=0 )
[解决办法]
declare @a table(a int, b int, c int,flg int)
insert @a select 111, 1 ,1, 1
union all select 111 ,1, 1, 0
union all select 222, 1 ,1, 1
union all select 333 ,1, 1 , 0
select a.a,a.b,a.c,a.flg from @a a Inner Join (select * from @a where flg=0) b on a.a=b.a and a.b=b.b and a.c=b.c