select * from 表 a where time=( select max(time) from 表 where type=a.type)
--或: select a.* from 表 a,( select type,time=max(time) from 表 group by type )b where a.type=b.type and a.time=b.time
--如果同一type,最大的time会重复,则用:select a.type,a.time, amount=max(a.amount) from 表 a,( select type,time=max(time) from 表 group by type )b where a.type=b.type and a.time=b.time group by a.type,a.time
select t.* from t,(select type,max(time) time from t group by type) a where t.type=a.type and t.time=a.time
--示例数据(为显示好看,我把time用字符型) declare @t table(type varchar(10),time varchar(10),amount int) insert @t select 'A','2004-8-10',300 union all select 'A','2004-8-02',400 union all select 'A','2004-8-06',300 union all select 'B','2004-8-02',200 union all select 'B','2004-8-09',150 union all select 'B','2004-8-01',400 union all select 'C','2004-8-10',200 union all select 'C','2004-8-06',150 union all select 'C','2004-8-12',400 union all select 'D','2004-8-06',150 --添加三条记录 union all select 'D','2004-8-12',400 union all select 'D','2004-8-12',150--查询1 select * from @t a where time=( select max(time) from @t where type=a.type) order by a.type/*--结果: type time amount ---------- ---------- ----------- A 2004-8-10 300 B 2004-8-09 150 C 2004-8-12 400 D 2004-8-12 400 --type='D'的有两条 D 2004-8-12 150(所影响的行数为 5 行) --*/ --查询2 select a.* from @t a,( select type,time=max(time) from @t group by type )b where a.type=b.type and a.time=b.time order by a.type/*--结果 type time amount ---------- ---------- ----------- A 2004-8-10 300 B 2004-8-09 150 C 2004-8-12 400 D 2004-8-12 400 --type='D'的有两条 D 2004-8-12 150(所影响的行数为 5 行) --*/ --查询3 select a.type,a.time, amount=max(a.amount) from @t a,( select type,time=max(time) from @t group by type )b where a.type=b.type and a.time=b.time group by a.type,a.time order by a.type/*--结果type time amount ---------- ---------- ----------- A 2004-8-10 300 B 2004-8-09 150 C 2004-8-12 400 D 2004-8-12 400 --只有一条(所影响的行数为 4 行) --*/
where time=(
select max(time) from 表 where type=a.type)
--或:
select a.*
from 表 a,(
select type,time=max(time)
from 表 group by type
)b where a.type=b.type and a.time=b.time
--如果同一type,最大的time会重复,则用:select a.type,a.time, amount=max(a.amount)
from 表 a,(
select type,time=max(time)
from 表 group by type
)b where a.type=b.type and a.time=b.time
group by a.type,a.time
where t.type=a.type and t.time=a.time
-如果同一type,最大的time会重复
本来不麻烦,结果看晕了。呵呵,
3个有什么不同...这第三个取出了类别相同时间相同的amount最大的记录
declare @t table(type varchar(10),time varchar(10),amount int)
insert @t select 'A','2004-8-10',300
union all select 'A','2004-8-02',400
union all select 'A','2004-8-06',300
union all select 'B','2004-8-02',200
union all select 'B','2004-8-09',150
union all select 'B','2004-8-01',400
union all select 'C','2004-8-10',200
union all select 'C','2004-8-06',150
union all select 'C','2004-8-12',400
union all select 'D','2004-8-06',150 --添加三条记录
union all select 'D','2004-8-12',400
union all select 'D','2004-8-12',150--查询1
select * from @t a
where time=(
select max(time) from @t where type=a.type)
order by a.type/*--结果:
type time amount
---------- ---------- -----------
A 2004-8-10 300
B 2004-8-09 150
C 2004-8-12 400
D 2004-8-12 400 --type='D'的有两条
D 2004-8-12 150(所影响的行数为 5 行)
--*/
--查询2
select a.*
from @t a,(
select type,time=max(time)
from @t group by type
)b where a.type=b.type and a.time=b.time
order by a.type/*--结果
type time amount
---------- ---------- -----------
A 2004-8-10 300
B 2004-8-09 150
C 2004-8-12 400
D 2004-8-12 400 --type='D'的有两条
D 2004-8-12 150(所影响的行数为 5 行)
--*/
--查询3
select a.type,a.time, amount=max(a.amount)
from @t a,(
select type,time=max(time)
from @t group by type
)b where a.type=b.type and a.time=b.time
group by a.type,a.time
order by a.type/*--结果type time amount
---------- ---------- -----------
A 2004-8-10 300
B 2004-8-09 150
C 2004-8-12 400
D 2004-8-12 400 --只有一条(所影响的行数为 4 行)
--*/