select a0188,rq,ti,COUNT(*) from ( select distinct a0188 ,cast(left(checktime,10) as datetime) rq,cast(dbo.tim(checktime) as datetime) ti,SENSORID jh,null b,'system' c,null d from zx_hr..a01 a01,zk..CHECKINOUT ,zk..userinfo where a01.a0190=zk..userinfo.BADGENUMBER and zk..checkinout.userid=zk..userinfo.userid and cast(left(checktime,10) as datetime)>=dateadd(day,-15,cast(convert(varchar(10),getdate(),126) as datetime)) and not exists(select 1 from zx_hr..k07 k07 where k07.a0188=a01.a0188 and k07.k0700=cast(left(checktime,10) as datetime ) and k07.k0701=cast(dbo.tim(checktime) as datetime) ) ) a GROUP BY a0188,rq,ti HAVING COUNT(*)>1 看哪个主键有重复,然后按这个主键去找明细。 就是主键下 SENSORID 有不同吧。
SELECT DISTINCT 是按整行记录去重复,不是仅对单个 a0188 字段去重复。
如果 a0188 相同但是其它字段有不同,就会有多条结果,插入 k07 表当然主键重复了。
a0188,k0700,k0701这三个为主键,但是这三个都没有NULL值的
from
(
select distinct a0188 ,cast(left(checktime,10) as datetime) rq,cast(dbo.tim(checktime) as datetime) ti,SENSORID jh,null b,'system' c,null d
from zx_hr..a01 a01,zk..CHECKINOUT ,zk..userinfo
where a01.a0190=zk..userinfo.BADGENUMBER and zk..checkinout.userid=zk..userinfo.userid
and cast(left(checktime,10) as datetime)>=dateadd(day,-15,cast(convert(varchar(10),getdate(),126) as datetime))
and not exists(select 1 from zx_hr..k07 k07 where k07.a0188=a01.a0188 and k07.k0700=cast(left(checktime,10) as datetime )
and k07.k0701=cast(dbo.tim(checktime) as datetime) )
) a
GROUP BY a0188,rq,ti
HAVING COUNT(*)>1
看哪个主键有重复,然后按这个主键去找明细。
就是主键下 SENSORID 有不同吧。