表结构如下
id classid content types times
1 1 abc a 2006-06-01
2 1 abcsadf a 2006-06-05
3 1 abc b 2006-06-01
4 1 abcsacc b 2006-06-05现在我要取出按types分组后,times时间为最后的记录,types大约有七个不同的值吧.固定的,不是动态的.用什么方法好一些.并把语句写出来.
2 1 abcsadf a 2006-06-05
4 1 abcsacc b 2006-06-05
这是结果.或者以一行的结果返回也行.
a b
abcsadf abcsacc
id classid content types times
1 1 abc a 2006-06-01
2 1 abcsadf a 2006-06-05
3 1 abc b 2006-06-01
4 1 abcsacc b 2006-06-05现在我要取出按types分组后,times时间为最后的记录,types大约有七个不同的值吧.固定的,不是动态的.用什么方法好一些.并把语句写出来.
2 1 abcsadf a 2006-06-05
4 1 abcsacc b 2006-06-05
这是结果.或者以一行的结果返回也行.
a b
abcsadf abcsacc
where a.times = b.times and a.types = b.types
where not exists (select 1 from table where times>a.times and types=a.types)
where a.times = b.times and a.types = b.types
create table t
(
id int identity(1,1) not null,
classid int,
content varchar(100),
type varchar(10),
times datetime
)insert into t(classid,content,type,times)
select 1,'abc','a','2006-06-01'
union
select 1,'abcsadf','a','2006-06-05'
union
select 1,'abc','b','2006-06-01'
union
select 1,'abcsacc','b','2006-06-05'
select * from t
where times in(select a.times from
(select type,max(times) as times from t group by type) a)
drop table t
select * form TableName A
inner join
(
select types,max(times) times from TableName group by types
) B
on A.types=B.types and A.times=B.times