SQL ,鸟查询。问题很简单，就是想不出来
id a b c----------- ----------- ----------- -----------1 2 3 41 3 5 41 3 7 41 3 7 82 2 3 42 5 3 82 5 3 82 7 3 82 1 9 9(9 行受影响)
create table #test (id int, a int, b int , c int)insert #test select 1,2,3,4 union all select 1,3,5,4 union allselect 1,3,7,4 union allselect 1,3,7,8 union all select 2,2,3,4 union all select 2,5,3,8 union all select 2,5,3,8 union all select 2,7,3,8 union allselect 2,1,9,9
create table tLife(id int,a int,b int,c int)insert into tLife select 1 , 2, 3, 4 union allselect 1 , 3, 5, 4 union allselect 1 , 3, 7, 4 union allselect 1 , 3, 7, 8 union allselect 2 , 2, 3, 4 union allselect 2 , 5, 3, 8 union allselect 2 , 5, 3, 8 union allselect 2 , 7, 3, 8 union allselect 2 , 1, 9, 9goselect * from tLifeexceptselect *from tLife twhere not exists (select 1 from tLife where id = t.id and a+b+c > t.a+t.b+t.c)drop table tLife/********************id a b c----------- ----------- ----------- -----------1 2 3 41 3 5 41 3 7 42 2 3 42 5 3 82 7 3 8(6 行受影响)
[解决办法]
select A.*from #test Ainner join (select id,max(a) as a from #test group by id) T on A.a <> T.a and A.id =T.idinner join (select id,max(b) as b from #test group by id) T1 on A.b <> T1.b and A.id =T1.idinner join (select id,max(c) as c from #test group by id) T2 on A.c <> T2.c and A.id =T2.id/*1 2 3 42 2 3 42 5 3 82 5 3 8*/
