如何修复查询，使其工作?

Question

SELECT 
(SELECT 
CREATEDBY, 
COUNT(CUSTOMER) AS FIRSTMONTH 
FROM 
CUSTOMERSTABLE
WHERE 
CREATEDAT BETWEEN '2021-06-17' 
AND '2021-07-17' 
AND CREATEDBY IN ('Adam', 'John', 'Molly'
GROUP BY 
CREATEDBY), 
(SELECT 
CREATEDBY, 
COUNT(CUSTOMER) AS SECONDMONTH 
FROM 
CUSTOMERSTABLE 
WHERE 
CREATEDAT BETWEEN '2021-07-17' 
AND '2021-08-17' 
AND CREATEDBY IN ('Adam', 'John', 'Molly'
GROUP BY 
CREATEDBY), 
(SELECT 
CREATEDBY, 
COUNT(CUSTOMER) AS THIRDMONTH 
FROM 
CUSTOMERSTABLE
WHERE 
CREATEDAT BETWEEN '2021-08-17' 
AND '2021-09-17' 
AND CREATEDBY IN ('Adam', 'John', 'Molly'
GROUP BY 
CREATEDBY) 
FROM 
CUSTOMERSTABLE

错误:

Msg 116, Level 16, State 1, Line 24
当子查询没有引入EXISTS时，只能在选择列表中指定一个表达式。

Msg 116, Level 16, State 1, Line 27
当子查询没有使用EXISTS引入时，只能在选择列表中指定一个表达式。

Msg 116, Level 16, State 1, Line 30
当子查询没有使用EXISTS引入时，只能在选择列表中指定一个表达式

Answer 1

您可以使用单个select语句进行条件聚合:

SELECT CREATEDBY, 
SUM(CASE WHEN CREATEDAT BETWEEN '2021-06-17' AND '2021-07-17' 
THEN 1 ELSE 0 
END) AS FIRSTMONTH,
SUM(CASE WHEN CREATEDAT BETWEEN '2021-07-17' AND '2021-08-17' 
THEN 1 ELSE 0 
END) AS SECONDMONTH,
SUM(CASE WHEN CREATEDAT BETWEEN '2021-08-17' AND '2021-09-17' 
THEN 1 ELSE 0 
END) AS THIRDMONTH        
FROM CUSTOMERSTABLE
WHERE CREATEDBY IN ('Adam', 'John', 'Molly')
GROUP BY CREATEDBY;

Answer 2

您可以使用PIVOT，例如:

DECLARE @FirstDate  date = '20210617',
@numMonths  tinyint = 3;
;WITH n (n) AS 
(
SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < @numMonths
),
d (MonthNumber, d) AS 
(
SELECT n, DATEADD(MONTH, n.n-1, @FirstDate) FROM n
),
src AS
(
SELECT c.CREATEDBY, MonthNumber, num = COUNT(*)
FROM dbo.CustomerStable AS c INNER JOIN d 
ON c.CREATEDAT >= d.d AND c.CREATEDAT < DATEADD(MONTH, 1, d.d)
WHERE c.CREATEDBY IN ('Adam', 'John', 'Molly')
GROUP BY c.CREATEDBY, d.MonthNumber
)
SELECT CREATEDBY,
FIRSTMONTH  = COALESCE([1],0),
SECONDMONTH = COALESCE([2],0),
THIRDMONTH  = COALESCE([3],0)
FROM src
PIVOT (MAX(num) FOR MonthNumber IN ([1],[2],[3])) AS p;

• db&lt例子;在小提琴

是的，它比条件聚合更复杂一点，但它避免了将特定的月份边界硬编码到查询中，并且更容易更改月份数，作为边界的日期等。

还请注意，在您的原始查询中，如果有一行CREATEDAT发生在17日午夜(或时间根本没有记录)，它将被计数两次。这就是为什么我们在日期范围查询中远离BETWEEN的原因(参见这篇文章和这个视频)。