数据库里有个表叫A,表里有 id,name,timeid name time
1 aa 2010-1-1
2 ab 2010-1-1
5 c 2010-1-3
6 d 2010-1-4
7 aea 2010-1-4
8 axa 2010-1-4
9 aesa 2010-1-5我想把所有time一样中最小ID的记录都列出来,想要的结果是这样的
1 aa 2010-1-1
5 c 2010-1-3
6 d 2010-1-4
9 aesa 2010-1-5
1 aa 2010-1-1
2 ab 2010-1-1
5 c 2010-1-3
6 d 2010-1-4
7 aea 2010-1-4
8 axa 2010-1-4
9 aesa 2010-1-5我想把所有time一样中最小ID的记录都列出来,想要的结果是这样的
1 aa 2010-1-1
5 c 2010-1-3
6 d 2010-1-4
9 aesa 2010-1-5
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,name varchar(4),time datetime)
insert into #tb
select 1,'aa','2010-1-1' union all
select 2,'ab','2010-1-1' union all
select 5,'c','2010-1-3' union all
select 6,'d','2010-1-4' union all
select 7,'aea','2010-1-4' union all
select 8,'axa','2010-1-4' union all
select 9,'aesa','2010-1-5'select * from #tb t
where not exists(select * from #tb where time=t.time and id<t.id)
id name time
----------- ---- -----------------------
1 aa 2010-01-01 00:00:00.000
5 c 2010-01-03 00:00:00.000
6 d 2010-01-04 00:00:00.000
9 aesa 2010-01-05 00:00:00.000(4 行受影响)
-->Microsoft SQL Server Management Studio Complete 2008***
-->AUTHOR : Mr wang **********
-->CREATE TIME : 2010-11-18 16:21:06 **************
-->*******************************************************
--> 测试数据:[TB]
if object_id('[TB]') is not null
drop table [TB]---->建表
create table [TB]([id] int,[name] varchar(4),[time] datetime)
insert [TB]
select 1,'aa','2010-1-1' union all
select 2,'ab','2010-1-1' union all
select 5,'c','2010-1-3' union all
select 6,'d','2010-1-4' union all
select 7,'aea','2010-1-4' union all
select 8,'axa','2010-1-4' union all
select 9,'aesa','2010-1-5'--> 查询结果
SELECT * FROM [TB] t where
not exists(select 1 from TB where [time] = t.[time] and id<t.id)--> 删除表格
DROP TABLE [TB]
--自己挑吧
--1
select t.* from A t
where not exists
(select 1 from A where time = t.time and id < t.id)--2
select * from A t
where id=(select min(id) from A where time = t.time)--3
select id,name,time
from
(select *,rn=row_number()over(partition by time order by id) from A)t
where rn=1--4
select t1.id,t2.name,t1.time
from
(select min(id) as id,time from A) t1,A t2
where t1.id = t2.id