这句SQL如何写？

[yinxionglu2009]

DepartID EmployeeID EmployeeName CheckDate CheckTime CheckDateTime eatMoney Toll_MachineID
a001002004001 87207024 季支付 2014-04-09 00:00:00.000 17:23:08 2014-04-09 17:23:08.000 5.00 008
a002010002001 88109976 高爱萍 2014-04-09 00:00:00.000 17:54:14 2014-04-09 17:54:14.000 1.70 002
a002010002001 88109976 高爱萍 2014-04-09 00:00:00.000 18:32:14 2014-04-09 18:32:14.000 1.70 002
a003006001001 S-PROCU-03 张维冰 2014-04-09 00:00:00.000 10:54:19 2014-04-09 10:54:19.000 1.70 004
a002015001001 88100340 傅晓芬 2014-04-09 00:00:00.000 10:54:21 2014-04-09 10:54:21.000 5.00 011
a002010002001 88109968 廖宏菊 2014-04-09 00:00:00.000 10:54:27 2014-04-09 10:54:27.000 2.40 002
a001002005001 87207008 李芸 2014-04-09 00:00:00.000 10:54:38 2014-04-09 10:54:38.000 1.50 008
a002010002001 88109979 董华勤 2014-04-09 00:00:00.000 10:54:38 2014-04-09 10:54:38.000 2.70 002
a002018001001 88100174 刘辉 2014-04-09 00:00:00.000 10:54:43 2014-04-09 10:54:43.000 7.50 004
a003006001001 S-PROCU-03 张维冰 2014-04-09 00:00:00.000 18:54:52 2014-04-09 18:54:52.000 3.50 008
a002010002001 88109976 高爱萍 2014-04-10 00:00:00.000 17:04:24 2014-04-10 17:04:14.000 1.70 002
.............
数据表中的记录太多，无法全部列举
要求统计出17：00以后各公司加班人数（当天多次记录只算一人）
a001（SSLS），a002（SHVS）,a003(SLC) 分别是三家公司

统计结果如图：
统计日期 SSLS SHVS SLC
2014-04-09 1 1 1
2014-04-10 0 1 0

请教SQL语句如何写？

[yinxionglu2009]

谢谢 godbless_zf 的回复和帮助，已结贴

[yinxionglu2009]

请高手看看，这个贴http://bbs.csdn.net/topics/390802220

[--小F--]

SELECT
  CONVERT(CARCHAR(10),CHECKDATE,120) AS 统计日期,
  SUM(CASE WHEN DepartID='0001' THEN 1 ELSE 0 END) AS SSLS,
  SUM(CASE WHEN DepartID='0002' THEN 1 ELSE 0 END) AS SHVS,
  SUM(CASE WHEN DepartID='0003' THEN 1 ELSE 0 END) AS SLC
FROM
   (SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE EmployeeID=T.EmployeeID AND CheckDate=T.CheckDate AND CheckTime>T.CheckTime)) AS T
GROUP BY
     CONVERT(CARCHAR(10),CHECKDATE,120)

[--小F--]

SELECT
  CONVERT(CARCHAR(10),CHECKDATE,120) AS 统计日期,
  SUM(CASE WHEN DepartID='0001' THEN 1 ELSE 0 END) AS SSLS,
  SUM(CASE WHEN DepartID='0002' THEN 1 ELSE 0 END) AS SHVS,
  SUM(CASE WHEN DepartID='0003' THEN 1 ELSE 0 END) AS SLC
FROM
   (SELECT * FROM TB T WHERE CheckTime>'17:00' and NOT EXISTS(SELECT 1 FROM TB WHERE EmployeeID=T.EmployeeID AND CheckDate=T.CheckDate AND CheckTime>T.CheckTime)) AS T
GROUP BY
     CONVERT(CARCHAR(10),CHECKDATE,120)

掉了大于17：00这个条件

[godbless_zf]

引用 1 楼 fredrickhu 的回复:

SELECT
  CONVERT(CARCHAR(10),CHECKDATE,120) AS 统计日期,
  SUM(CASE WHEN DepartID='0001' THEN 1 ELSE 0 END) AS SSLS,
  SUM(CASE WHEN DepartID='0002' THEN 1 ELSE 0 END) AS SHVS,
  SUM(CASE WHEN DepartID='0003' THEN 1 ELSE 0 END) AS SLC
FROM
   (SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE EmployeeID=T.EmployeeID AND CheckDate=T.CheckDate AND CheckTime>T.CheckTime)) AS T
GROUP BY
     CONVERT(CARCHAR(10),CHECKDATE,120)

少了5点钟的判断：

SELECT CheckDate AS 统计日期,
       COUNT(DISTINCT(CASE WHEN DepartID='a001' THEN EmployeeID ELSE NULL END)) AS SSLS,
       COUNT(DISTINCT(CASE WHEN DepartID='a002' THEN EmployeeID ELSE NULL END)) AS SHVS,
       COUNT(DISTINCT(CASE WHEN DepartID='a003' THEN EmployeeID ELSE NULL END)) AS SLC
       
FROM TB 
WHERE DATENAME(Hh,CheckDateTime)>17
GROUP BY CheckDate

[yinxionglu2009]

多谢各位大神，测试下看看

[yinxionglu2009]

另外请教两位大神，刚才这个是筛选出当月每天17点之后的加班人数，要是需要统计当月双休日（周六周日）加班的人数应该如何写？

[godbless_zf]

引用 5 楼 yinxionglu2009 的回复:

另外请教两位大神，刚才这个是筛选出当月每天17点之后的加班人数，要是需要统计当月双休日（周六周日）加班的人数应该如何写？

只需要在where 条件里面加上DATENAME(w,CheckDateTime) in ('星期六','星期日')