table表有三个字段: id, time, name
我想取出按照name进行分组的最小time集合(就是每个name的最小time),并且取出对应的id值,
例如:id time name
-----------------
0 5:00 zhang
1 6:00 wang
2 4:00 zhang
3 3:00 wang
4 3:30 wang
我想要的结果是id name min(time)
---------------------
2 zhang 4:00
3 wang 3:00我就是不清楚怎么把id搞定,谢谢各位高人。
我想取出按照name进行分组的最小time集合(就是每个name的最小time),并且取出对应的id值,
例如:id time name
-----------------
0 5:00 zhang
1 6:00 wang
2 4:00 zhang
3 3:00 wang
4 3:30 wang
我想要的结果是id name min(time)
---------------------
2 zhang 4:00
3 wang 3:00我就是不清楚怎么把id搞定,谢谢各位高人。
t.*
from
表 t
where
not exists(select 1 from 表 where name=t.name and [time]<t.[time])
t.*
from
表 t
where
t.id=(select top 1 id from 表 where name=t.name order by [time])
inner join
(select name,min(time) mt from tb group by name) b
on a.name=b.name and a.time=b.mt
a.*
from
表 a,
(select name,min([time]) as time from 表 group by name) b
where
a.name=b.name and a.[time]=b.[time]
INSERT INTO @tb
SELECT 0, '5:00', 'zhang'
UNION ALL SELECT 1, '6:00', 'wang'
UNION ALL SELECT 2, '4:00', 'zhang'
UNION ALL SELECT 3, '3:00', 'wang'
UNION ALL SELECT 4, '3:30', 'wang'SELECT * FROM @tb T WHERE NOT EXISTS(SELECT 1 FROM @tb WHERE name = T.name AND time < T.time)
drop table tb
gocreate table tb
(
id int,
time varchar(10),
name varchar(10)
)insert into tb(id,time,name) values(0,'5:00','zhang')
insert into tb(id,time,name) values(1,'6:00','wang')
insert into tb(id,time,name) values(2,'4:00','zhang')
insert into tb(id,time,name) values(3,'3:00','wang')
insert into tb(id,time,name) values(4,'3:30','wang')select a.* from tb a,
(select name , min(time) as time from tb group by name) b
where a.name = b.name and a.time = b.time
order by a.name descdrop table tbid time name
----------- ---------- ----------
2 4:00 zhang
3 3:00 wang(所影响的行数为 2 行)
drop table tb
gocreate table tb
(
id int,
time varchar(10),
name varchar(10)
)insert into tb(id,time,name) values(0,'5:00','zhang')
insert into tb(id,time,name) values(1,'6:00','wang')
insert into tb(id,time,name) values(2,'4:00','zhang')
insert into tb(id,time,name) values(3,'3:00','wang')
insert into tb(id,time,name) values(4,'3:30','wang')select a.* from tb a,
(select name , max(time) as time from tb group by name) b
where a.name = b.name and a.time = b.time
order by a.name descdrop table tbid time name
----------- ---------- ----------
0 5:00 zhang
1 6:00 wang(所影响的行数为 2 行)