列位大侠,小弟遇到一难题,百思不得其解,望指点!如题:
查询出连续两天缺勤的学生!(必须是连续两天)以下是数据表和测试数据
0表示缺勤 1表示出勤
create table roll_call
(
[date] datetime not null,
StuName char(20) not null,
flag int not null
)
insert into roll_call select '2009-8-1','chenwei',1
insert into roll_call select '2009-8-2','chenwei',0
insert into roll_call select '2009-8-3','chenwei',0
insert into roll_call select '2009-8-4','chenwei',1insert into roll_call select '2009-8-1','qiyekun',0
insert into roll_call select '2009-8-2','qiyekun',1
insert into roll_call select '2009-8-3','qiyekun',1
insert into roll_call select '2009-8-4','qiyekun',0insert into roll_call select '2009-8-1','liufeifan',0
insert into roll_call select '2009-8-2','liufeifan',0
insert into roll_call select '2009-8-3','liufeifan',0
insert into roll_call select '2009-8-4','liufeifan',1insert into roll_call select '2009-8-1','hujintao',0
insert into roll_call select '2009-8-2','hujintao',0
insert into roll_call select '2009-8-3','hujintao',1
insert into roll_call select '2009-8-4','hujintao',1insert into roll_call select '2009-8-1','wenjiabao',1
insert into roll_call select '2009-8-2','wenjiabao',0
insert into roll_call select '2009-8-3','wenjiabao',1
insert into roll_call select '2009-8-4','wenjiabao',1insert into roll_call select '2009-8-1','maozhuxi',1
insert into roll_call select '2009-8-2','maozhuxi',0
insert into roll_call select '2009-8-3','maozhuxi',0
insert into roll_call select '2009-8-4','maozhuxi',1
insert into roll_call select '2009-8-1','zhouenlai',1
insert into roll_call select '2009-8-2','zhouenlai',1
insert into roll_call select '2009-8-3','zhouenlai',1
insert into roll_call select '2009-8-4','zhouenlai',1
insert into roll_call select '2009-8-1','zhurongji',1
insert into roll_call select '2009-8-2','zhurongji',0
insert into roll_call select '2009-8-3','zhurongji',0
insert into roll_call select '2009-8-4','zhurongji',0
测试结果 连续两天缺勤的有chenwei liufeifan hujintao maozhuxi zhurongji
需要说明的是 比如说5月31日和6月1日也是连续两天谁能够帮助小弟完成这个sql查询任务,真诚地感谢!
查询出连续两天缺勤的学生!(必须是连续两天)以下是数据表和测试数据
0表示缺勤 1表示出勤
create table roll_call
(
[date] datetime not null,
StuName char(20) not null,
flag int not null
)
insert into roll_call select '2009-8-1','chenwei',1
insert into roll_call select '2009-8-2','chenwei',0
insert into roll_call select '2009-8-3','chenwei',0
insert into roll_call select '2009-8-4','chenwei',1insert into roll_call select '2009-8-1','qiyekun',0
insert into roll_call select '2009-8-2','qiyekun',1
insert into roll_call select '2009-8-3','qiyekun',1
insert into roll_call select '2009-8-4','qiyekun',0insert into roll_call select '2009-8-1','liufeifan',0
insert into roll_call select '2009-8-2','liufeifan',0
insert into roll_call select '2009-8-3','liufeifan',0
insert into roll_call select '2009-8-4','liufeifan',1insert into roll_call select '2009-8-1','hujintao',0
insert into roll_call select '2009-8-2','hujintao',0
insert into roll_call select '2009-8-3','hujintao',1
insert into roll_call select '2009-8-4','hujintao',1insert into roll_call select '2009-8-1','wenjiabao',1
insert into roll_call select '2009-8-2','wenjiabao',0
insert into roll_call select '2009-8-3','wenjiabao',1
insert into roll_call select '2009-8-4','wenjiabao',1insert into roll_call select '2009-8-1','maozhuxi',1
insert into roll_call select '2009-8-2','maozhuxi',0
insert into roll_call select '2009-8-3','maozhuxi',0
insert into roll_call select '2009-8-4','maozhuxi',1
insert into roll_call select '2009-8-1','zhouenlai',1
insert into roll_call select '2009-8-2','zhouenlai',1
insert into roll_call select '2009-8-3','zhouenlai',1
insert into roll_call select '2009-8-4','zhouenlai',1
insert into roll_call select '2009-8-1','zhurongji',1
insert into roll_call select '2009-8-2','zhurongji',0
insert into roll_call select '2009-8-3','zhurongji',0
insert into roll_call select '2009-8-4','zhurongji',0
测试结果 连续两天缺勤的有chenwei liufeifan hujintao maozhuxi zhurongji
需要说明的是 比如说5月31日和6月1日也是连续两天谁能够帮助小弟完成这个sql查询任务,真诚地感谢!
解决方案 »
- 讨厌的微软错误提示“将截断字符串或二进制数据”
- 请教一个SQL语句
- 老板给了个怪需求,求个思路
- Sqlserver用户问题,急!
- 数据库有1千道数学题,我重中随机抽取80道题
- 谁用过那种提供SQL2000的虚拟主机?谈谈最经常遇到的问题可以不?
- 对一编码进行修改,如果没有与该编码想关联的数据( 数据库中相关连的数据表有十几个),则可以修该否则不能 ,怎么判断效率最高???
- 2003上安装sqlserver2000的问题
- 关于存储过程调用性能
- 单表的存储过程 可以 调用视图吗/望高手指点
- 关于SQL05数据库连接不上本地服务器
- 如何用SQL语句查看用户的角色?(sql server 2000数据库)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-05 23:13:42
---------------------------------
--> 生成测试数据表:tbIf not object_id('roll_call') is null
Drop table roll_call
Go
create table roll_call
(
[date] datetime not null,
StuName char(20) not null,
flag int not null
)
insert into roll_call select '2009-8-1','chenwei',1
insert into roll_call select '2009-8-2','chenwei',0
insert into roll_call select '2009-8-3','chenwei',0
insert into roll_call select '2009-8-4','chenwei',1insert into roll_call select '2009-8-1','qiyekun',0
insert into roll_call select '2009-8-2','qiyekun',1
insert into roll_call select '2009-8-3','qiyekun',1
insert into roll_call select '2009-8-4','qiyekun',0insert into roll_call select '2009-8-1','liufeifan',0
insert into roll_call select '2009-8-2','liufeifan',0
insert into roll_call select '2009-8-3','liufeifan',0
insert into roll_call select '2009-8-4','liufeifan',1insert into roll_call select '2009-8-1','hujintao',0
insert into roll_call select '2009-8-2','hujintao',0
insert into roll_call select '2009-8-3','hujintao',1
insert into roll_call select '2009-8-4','hujintao',1insert into roll_call select '2009-8-1','wenjiabao',1
insert into roll_call select '2009-8-2','wenjiabao',0
insert into roll_call select '2009-8-3','wenjiabao',1
insert into roll_call select '2009-8-4','wenjiabao',1insert into roll_call select '2009-8-1','maozhuxi',1
insert into roll_call select '2009-8-2','maozhuxi',0
insert into roll_call select '2009-8-3','maozhuxi',0
insert into roll_call select '2009-8-4','maozhuxi',1
insert into roll_call select '2009-8-1','zhouenlai',1
insert into roll_call select '2009-8-2','zhouenlai',1
insert into roll_call select '2009-8-3','zhouenlai',1
insert into roll_call select '2009-8-4','zhouenlai',1
insert into roll_call select '2009-8-1','zhurongji',1
insert into roll_call select '2009-8-2','zhurongji',0
insert into roll_call select '2009-8-3','zhurongji',0
insert into roll_call select '2009-8-4','zhurongji',0select distinct stuname
from roll_call t
where exists(
select 1
from roll_call
where stuname=t.stuname
and (date=t.date+1 or date=t.date-1)
and flag=0)
and flag=0
/*
stuname
--------------------
chenwei
hujintao
liufeifan
maozhuxi
zhurongji (5 行受影响)
*/
(
[date] datetime not null,
StuName char(20) not null,
flag int not null
)
insert into roll_call select '2009-8-1','chenwei',1
insert into roll_call select '2009-8-2','chenwei',0
insert into roll_call select '2009-8-3','chenwei',0
insert into roll_call select '2009-8-4','chenwei',1insert into roll_call select '2009-8-1','qiyekun',0
insert into roll_call select '2009-8-2','qiyekun',1
insert into roll_call select '2009-8-3','qiyekun',1
insert into roll_call select '2009-8-4','qiyekun',0insert into roll_call select '2009-8-1','liufeifan',0
insert into roll_call select '2009-8-2','liufeifan',0
insert into roll_call select '2009-8-3','liufeifan',0
insert into roll_call select '2009-8-4','liufeifan',1insert into roll_call select '2009-8-1','hujintao',0
insert into roll_call select '2009-8-2','hujintao',0
insert into roll_call select '2009-8-3','hujintao',1
insert into roll_call select '2009-8-4','hujintao',1insert into roll_call select '2009-8-1','wenjiabao',1
insert into roll_call select '2009-8-2','wenjiabao',0
insert into roll_call select '2009-8-3','wenjiabao',1
insert into roll_call select '2009-8-4','wenjiabao',1insert into roll_call select '2009-8-1','maozhuxi',1
insert into roll_call select '2009-8-2','maozhuxi',0
insert into roll_call select '2009-8-3','maozhuxi',0
insert into roll_call select '2009-8-4','maozhuxi',1
insert into roll_call select '2009-8-1','zhouenlai',1
insert into roll_call select '2009-8-2','zhouenlai',1
insert into roll_call select '2009-8-3','zhouenlai',1
insert into roll_call select '2009-8-4','zhouenlai',1
insert into roll_call select '2009-8-1','zhurongji',1
insert into roll_call select '2009-8-2','zhurongji',0
insert into roll_call select '2009-8-3','zhurongji',0
insert into roll_call select '2009-8-4','zhurongji',0
;With China as
(
select * ,row_number () over(partition by stuname order by date) px from roll_call
),china1 as
(
select a.date adate,a.flag aflag,a.StuName astuname,a.px apx,b.px bpx,
b.date bdate,b.flag bflag,b.StuName bstuname
from China a join China b on a.StuName=b.StuName and a.px=b.px-1
)
select min(adate) date,astuname from china1 where aflag=0 and bflag=0
group by astunamedate astuname
----------------------- --------------------
2009-08-02 00:00:00.000 chenwei
2009-08-01 00:00:00.000 hujintao
2009-08-01 00:00:00.000 liufeifan
2009-08-02 00:00:00.000 maozhuxi
2009-08-02 00:00:00.000 zhurongji (5 行受影响)
select stuname 姓名,min([date]) 第一次缺勤日期,max([date]) 最后一次缺勤日期
from roll_call t
where exists(
select 1
from roll_call
where stuname=t.stuname
and (date=t.date+1 or date=t.date-1)
and flag=0)
and flag=0
group by stuname
/*
姓名 第一次缺勤日期 最后一次缺勤日期
chenwei 2009-08-02 00:00:00.000 2009-08-03 00:00:00.000
hujintao 2009-08-01 00:00:00.000 2009-08-02 00:00:00.000
liufeifan 2009-08-01 00:00:00.000 2009-08-03 00:00:00.000
maozhuxi 2009-08-02 00:00:00.000 2009-08-03 00:00:00.000
zhurongji 2009-08-02 00:00:00.000 2009-08-04 00:00:00.000
*/
from roll_call t
where exists(
select 1
from roll_call
where stuname=t.stuname
and (date=t.date+1 or date=t.date-1)
and flag=0)
and flag=0
group by stuname
date=t.date+1 or date=t.date-1 条件取一个就可以了!
select distinct stuname
from roll_call a
where flag=0 and (exists (select 1 from roll_call where stuname=a.stuname and flag=0 and (date=a.date-1 or date=a.date+1)))