请教:分组sql语句如何编写?
表a
a1 a2 a3
-----------------
a 2005-5-1 123
a 2005-4-1 456
a 2005-6-1 789
b 2005-1-1 123
b 2005-3-1 333
按a1分组,取a2最大值,同时取a2最大值对应记录的a3值
结果应该为:
a 2005-6-1 789
b 2005-3-1 333语句select a1,max(a2) from 表a group by a1
只能a1,a2的值,如何取a3的值呢?请帮忙。
谢谢!
表a
a1 a2 a3
-----------------
a 2005-5-1 123
a 2005-4-1 456
a 2005-6-1 789
b 2005-1-1 123
b 2005-3-1 333
按a1分组,取a2最大值,同时取a2最大值对应记录的a3值
结果应该为:
a 2005-6-1 789
b 2005-3-1 333语句select a1,max(a2) from 表a group by a1
只能a1,a2的值,如何取a3的值呢?请帮忙。
谢谢!
insert into @t select 'a' , '2005-5-1', 123
union all select 'a','2005-4-1' ,456
union all select 'a','2005-6-1' ,789
union all select 'b','2005-1-1' ,123
union all select 'b','2005-3-1' ,333select * from @t a where not exists(select * from @t where a1=a.a1 and a2>a.a2)
(select a1,a2=max(a2) from 表
group by a1) b
where a.a2=b.a2
from 表a a,(select a1,max(a2) as a2 from 表a group by a1) b
where a.a1=b.a1 and a.a2=b.a2
select *
from @t a
where not exists(
select *
from @t
where a1=a.a1 and a2>a.a2
)
另外:
不好意思,刚才写得不全。
表a
a1 a2 a3 a4
---------------------
a 2005-5-1 123 1.11
a 2005-4-1 456 1.12
a 2005-6-1 789 2.10
b 2005-1-1 123 1.10
b 2005-3-1 333 1.22
按a1分组,取a2最大值,同时取a2最大值对应记录的a3值,对a4求和
结果应该为:
a 2005-6-1 789 4.33
b 2005-3-1 333 2.32语句select a1,max(a2),sum(a4) from 表a group by a1
只能a1,a2,a4的值,如何取a3的值呢?请帮忙。
谢谢!
create table c(a1 char(20), a2 char(20),a3 int ,a4 float )insert into c
select 'a','2005-5-1',123,1.11 union all
select 'a','2005-4-1',456,1.12 union all
select 'a','2005-6-1',789,2.10 union all
select 'b','2005-1-1',123,1.11 union all
select 'b','2005-3-1',333,1.22
select a1,max(a2),sum(a3),(select sum(a4) from c where c.a1=cc.a1 )
from c as cc
where a2=(select max(a2) from c where cc.a1=c.a1)
group by a1drop table c
insert into @t select 'a' , '2005-5-1', 123,1.11
union all select 'a','2005-4-1' ,456,1.12
union all select 'a','2005-6-1' ,789,2.10
union all select 'b','2005-1-1' ,123,1.10
union all select 'b','2005-3-1' ,333,1.22--查询
select a.a1,a.a2,a.a3,b.a4 from @t a,
(select a1,a2=max(a2),a4=sum(a4) from @t
group by a1) b
where a.a2=b.a2
--结果
/*
a1 a2 a3 a4
---------- ---------- ----------- ----------------------
a 2005-6-1 789 4.33
b 2005-3-1 333 2.32(2 行受影响)*/