查询连续出现的一句sql (MSSQL)
--表 人员表,人员旷工记录
CREATE TABLE [dbo].[人员表](
[id] [int] IDENTITY(1,1) NOT NULL,
[ry] [nchar](10) NULL, --人员
[rq] [datetime] NULL, --时间
[jf] [decimal](18, 0) NULL, --扣分,关键查该字段,有多少个连续出现的0
[reason] [nchar](10) NULL
)
--查询,关键增加两次自增目录
select ry,rq,jf,reason,rn,rm,(rn-rm) as rh from
(select * ,row_number() over (order by rn) as rm
from ( select *,row_number() over (order by rq) as rn from 人员表) a
where jf = 0
) b
--结果
select ry,count(rh)
from (
--select * from 人员表
select ry,rq,jf,reason,rn,rm,(rn-rm) as rh from
(select * ,row_number() over (order by rn) as rm
from ( select *,row_number() over (order by rq) as rn from 人员表) a
where jf = 0
) b
)c
group by ry,rh having count(rh)>=4