mysql - SQL - UNION 不排序第二个 SELECT

Question

我正在努力解决这个挑战: https://www.hackerrank.com/challenges/the-pads

我在 MySQL 中的解决方案是这样的:

(SELECT CONCAT(Name,'(',SUBSTR(Occupation,1,1),')') FROM Occupations ORDER BY Name)
UNION
(SELECT CONCAT('There are total ', COUNT(Occupation), ' ',LOWER(Occupation),'s.') AS total FROM Occupations 
GROUP BY Occupation 
ORDER BY total);

但是它无法按总数排序。

Ashley(P) 
Samantha(A) 
Julia(D) 
Britney(P) 
Maria(P) 
Meera(P) 
Priya(D) 
Priyanka(P) 
Jennifer(A) 
Ketty(A) 
Belvet(P) 
Naomi(P) 
Jane(S) 
Jenny(S) 
Kristeen(S) 
Christeen(S) 
Eve(A) 
Aamina(D) 
There are total 4 actors. 
There are total 3 doctors. 
There are total 7 professors. 
There are total 4 singers. 

如果我只跑

SELECT CONCAT('There are total ', COUNT(Occupation), ' ',LOWER(Occupation),'s.') AS total FROM Occupations 
GROUP BY Occupation 
ORDER BY total

它确实有顺序:

There are total 3 doctors. 
There are total 4 actors. 
There are total 4 singers. 
There are total 7 professors. 

Answer 1

结果由最后的 ORDER BY 子句排序。这是 ORDER BY total，即按第一列。 (你只在 UNION 的第二部分给出这个名字，这在另一个 DBMS 中可能不起作用。你应该在第一部分的 UNION 查询中命名你选择的列。)

您想先获取名称，然后再获取聚合。然后你想要名字按字母顺序排列，按计数聚合(即不是按字母顺序排列，不是 1 -> 10 -> 11 -> 2 -> 20 ...，而是 1 -> 2 -> 10 -> 11 -> 20 ...) 然后按工作名称。您可以为此任务创建排序键。我假设您真的想要 UNION ALL，而不是 UNION。如果我错了，请更改它:-)

SELECT txt
FROM
(
  SELECT 
    CONCAT(Name, '(', SUBSTR(Occupation, 1, 1), ')') as txt,
    1 as sortkey1,
    CONCAT(Name, '(', SUBSTR(Occupation, 1, 1), ')') as sortkey2
  FROM Occupations
  UNION ALL
  SELECT 
    CONCAT('There are total ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') AS txt,
    2 + COUNT(Occupation) as sortkey1,
    LOWER(Occupation) as sortkey2
  FROM Occupations 
  GROUP BY Occupation 
) data
ORDER BY sortkey1, sortkey2;