查询连续出现的一句sql (MSSQL)

转载于 2011-02-26 20:40:00 发布 · 86 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/singin/archive/2011/02/26/1965929.html

本文介绍了一种使用SQL查询人员旷工记录的方法，通过创建人员表并存储人员信息及旷工情况，利用复杂的SQL语句来筛选出连续旷工四天及以上的人员名单。

--表人员表，人员旷工记录

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

转载于:https://www.cnblogs.com/singin/archive/2011/02/26/1965929.html