sql - 获取动态列的行数

Question

我有一个 sql server 数据库，我正在使用以下查询来获取任何给定员工迟到的出勤日期列表。

SELECT MIN(att.Date) 
  from Attendance att
  where att.Date between '12/01/2011 00:00:00' and '12/31/2011 00:00:00' 
  and att.EmpID = 4700 
  GROUP BY EmpID, CONVERT(VARCHAR, att.Date , 111) 
  having min(CONVERT(VARCHAR, att.Date , 8)) > '09:00:00'

该查询返回一个没有列名的日期列表。
我需要的是计算这个列表的日期。
如何使用此查询完成此操作？

Answer 1

尝试这个:

WITH CTE AS (
    SELECT MIN(att.Date) [minDate]
    FROM Attendance att
    WHERE att.Date between '12/01/2011 00:00:00'
    AND '12/31/2011 00:00:00'
    AND att.EmpID = 4700
    GROUP BY EmpID, CONVERT(VARCHAR, att.Date , 111)
    HAVING MIN(CONVERT(VARCHAR, att.Date , 8)) > '09:00:00')

SELECT COUNT(minDate) FROM CTE

Alt 不使用 CTE:

SELECT COUNT(minDate) FROM (
    SELECT MIN(att.Date) [minDate]
    FROM Attendance att
    WHERE att.Date between '12/01/2011 00:00:00'
    AND '12/31/2011 00:00:00'
    AND att.EmpID = 4700
    GROUP BY EmpID, CONVERT(VARCHAR, att.Date , 111)
    HAVING MIN(CONVERT(VARCHAR, att.Date , 8)) > '09:00:00') AS tbl