有表xxb,字段身份证号sfzh,操作日期czrq,因为表里面有相同的身份证号但操作日期不同,现要查询出表里面所有的身份证号,但如果有两个或以上相同的身份证号就显示最早的操作时间的。
例如
1,2008-1-1
2,2008-1-1
2,2008-2-1查询显示
1,2008-1-1
2,2008-1-1
例如
1,2008-1-1
2,2008-1-1
2,2008-2-1查询显示
1,2008-1-1
2,2008-1-1
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[sfzh]varchar(20),[czrq] datetime)
Insert #T
select 1,1,'2008-1-1' union all
select 2,2,'2008-1-1' union all
select 3,2,'2008-2-1'
Go
select * from #T a where not exists(select 1 from #T where sfzh=a.sfzh and czrq<a.czrq)
create table #F
(
sfzh nvarchar(20),
czrq datetime
)
insert into #F
select '1','2008-1-1' union all
select '2','2008-1-1' union all
select '2','2008-2-1'
select sfzh,min(czrq) from #F group by sfzh
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([sfzh]varchar(20),[czrq] datetime)
Insert #T
select 1,'2008-1-1' union all
select 2,'2008-1-1' union all
select 2,'2008-2-1'
Go
--select * from #T a where not exists(select 1 from #T where sfzh=a.sfzh and czrq<a.czrq)
select * from #T a where czrq =(select min(czrq) from #T where sfzh=a.sfzh )
直接按身份证分组,然后取组内最小就可以了。
select sfzh,min(czrg) as czrg
from xxb
group by sfzh
-- that's ok....
Create table #T([sfzh]varchar(20),[czrq] datetime)
Insert #T
select 1,'2008-1-1' union all
select 2,'2008-1-1' union all
select 2,'2008-2-1'
Goselect * from #T TT where not exists(select * from #T where TT.[sfzh]=[sfzh] and TT.[czrq]>[czrq])
Create table #T([sfzh]varchar(20),[czrq] datetime)
Insert #T
select 1,'2008-1-1' union all
select 2,'2008-1-1' union all
select 2,'2008-2-1'
Goselect * from #T TT where not exists(select * from #T where TT.[sfzh]=[sfzh] and TT.[czrq]>[czrq])