滤重后，加上第一次出现的时间，帮忙！拜托!

[jason_sun22]

name class time
------------------------------------
jason 1 2012-01-15
jason 1 2012-01-16
mike 2 2012-01-16

需要对name+class滤重，可用group by name,class
但是还要加上时间就不会了，请大家帮忙～～

要求结果：name+class，第一次出现的时间
即如下：

name class time
------------------------------------
jason 1 2012-01-15
mike 2 2012-01-16

[快溜]

select name,class,min([time]) from tb group by name,class

[百年树人]

select name,class,min(time) as time
from tb 
group by name,class

--or

select *
from tb t
where not exists(select 1 from tb where name=t.name and time<t.time)

[勿勿]

--name class time
--------------------------------------
--jason 1 2012-01-15
--jason 1 2012-01-16
--mike 2 2012-01-16

--需要对name+class滤重，可用group by name,class
--但是还要加上时间就不会了，请大家帮忙～～

--要求结果：name+class，第一次出现的时间
--即如下：

--name class time
--------------------------------------
--jason 1 2012-01-15
--mike 2 2012-01-16

if OBJECT_ID('tb') is not null
 drop table tb
  go 
create table tb (name varchar(50),class int ,[time] varchar(50))
insert into tb select 'jason', 1, '2012-01-15' union all
select 'jason' ,1, '2012-01-16' union all
select 'mike' ,1, '2012-01-16'  
 
select * from tb  t where [time]=(select min(time) from tb where name=t.name)


name                                               class       time
-------------------------------------------------- ----------- --------------------------------------------------
jason                                              1           2012-01-15
mike                                               1           2012-01-16

(2 行受影响)

[AcHerat]

select name,class,min([time]) as minTime
from tb 
group by name,class

[勿勿]

--name class time
--------------------------------------
--jason 1 2012-01-15
--jason 1 2012-01-16
--mike 2 2012-01-16

--需要对name+class滤重，可用group by name,class
--但是还要加上时间就不会了，请大家帮忙～～

--要求结果：name+class，第一次出现的时间
--即如下：

--name class time
--------------------------------------
--jason 1 2012-01-15
--mike 2 2012-01-16

if OBJECT_ID('tb') is not null
 drop table tb
  go 
create table tb (name varchar(50),class int ,[time] varchar(50))
insert into tb select 'jason', 1, '2012-01-15' union all
select 'jason' ,1, '2012-01-16' union all
select 'mike' ,1, '2012-01-16'  
 select name,class,min(time) as time
from tb 
group by name,class


 name                                               class       time
-------------------------------------------------- ----------- --------------------------------------------------
jason                                              1           2012-01-15
mike                                               1           2012-01-16

(2 行受影响)

[jason_sun22]

原来可以这样用，谢谢各位，已结帖～～

[jmx123456789]

if OBJECT_ID('tb') is not null
 drop table tb
  go 
create table tb (name varchar(50),class int ,[time] varchar(50))
insert into tb select 'jason', 1, '2012-01-15' union all
select 'jason' ,1, '2012-01-16' union all
select 'mike' ,1, '2012-01-16'  


select name,class,MIN(time) from tb group by name,class

------------------------------
jason	1	2012-01-15
mike	1	2012-01-16

[苦苦的潜行者]

不用group by

select * from t1 as a
where not exists(select 1 from t1 
where a.name=name and a.class=class and a.time>time)
/*
name   class time
---   ----  ----
jason	1	2012-01-15 00:00:00.000
mike	2	2012-01-16 00:00:00.000
*/

[jason_sun22]

xiaolinyouni朋友的方法也不错，谢谢～～

[苦苦的潜行者]

--query 1
select name,class,time=MIN(time) from t1 
group by name,class
go
--query 2
select * from t1 as a
where not exists(select 1 from t1 
where a.name=name and a.class=class and a.time>time)

效率对比:

[jason_sun22]

嗯，不用 group by 效率高，谢谢～～