选择在6月份完成MAXIMUM交易的客户的姓名、联系人和邮政编码。
SELECT
Customer.customer_name,
Customer.customer_email,
Customer.customer_postcode
FROM
Customer
INNER JOIN
Sales on Customer.customer_id = Sales.customer_id
WHERE
MAX(Sales.customer_id) IN (SELECT COUNT((sales.customer_id)) AS 'transactions'
FROM sales
GROUP BY (sales.customer_id))
AND MONTH(date_purchased) = 6;
但我得到了这个错误:
Msg 147,Level 15,State 1,Line 4
聚合不能出现在WHERE子句中,除非它位于HAVING子句或选择列表中包含的子查询中,并且聚合的列是外部引用
您获取的是customer_id的最大值,但您想要的是具有最高事务数的customer_id。从您的内部查询开始,使用ORDER BY..DESC
获取顶级客户。
SELECT Sales.customer_id, count(Sales.customer_id) as transactions
FROM Sales
GROUP BY Sales.customer_id
ORDER BY transactions DESC;
现在您已经拥有了顶级customer_id,您应该能够将该结果加入Customers表(将其用作CTE或内部查询(,以获取姓名、联系人和邮政编码。
您当前的查询有许多问题:
MAX
等聚合不能在WHERE
中使用,它们必须在HAVING
部分中- 即使您将其更改为
HAVING
,子查询也是错误的,因为它没有在六月进行筛选 - 一个简单得多的方法是连接表,分组,然后按
COUNT
排序,并取第一行 - 外部六月筛选器应该使用开始和结束日期,而不是
MONTH
函数,以提高性能 - 您应该使用适当的表别名
SELECT TOP (1)
c.customer_name,
c.customer_email,
c.customer_postcode
FROM
Customer c
INNER JOIN
Sales s on Customer.customer_id = Sales.customer_id
WHERE
date_purchased >= '20200601' AND date_purchased < '20200701'
-- note the use of half open interval >= AND <
GROUP BY
c.customer_name,
c.customer_email,
c.customer_postcode
ORDER BY COUNT(*) DESC;