SQL ,鸟查询。问题很简单，就是想不出来
- SQL code
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 行受影响)
抓出除最大值以外的数据。就是把每个字段的最大值拿掉，按ID分组，也就是说A列有两个最大值。3和7 以此类推
- SQL code
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
源码
[解决办法]
变态的需求，结贴给分吧
[解决办法]
- SQL code
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 行受影响)
[解决办法]
- SQL code
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*/