MySQL中HAVING子句用salary*months报错Unknown column 'salary'的原因

Let's break down why your query is throwing that error and how to fix it while sticking to using the HAVING clause as you want.

First, let's recap your original query:

SET @mte := (SELECT MAX(salary * months) FROM employee); 
SELECT COUNT(name), salary*months FROM employee 
GROUP BY salary*months 
HAVING salary*months = @mte;

The Root Cause #

In modern MySQL (5.7+), the ONLY_FULL_GROUP_BY SQL mode is enabled by default. This mode enforces strict adherence to SQL standards, which means:

• Any column or expression used in SELECT, HAVING, or ORDER BY must either:
  1. Be explicitly listed in the GROUP BY clause, or
  2. Be wrapped in an aggregate function (like MAX(), COUNT(), etc.)

Your HAVING clause uses salary*months, which looks like it matches the GROUP BY expression—but here's the catch: when you reference salary directly in that expression within HAVING, MySQL sees it as a non-grouped column. Since you're grouping by the computed salary*months (not the individual salary column), salary doesn't exist as a valid column in the grouped result set. That's why you get the "Unknown column 'salary'" error.

Fixed Query Using HAVING #

The simplest fix is to assign an alias to your computed salary*months expression in the SELECT clause, then use that alias in both GROUP BY and HAVING. This works because HAVING can reference aliases defined in SELECT (unlike WHERE):

SET @mte := (SELECT MAX(salary * months) FROM employee); 
SELECT COUNT(name), salary * months AS total_earnings 
FROM employee 
GROUP BY total_earnings 
HAVING total_earnings = @mte;

Why This Works #

• By aliasing salary*months as total_earnings, you're telling MySQL to treat this computed value as a single column in the grouped result.
• Using the alias in GROUP BY makes it clear that we're grouping by this computed value.
• The HAVING clause now references the alias, which is a valid grouped column, so MySQL doesn't throw an error about missing columns.

Alternatively, if you prefer not to use an alias, you could repeat the full expression in HAVING but wrap it in an aggregate function (though this is less clean):

SET @mte := (SELECT MAX(salary * months) FROM employee); 
SELECT COUNT(name), salary*months FROM employee 
GROUP BY salary*months 
HAVING MAX(salary*months) = @mte;

But using the alias is the more readable and maintainable approach.

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