为何在AVG中使用DISTINCT后，查询仍返回多条重复薪资记录？

Great question—let’s break this down step by step so you can see exactly what’s happening here.

First, let’s clarify what AVG(DISTINCT Salary) actually does:

• It only affects the calculation of the average salary, not the rows returned by your outer query.
• In your dataset, the salaries are 70000, 60000, 70000, 80000. Using DISTINCT inside AVG() tells SQL to first deduplicate the salary values, leaving us with 60000, 70000, 80000. The average here is (60000 + 70000 + 80000)/3 = 70000.

Now, let’s look at your outer query: select * from Employees where Salary > (SELECT AVG(distinct SALARY) FROM employees). Wait, based on the average we just calculated, Salary > 70000 would only return the row with Salary = 80000—so there might be a small discrepancy here (maybe you meant >= instead of >?). But regardless, the core issue you’re asking about is why duplicate rows are still showing up, and that’s easy to explain:

• The DISTINCT inside the subquery doesn’t modify the original Employees table or the outer query’s behavior. Your outer query is still pulling every row from the original table that meets the salary condition. Since your original table has two separate rows with Salary = 70000, both will be returned if they meet the condition.

If your goal is to only get one unique row for each salary value that meets the condition, you need to apply DISTINCT in the outer query, not just the subquery. For example:

SELECT DISTINCT * FROM Employees 
WHERE Salary > (SELECT AVG(DISTINCT Salary) FROM Employees)

Or if you only care about unique salary values (not all columns), you could use GROUP BY instead:

SELECT Salary, COUNT(*) AS RowCount FROM Employees 
WHERE Salary > (SELECT AVG(DISTINCT Salary) FROM Employees)
GROUP BY Salary

To sum it up: DISTINCT inside an aggregate function like AVG() only affects the data used to calculate the aggregate value. It never automatically deduplicates rows in your outer result set—you have to explicitly tell SQL to do that with DISTINCT or GROUP BY in the outer query.

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