获得总计数和子集计数的最简单方法?
问题描述：
我试图这样做:
SELECT COUNT(*),
(
SELECT COUNT(*) FROM attend
WHERE (DATEPART(WEEKDAY,start_date) = 2 OR DATEPART(WEEKDAY,start_date) = 6)
AND empl_no = 12345
)
FROM attend as a
WHERE empl_no = 12345
但这似乎有点难看.有没有更好的方法来做到这一点?
But this seems a little ugly. Is there a better way to do this?
答
您可以使用 CTE:
WITH T1 AS (
SELECT DATEPART(WEEKDAY,start_date) AS weekday
FROM attend
WHERE empl_no = 12345
)
SELECT
(SELECT COUNT(*) FROM T1) AS total,
(SELECT COUNT(*) FROM T1 WHERE weekday = 2 OR weekday = 6) AS subset