select a.* from tb a inner join (select id,time=min(time) from tb group by id)b on a.id=b.id and a.time=b.time
create table tb(id int,name varchar(20),[time] datetime) Insert into tb select '1','武穴市','2004-01-27 00:00:00' union all select '2','鄂州市','2004-01-31 00:00:00' union all select '3','宜昌市伍家崗區','2004-02-01 00:00:00' union all select '4','襄樊市襄樊區','2004-02-03 00:00:00' union all select '3','宜昌市伍家崗區','2004-02-08 00:00:00' union all select '5','麻城市','2004-02-09 00:00:00:00' union all select '6','洪湖市','2004-02-11 00:00:00:00' union all select '7','公安縣','2004-02-11 00:00:00:00' union all select '8','松滋市','2004-02-11 00:00:00:00' union all select '9','陽新縣','2004-02-11 00:00:00:00' union all select '10','黃岡市','2004-02-15 00:00:00:00'select * from tbselect a.* from tb a inner join (select id,time=min(time) from tb group by id)b on a.id=b.id and a.time=b.time order by a.id--結果 id name time --------------------------------------------------- 1 武穴市 2004-01-27 00:00:00.000 2 鄂州市 2004-01-31 00:00:00.000 3 宜昌市伍家崗區 2004-02-01 00:00:00.000 4 襄樊市襄樊區 2004-02-03 00:00:00.000 5 麻城市 2004-02-09 00:00:00.000 6 洪湖市 2004-02-11 00:00:00.000 7 公安縣 2004-02-11 00:00:00.000 8 松滋市 2004-02-11 00:00:00.000 9 陽新縣 2004-02-11 00:00:00.000 10 黃岡市 2004-02-15 00:00:00.000
select id ,max(name),min(time)form tabel group by id
select id,name,min(time) time from table1 group by id,name
declare @tb1 table([id] int,[name] varchar(20),[time] datetime) insert into @tb1 select 1, '武穴市', '2004-01-27 00:00:00' union all select 2, '鄂州市', '2004-01-31 00:00:00' union all select 3, '宜昌市伍家岗区', '2004-02-01 00:00:00' union all select 4, '襄樊市襄樊区', '2004-02-03 00:00:00' union all select 3, '宜昌市伍家岗区', '2004-02-08 00:00:00' union all select 5, '麻城市', '2004-02-09 00:00:00' union all select 6, '洪湖市', '2004-02-11 00:00:00' union all select 7, '公安县', '2004-02-11 00:00:00' union all select 8, '松滋市', '2004-02-11 00:00:00' union all select 9, '阳新县', '2004-02-11 00:00:00' union all select 10, '黄冈市', '2004-02-15 00:00:00'select a.* from @tb1 a right join (select [id],[time]=max([time]) from @tb1 group by [id])b on a.[id]=b.[id] and a.[time]=b.[time]/*(所影响的行数为 11 行)id name time ----------- -------------------- ------------------------------------------------------ 1 武穴市 2004-01-27 00:00:00.000 2 鄂州市 2004-01-31 00:00:00.000 3 宜昌市伍家岗区 2004-02-08 00:00:00.000 4 襄樊市襄樊区 2004-02-03 00:00:00.000 5 麻城市 2004-02-09 00:00:00.000 6 洪湖市 2004-02-11 00:00:00.000 7 公安县 2004-02-11 00:00:00.000 8 松滋市 2004-02-11 00:00:00.000 9 阳新县 2004-02-11 00:00:00.000 10 黄冈市 2004-02-15 00:00:00.000(所影响的行数为 10 行) */
inner join (select id,time=min(time) from tb group by id)b
on a.id=b.id and a.time=b.time
Insert into tb
select '1','武穴市','2004-01-27 00:00:00'
union all select '2','鄂州市','2004-01-31 00:00:00'
union all select '3','宜昌市伍家崗區','2004-02-01 00:00:00'
union all select '4','襄樊市襄樊區','2004-02-03 00:00:00'
union all select '3','宜昌市伍家崗區','2004-02-08 00:00:00'
union all select '5','麻城市','2004-02-09 00:00:00:00'
union all select '6','洪湖市','2004-02-11 00:00:00:00'
union all select '7','公安縣','2004-02-11 00:00:00:00'
union all select '8','松滋市','2004-02-11 00:00:00:00'
union all select '9','陽新縣','2004-02-11 00:00:00:00'
union all select '10','黃岡市','2004-02-15 00:00:00:00'select * from tbselect a.* from tb a
inner join (select id,time=min(time) from tb group by id)b
on a.id=b.id and a.time=b.time
order by a.id--結果
id name time
---------------------------------------------------
1 武穴市 2004-01-27 00:00:00.000
2 鄂州市 2004-01-31 00:00:00.000
3 宜昌市伍家崗區 2004-02-01 00:00:00.000
4 襄樊市襄樊區 2004-02-03 00:00:00.000
5 麻城市 2004-02-09 00:00:00.000
6 洪湖市 2004-02-11 00:00:00.000
7 公安縣 2004-02-11 00:00:00.000
8 松滋市 2004-02-11 00:00:00.000
9 陽新縣 2004-02-11 00:00:00.000
10 黃岡市 2004-02-15 00:00:00.000
insert into @tb1
select 1, '武穴市', '2004-01-27 00:00:00' union all
select 2, '鄂州市', '2004-01-31 00:00:00' union all
select 3, '宜昌市伍家岗区', '2004-02-01 00:00:00' union all
select 4, '襄樊市襄樊区', '2004-02-03 00:00:00' union all
select 3, '宜昌市伍家岗区', '2004-02-08 00:00:00' union all
select 5, '麻城市', '2004-02-09 00:00:00' union all
select 6, '洪湖市', '2004-02-11 00:00:00' union all
select 7, '公安县', '2004-02-11 00:00:00' union all
select 8, '松滋市', '2004-02-11 00:00:00' union all
select 9, '阳新县', '2004-02-11 00:00:00' union all
select 10, '黄冈市', '2004-02-15 00:00:00'select a.* from @tb1 a right join (select [id],[time]=max([time]) from @tb1 group by [id])b on a.[id]=b.[id] and a.[time]=b.[time]/*(所影响的行数为 11 行)id name time
----------- -------------------- ------------------------------------------------------
1 武穴市 2004-01-27 00:00:00.000
2 鄂州市 2004-01-31 00:00:00.000
3 宜昌市伍家岗区 2004-02-08 00:00:00.000
4 襄樊市襄樊区 2004-02-03 00:00:00.000
5 麻城市 2004-02-09 00:00:00.000
6 洪湖市 2004-02-11 00:00:00.000
7 公安县 2004-02-11 00:00:00.000
8 松滋市 2004-02-11 00:00:00.000
9 阳新县 2004-02-11 00:00:00.000
10 黄冈市 2004-02-15 00:00:00.000(所影响的行数为 10 行)
*/