SQL查询格式纠错：2015年员工采购总额统计语句问题排查

第一个查询（统计员工2015年采购总额）的错误 #

• ROUND函数语法错误：ROUND(SUM(p.TotalPurchases) AS TotalPurchases 缺少闭合括号，正确写法是ROUND(SUM(p.TotalPurchases)) AS TotalPurchases
• 子查询GROUP BY引用错误：子查询里的GROUP BY e.EmployeeID无效，子查询作用域内没有e这个别名（e是外层Employee表的别名），应改为GROUP BY EmployeeID
• 冗余的聚合与分组：子查询已经按EmployeeID分组算出了每位员工的采购总额TotalPurchases，外层无需再用SUM(p.TotalPurchases)，也不需要额外的GROUP BY，直接取子查询的TotalPurchases即可

修正后的第一个查询代码：

SELECT
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    ROUND(p.TotalPurchases) AS TotalPurchases
FROM 
    Employee e
JOIN 
    (SELECT 
        EmployeeID,  
        SUM(Amount) AS TotalPurchases
     FROM Purchase
     WHERE YEAR(PurchaseDate) = 2015
     GROUP BY EmployeeID
    ) p ON e.EmployeeID = p.EmployeeID;

第二个查询（统计2015年有采购记录的员工数）的错误 #

• 字段语法错误：select distinct PurchaseDate EmployeeID 错误地将PurchaseDate重命名为EmployeeID，实际应选取唯一的EmployeeID，正确写法是select distinct EmployeeID
• 缺少年份过滤条件：原查询未限定2015年，会统计所有年份有采购记录的员工数，不符合需求
• 冗余字段：统计员工数不需要PurchaseDate字段，仅需唯一的EmployeeID

修正后的第二个查询代码：

SELECT
   COUNT(*) AS NumbOfEmpPurch
From (
    select distinct EmployeeID 
    from Purchase 
    WHERE YEAR(PurchaseDate) = 2015
) AS UniquePurchase;

内容的提问来源于stack exchange，提问作者Penny