select * from xxb a where exists(selec 1 from xxb where sfzh = a.xfzh and abs(datediff(y,czsj,a.czsj)) >2)
select * from xxb a where exists(selec 1 from xxb where sfzh = a.xfzh and abs(datediff(y,czsj,a.czsj)) <=2)
select xm from (select xm, dt1=min(czsj), dt2=max(czsj) from xxb group by xm) T where dateadd(y,2,dt1)>=dt2
--try 1 select xm from (select xm, dt1=min(czsj), dt2=max(czsj) from xxb group by xm) T where dateadd(yy,2,dt1)>=dt2 --try 2 select xm from xxb a where exists(select 1 from xxb where xm=a.xm and czsj between a.czsj and dateadd(yy,2,a.czsj))
select m.* from ( select * , px = (select count(1) from tb where xm = t.xm and czsj < t.czsj) + 1 from tb t ) m , ( select * , px = (select count(1) from tb where xm = t.xm and czsj < t.czsj) + 1 from tb t ) n where m.xm = n.xm and m.px = n.px - 1 and datediff(year,m.czsj,n.czsj) <= 2
convert(datetime,left('20070301100000',8))
--轉換成時間型的 DECLARE @S VARCHAR(20) SET @S=20070301100000 SELECT CAST(STUFF(STUFF(STUFF(STUFF(STUFF(@S,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':') AS DATETIME) /* ------------------------------------------------------ 2007-03-01 10:00:00.000*/
select xm from xxb a where exists( select 1 from xxb where sfzh=a.sfzh and convert(datetime,left(czsj,8))>dateadd(yy,2,convert(datetime,left(a.czsj,8))) )
两年,把日期取出来就行了,用不着去计算时,分,秒. 改一下,大于等于: select xm from xxb a where exists( select 1 from xxb where sfzh=a.sfzh and convert(datetime,left(czsj,8))>=dateadd(yy,2,convert(datetime,left(a.czsj,8))) )
--try 2 select xm from xxb a where lb='e' and exists(select 1 from xxb where xm=a.xm and lb<>'e' and convert(datetime,left(czsj,8)) between convert(datetime,left(a.czsj,8)) and dateadd(yy,2,convert(datetime,left(a.czsj,8))))
select xm from xxb a where lb='e' and not exists(select 1 from xxb where xm=a.xm and abs(datediff(day,convert(datetime,left(czsj,8)),convert(datetime,left(a.czsj,8))))>730 ) 这样就符合要求了,但还想加多一个要求,就是还有一个字段期数qs,如果某人报名的期数为111,那么查询结果就不显示这个人, 即如果一开始报名的类别为a期数为111,后来报名的类别为e但期数为其它例如222,查询结果也不显示
select xm from xxb a where lb='e' and exists(select 1 from xxb where xm=a.xm and lb<>'e' and convert(datetime,left(czsj,8)) between convert(datetime,left(a.czsj,8)) and dateadd(yy,2,convert(datetime,left(a.czsj,8))))
select xm ,sfzh from 人员表 group by xm ,sfzh having datediff(yy,min(czsj),max(czsj))>1
from xxb a
where exists(selec 1 from xxb where sfzh = a.xfzh and abs(datediff(y,czsj,a.czsj)) >2)
from xxb a
where exists(selec 1 from xxb where sfzh = a.xfzh and abs(datediff(y,czsj,a.czsj)) <=2)
(select xm, dt1=min(czsj), dt2=max(czsj) from xxb group by xm) T
where dateadd(y,2,dt1)>=dt2
select xm from
(select xm, dt1=min(czsj), dt2=max(czsj) from xxb group by xm) T
where dateadd(yy,2,dt1)>=dt2
--try 2
select xm
from xxb a
where exists(select 1 from xxb where xm=a.xm and czsj between a.czsj and dateadd(yy,2,a.czsj))
---->
dateadd(yy,2,dt1)>=dt2
select m.* from
(
select * , px = (select count(1) from tb where xm = t.xm and czsj < t.czsj) + 1 from tb t
) m ,
(
select * , px = (select count(1) from tb where xm = t.xm and czsj < t.czsj) + 1 from tb t
) n
where m.xm = n.xm and m.px = n.px - 1 and datediff(year,m.czsj,n.czsj) <= 2
DECLARE @S VARCHAR(20)
SET @S=20070301100000
SELECT CAST(STUFF(STUFF(STUFF(STUFF(STUFF(@S,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':') AS DATETIME)
/*
------------------------------------------------------
2007-03-01 10:00:00.000*/
select 1 from xxb where sfzh=a.sfzh and convert(datetime,left(czsj,8))>dateadd(yy,2,convert(datetime,left(a.czsj,8)))
)
改一下,大于等于:
select xm from xxb a where exists(
select 1 from xxb where sfzh=a.sfzh and convert(datetime,left(czsj,8))>=dateadd(yy,2,convert(datetime,left(a.czsj,8)))
)
有一个人员信息表xxb,
有姓名xm,身份证号sfzh,类别lb,
操作时间czsj(操作时间是字符型的,它这样记录的例如20070301100000表示2007年3月1日早上10时)其中类别有a,b,c,d,e五种,要有a,b,c,d其中的一种类别才能输入类别e
现要查询2007年的类别e但它办a或b或c或d类别没有两年的数据
现要查询2007年办了的类别e但它办(类别a或类别b时间没有两年)的数据
--try 2
select xm
from xxb a
where lb='e'
and exists(select 1 from xxb where xm=a.xm and lb<>'e'
and convert(datetime,left(czsj,8)) between convert(datetime,left(a.czsj,8))
and dateadd(yy,2,convert(datetime,left(a.czsj,8))))
from xxb a
where lb='e'
and not exists(select 1 from xxb where xm=a.xm
and abs(datediff(day,convert(datetime,left(czsj,8)),convert(datetime,left(a.czsj,8))))>730 )
这样就符合要求了,但还想加多一个要求,就是还有一个字段期数qs,如果某人报名的期数为111,那么查询结果就不显示这个人,
即如果一开始报名的类别为a期数为111,后来报名的类别为e但期数为其它例如222,查询结果也不显示
from xxb a
where lb='e'
and exists(select 1 from xxb where xm=a.xm and lb<>'e'
and convert(datetime,left(czsj,8)) between convert(datetime,left(a.czsj,8))
and dateadd(yy,2,convert(datetime,left(a.czsj,8))))
xm
,sfzh
from
人员表
group by
xm
,sfzh
having
datediff(yy,min(czsj),max(czsj))>1