使用Group语句的SQL查询

Question

我有4张桌子：

经理，销售，SaleDetails，产品：

• Manager：ID，名称
• Sales：ID，ManagerID，SaleNO，SaleDate
• SaleDetails：ID，SaleID，ProductID，数量
• 产品：ID，名称，价格

我想为每个经理检索: SaleDate和独特的产品计数；

我的选择如下：

;WITH cte
AS
(
    SELECT sd.SaleID, sd.ProductID FROM dbo.Products p
        INNER JOIN dbo.SalesDetails sd ON sd.ProductID = p.ID
    GROUP BY sd.SaleID, sd.ProductID
)
SELECT
     c.Name AS ManagerName
    ,s.SaleDate
    ,COUNT(ct.ProductID) AS ProductCount
FROM cte ct
    INNER JOIN dbo.Sales s ON ct.SaleID = s.ID
    INNER JOIN dbo.Managers c ON c.ID = s.ConsultantID
GROUP BY s.SaleDate, c.Name

这是最优的吗？您能帮助我用更优化的查询来替换它吗？

• 经理: ID = 1，Name = John；
• 销售: ID = 1，ManagerID = 1，SaleNO = 0015，SaleDate: 2016-09-08，ID = 2，ManagerID = 1，SaleNO = 0016，SaleDate: 2016-09-09
• SaleDetails: ID = 1，SaleID = 1，ProductID = 1，Quantity = 2，ID = 2，SaleID = 1，ProductID = 1，Quantity = 4，ID = 3，SaleID = 1，ProductID = 2，Quantity = 3，ID = 4，SaleID = 2，ProductID = 1，Quantity =3
• 产品: ID = 1，Name = Sony，ID = 2，Name = Samsung

查询必须返回结果：

• ManagerName = John，SaleDate = 2016-09-08，ProductCount =2
• ManagerName = John，SaleDate = 2016-09-09，ProductCount =1

Answer 1

SELECT c.Name AS ManagerName
      ,s.SaleDate
      ,COUNT(p.ProductID)
FROM dbo.Products p
INNER JOIN dbo.SalesDetails sd ON sd.ProductID = p.ID
INNER JOIN dbo.Sales s ON sd.SaleID = s.ID
INNER JOIN dbo.Managers c ON c.ID = s.ConsultantID
GROUP BY s.SaleDate, c.Name,sd.ProductID

Answer 2

正确的答案(正如我读到的问题)是：

SELECT c.Name AS ManagerName, s.SaleDate, COUNT(DISTINCT sd.ProductId)
FROM dbo.SalesDetails sd
     dbo.Sales s
     ON sd.SaleID = s.ID INNER JOIN
     dbo.Managers c
     ON c.ID = s.ConsultantID
GROUP BY s.SaleDate, c.Name;

备注：

• 不需要Products表，因为SalesDetails表具有产品信息。
• 产品id不在GROUP BY中。
• 正确的聚合函数是COUNT(DISTINCT)。