假设有个表内的内容如下:
A B C
————————————
0001 1 A002
0001 1 A005
0001 2 A005
0002 5 A008
0003 3 B001
0003 1 B009
要得到的结果是
A B C
————————————
0001 1 A005
0002 5 A008
0003 1 B009
按照A进行分组,选择B最小的记录,如果有B相等的记录,选择C最大的记录参见http://community.csdn.net/Expert/topic/4724/4724646.xml?temp=.7522852,当时没有考虑到有B相同的情况
A B C
————————————
0001 1 A002
0001 1 A005
0001 2 A005
0002 5 A008
0003 3 B001
0003 1 B009
要得到的结果是
A B C
————————————
0001 1 A005
0002 5 A008
0003 1 B009
按照A进行分组,选择B最小的记录,如果有B相等的记录,选择C最大的记录参见http://community.csdn.net/Expert/topic/4724/4724646.xml?temp=.7522852,当时没有考虑到有B相同的情况
Insert into @tbl
select '0001', 1 ,'A002'
union select '0001', 1 ,'A005'
union select '0001', 2 ,'A005'
union select '0002', 5 ,'A008'
union select '0003', 3 ,'B001'
union select '0003', 1 ,'B009'select A,B,max(C) as C from @tbl t
where not exists( select * from @tbl
where t.A=A and t.B>B)
group by A,B
select t2.a,t2.b,max(t3.c) as c
from
(select a,b = (select min(b) from table where a = t1.a)
from table t1
)t2,table t3
where t2.a = t3.a and t2.b = t3.b
from tablename a
group by A
goinsert into #t values('0001', 1, 'A002')
insert into #t values('0001', 1, 'A005')
insert into #t values('0001', 2, 'A005')
insert into #t values('0002', 5, 'A008')
insert into #t values('0003', 3, 'B001')
insert into #t values('0003', 1, 'B009')
goselect A,min(B),max(C)
from #t
group by A
insert into @t
select '0001', 1, 'A002' union
select '0001', 1, 'A005' union
select '0001', 2, 'A005' union
select '0002', 5, 'A008' union
select '0003', 3, 'B001' union
select '0003', 1, 'B009' select t1.a,t1.b , max(t2.c) as c
from
(
select a,
min(b) as b
from @t
group by a
) t1
inner join @t t2 on t1.a = t2.a
group by t1.a,t1.b
按A分组,而后取出最小的B,而C列一定要与B在同一条记录上。所以
lw1a2(一刀 Blog:http://blog.csdn.net/lw1a2/) 可能不对
A B C D
————————————
0001 1 A002 3
0001 1 A005 1
0001 2 A005 2
0002 5 A008 3
0003 3 B001 1
0003 1 B009 6
要得到的结果是
A B C
————————————
0001 1 A002
0002 5 A008
0003 1 B009
按照A进行分组,选择B最小的记录,如果有B相等的记录,选择D最大的记录
正如liangpei2008(Crystal)所说,按A分组,而后取出最小的B,如果最B有相等的情况,取D最大的记录,而C列一定要与B,D在同一条记录上如果D不在选择范围内,那不是要嵌套两次?
declare @t table(a varchar(4),b int , c varchar(4))
insert into @t
select '0001', 1, 'A002' union
select '0001', 1, 'A005' union
select '0001', 2, 'A005' union
select '0002', 5, 'A008' union
select '0003', 3, 'B001' union
select '0003', 1, 'B009'
SELECT *
from @t as t
where t.b<= all
(
select b
from @t as t1
where t.a=t1.a
)
and t.c>= all
(
select c
from @t as t2
where t.a=t2.a
)