table testid sname dat
--------------------------------------------------
6 A 2008-08-02 10:14:33.513
2 B 2008-08-02 10:14:33.513
1 A 2008-08-02 10:14:33.530
4 C 2008-08-02 10:14:33.530
3 C 2008-08-02 10:14:33.530
9 C 2008-08-02 10:14:33.530--------------------------------------------------我想得到结果是这样的6 A 2008-08-02 10:14:33.513
2 B 2008-08-02 10:14:33.513
4 C 2008-08-02 10:14:33.530
---sql :
select sname, min(dat) as dat,
id = (select top 1 id from test as a where a.sname = test.sname and a.dat = min(test.dat))
from test
group by sname
这样的效率太低
求效率高一点 sql--------
能明白我表白什么吗?
--------------------------------------------------
6 A 2008-08-02 10:14:33.513
2 B 2008-08-02 10:14:33.513
1 A 2008-08-02 10:14:33.530
4 C 2008-08-02 10:14:33.530
3 C 2008-08-02 10:14:33.530
9 C 2008-08-02 10:14:33.530 -------------------------------------------------- 我想得到结果是这样的 6 A 2008-08-02 10:14:33.513
2 B 2008-08-02 10:14:33.513
4 C 2008-08-02 10:14:33.530
---
from test a
where id=(select top 1 id from test where sname=a.sname order by dat)
declare @test table([id] int,[sname] nvarchar(1),[dat] Datetime)
Insert @test
select 6,N'A','2008-08-02 10:14:33.513' union all
select 2,N'B','2008-08-02 10:14:33.513' union all
select 1,N'A','2008-08-02 10:14:33.530' union all
select 4,N'C','2008-08-02 10:14:33.530' union all
select 3,N'C','2008-08-02 10:14:33.530' union all
select 9,N'C','2008-08-02 10:14:33.530'select *
from @test a
where id=(select top 1 id from @test where sname=a.sname)/*
6 A 2008-08-02 10:14:33.513
2 B 2008-08-02 10:14:33.513
4 C 2008-08-02 10:14:33.530
*/
3楼的要把后面的 order by dat去掉,不然查到的ID是6 2 3
drop table #2
go
declare @test table([id] int,[sname] nvarchar(1),[dat] Datetime)
Insert @test
select 6,N'A','2008-08-02 10:14:33.513' union all
select 2,N'B','2008-08-02 10:14:33.513' union all
select 1,N'A','2008-08-02 10:14:33.530' union all
select 4,N'C','2008-08-02 10:14:33.530' union all
select 3,N'C','2008-08-02 10:14:33.530' union all
select 9,N'C','2008-08-02 10:14:33.530'
--2000
Select px=identity(int,1,1),* into # from @test
SELECT px = (select count(1) from # where px <= t.px and [sname] = t.[sname]),[id],[sname],[dat] into #2 from # tselect ID,[sname],[dat] from #2 t where t.px =1
select ID,[sname],[dat] from #2 t where t.px =2
select ID,[sname],[dat] from #2 t where t.px =3--2005
select ID,[sname],[dat] from
(select px =row_number() over (partition by [sname] order by [sname],[dat]),* from @test ) a
where px =1select ID,[sname],[dat] from
(select px =row_number() over (partition by [sname] order by [sname],[dat]),* from @test ) a
where px =2select ID,[sname],[dat] from
(select px =row_number() over (partition by [sname] order by [sname],[dat]),* from @test ) a
where px =3
ID sname dat
----------- ----- -----------------------
6 A 2008-08-02 10:14:33.513
2 B 2008-08-02 10:14:33.513
4 C 2008-08-02 10:14:33.530
ID sname dat
----------- ----- -----------------------
1 A 2008-08-02 10:14:33.530
3 C 2008-08-02 10:14:33.530
ID sname dat
----------- ----- -----------------------
9 C 2008-08-02 10:14:33.530*/