我用到两个表,一个是student,一个是attendence
student表的数据如下:
studentId studentName
1 李三
2 王五
3 刘六
4 黄七attendence表的数据如下:
studentId checkInTime
1 2013-5-26
1 2013-5-27
2 2013-5-26
1 2013-5-28
上表attendence里只有1号学生出勤过三次,其余三个学生近三天都不足三次,所以查询的结果应为
studentId studentName
2 王五
3 刘六
4 黄七
这个查询语句怎么写?多谢!
student表的数据如下:
studentId studentName
1 李三
2 王五
3 刘六
4 黄七attendence表的数据如下:
studentId checkInTime
1 2013-5-26
1 2013-5-27
2 2013-5-26
1 2013-5-28
上表attendence里只有1号学生出勤过三次,其余三个学生近三天都不足三次,所以查询的结果应为
studentId studentName
2 王五
3 刘六
4 黄七
这个查询语句怎么写?多谢!
(SELECT STUDENTID FROM ARRENDENCE WHERE DATEDIFF(DD,CHECKINTIME,GETDATE())<4 GROUP BY STUDENTID HAVING COUNT(*)<3) AS B ON A.STUDENTID=B.STUDENTID
if object_id('student') is not null
drop table student
go
create table student
(
id int,
name nvarchar(20)
)
go
if object_id('attendence') is not null
drop table attendence
go
create table attendence
(
id int,
date datetime
)
go
insert into student
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六'
go
insert into attendence
select 1,'2013-5-26' union all
select 1,'2013-5-27' union all
select 2,'2013-5-26' union all
select 1,'2013-5-28'
go
select * from student
where id not in (select id from attendence group by id having count(*)>=3)
from student a
where not exists
(select count(1)
from attendence b
where a.id = b.id
group by b.id
having count(1)>=3)