关于一个考勤问题的查询。
有一个表。ATD
ATD_SN ATD_DATE
100011 2013-06-01 07:58 本条重复。在上午取最小值也就是取下面的那条记录
201281 2013-06-01 07:55
100012 2013-06-01 08:01
100011 2013-06-01 07:01
100011 2013-06-01 06：00本条重复。在下午下班取最大值也就是取下面的那条记录
100012 2013-06-01 08：59
100011 2013-06-01 08：59
100011 2013-06-02 07:33
100012 2013-06-02 07:58
并且联合查询 EMPLOYEE
SN NAME
100011 王小艳
201281 李小明
100012 李思思
100013 王涛
得出结果为（只要一天有记录就为1）
王小艳 2
李思思 2
[解决办法]
SELECT NAME ,COUNT(ATD_DATE ) FROM EMPLOYEE A JOIN
(select ATD_SN,MIN(ATD_DATE)ATD_DATE FROM ATD GROUP BY ATD_SN,convert(char(11),atd_Date,120))B ON A.SN=B.ATD_SN GROUP BY NAME
SELECT E.SN, E.Name, SUM(D.DAYCOUNT) AS Total
FROM Employee E LEFT JOIN
(SELECT ATD_SN, CONVERT(varchar(8),ATD_DATE,112), 1 AS DAYCOUNT
FROM ATD
GROUP BY ATD_SN, CONVERT(varchar(8),ATD_DATE,112)
HAVING COUNT(1) > 1 ) D
ON E.SN=D.ATD_SN
GROUP BY E.SN, E.Name
SELECT E.NAME,COUNT(A.atd_Date) --A.ATD_SN
FROM
(
SELECT ATD_SN, CAST(convert(char(10),atd_Date,120) AS DATE) AS MYDATE, MAX(atd_Date) AS atd_Date
GROUP BY ATD_SN, CAST(convert(char(10),atd_Date,120) AS DATE)
) A INNER JOIN EMPLOYEE e on A.ATD_SN=e.SN
