表ATTENDANCE NAME DATE 在ACCESS上测试OKSELECT NAME FROM (SELECT DISTINCT NAME, DAY(DATE) FROM ATTENDANCE WHERE DAY(DATE) BETWEEN 7 AND 14) GROUP BY NAME HAVING COUNT(NAME) = 7;用子查询用来保证每天每人只有一次出席记录,然后分组,用HAVING组过滤的功能得到7天全勤的人
select distinct UserName from table1 a where a.DateTime > convert(datetime,'2004-4-01',20) and a.DateTime < convert(datetime,'2004-4-30',20) and (select count(CheckTag) from table1 b where CheckTag = '1' and a.UserName = b.UserName and (b.DateTime>=a.DateTime and b.DateTime <= a.DateTime+6))=7
NAME
DATE
在ACCESS上测试OKSELECT NAME
FROM (SELECT DISTINCT NAME, DAY(DATE) FROM ATTENDANCE WHERE DAY(DATE) BETWEEN 7 AND 14)
GROUP BY NAME
HAVING COUNT(NAME) = 7;用子查询用来保证每天每人只有一次出席记录,然后分组,用HAVING组过滤的功能得到7天全勤的人
where a.DateTime > convert(datetime,'2004-4-01',20) and a.DateTime < convert(datetime,'2004-4-30',20) and
(select count(CheckTag) from table1 b
where CheckTag = '1' and a.UserName = b.UserName
and (b.DateTime>=a.DateTime and b.DateTime <= a.DateTime+6))=7