SQL查询问题

Question

我有这个查询要显示

有 2 张表，我将从表 renovation 中获取装修编号，而 customer-id 和 name 来自表 1，customer。

SELECT c.[Customer-ID], c.name, COUNT(*)"Number of Renovation"
FROM CUSTOMER c, RENOVATION r
WHERE c.[Customer-ID] = r.[Customer-ID]
GROUP BY c.[Customer-ID], c.name
HAVING Count(*) in 
(SELECT COUNT(*) FROM RENOVATION GROUP BY [Customer-ID])
ORDER BY c.[customer-id]

这不是我进行查询的正确方法，有人知道如何缩短查询吗？或其他方法吗？虽然它仍然找到答案。顺便说一句，我正在学习 SQL Server。

Answer 1

好的，所以您需要客户和他们的装修 - 为什么不直接使用:

SELECT c.[Customer-ID], c.name, COUNT(*) AS 'Number of Renovations'
FROM dbo.CUSTOMER c
INNER JOIN dbo.RENOVATION r ON c.[Customer-ID] = r.[Customer-ID]
GROUP BY c.[Customer-ID], c.name

我不太明白您要通过查询的 HAVING COUNT(*) IN...... 部分实现什么......

如果您想让所有客户至少进行一次装修 - 试试这个:

SELECT c.[Customer-ID], c.name, COUNT(*) AS 'Number of Renovations'
FROM dbo.CUSTOMER c
INNER JOIN dbo.RENOVATION r ON c.[Customer-ID] = r.[Customer-ID]
GROUP BY c.[Customer-ID], c.name
HAVING COUNT(*) > 0