--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(name varchar(8), type varchar(8), times datetime) insert into # select 'A', '计算机', '2010-11-04' union all select 'B', '经济学', '2010-11-04' union all select 'C', '政治学', '2010-11-03' union all select 'A', '计算机', '2010-11-03' union all select 'B', '经济学', '2010-11-03'select * from # t where not exists (select 1 from # where type=t.type and times>t.times)/* name type times -------- -------- ----------------------- A 计算机 2010-11-04 00:00:00.000 B 经济学 2010-11-04 00:00:00.000 C 政治学 2010-11-03 00:00:00.000 */
select name,type,max(times) as times from details group by name,type --or select * from details t where not exists(select 1 from details where name=t.name and type=t.type and times>t.times)
select name,type,max(times) as times from [表] group by name,type
select name,type,convert(varchar(12),max(times),111) from details group by type,name
学习 select name,type,max(times) as times from [表] group by name,type
if object_id('tempdb.dbo.#') is not null drop table #
create table #(name varchar(8), type varchar(8), times datetime)
insert into #
select 'A', '计算机', '2010-11-04' union all
select 'B', '经济学', '2010-11-04' union all
select 'C', '政治学', '2010-11-03' union all
select 'A', '计算机', '2010-11-03' union all
select 'B', '经济学', '2010-11-03'select * from # t where not exists (select 1 from # where type=t.type and times>t.times)/*
name type times
-------- -------- -----------------------
A 计算机 2010-11-04 00:00:00.000
B 经济学 2010-11-04 00:00:00.000
C 政治学 2010-11-03 00:00:00.000
*/
--or
select * from details t where not exists(select 1 from details where name=t.name and type=t.type and times>t.times)
from [表]
group by name,type
from [表]
group by name,type