mysql 当 UNION 显示 [Err] 1111 - 组函数的无效使用

Question

我在 hackerrank 上做了一个 sql oj。我需要首先对人员进行排序，并列出姓名和职业以及相应的数量。数据库表像png图片显示table structure 。我想使用

select type from
(
(
SELECT name as type, 1 as filter FROM occupations
order by name
)
UNION All
(
select concat(count(occupation), ' ', lower(occupation), 's.') as type, 2 as filter
FROM occupations
group by occupation 
order by count(occupation)
)
) result
order by filter, type

解决这个问题，但是得到“[Err] 1111 - Invalid use of group function”。如果我按计数(职业)评论顺序可能没问题。或者只是使用

select concat(count(occupation), ' ', lower(occupation), 's.') as type, 2 as filter
FROM occupations
group by occupation 
order by count(occupation)

也可以，但是一旦union就会收到这个错误

(
SELECT name as type, 1 as filter FROM occupations
order by name
)
UNION All

(
select concat(count(occupation), ' ', lower(occupation), 's.') as type, 2 as filter
FROM occupations
group by occupation 
order by count(occupation)
)    

但是我把这个联合复制到postgresql(更改字符串契约函数)就可以了，没有这个错误。 postgresql is okay在mysql中，我必须将这个“order by count(ocupation)”设置转换为另一个临时表来解决这个错误，如下所示:

select type from 
(
    (SELECT concat(name, '(', LEFT(occupation , 1), ')') as type, 1 as filter FROM occupations
    order by name)
UNION All
    (select * from 
        (
        select concat('There are a total of ', count(occupation), ' ', lower(occupation), 's.') as type, 2 as filter
        FROM occupations
        group by occupation 
        order by count(occupation)
        ) 
    result)
) last 
order by filter, type

我很困惑，非常感谢您的帮助!

Answer 1

order by 在联合内无效。将 order by 移到联合运算之外。