SQL - UNION不订购第二选择

I`m trying to solve this challenge: https://www.hackerrank.com/challenges/the-pads

我正在尝试解决这个挑战:https://www.hackerrank.com/challenge /the pads。

My solution is this in MySQL:

我的解决方案是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);

However it fails to 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.

If I only run

如果我只运行

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

it does order:

它顺序:

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

4 个解决方案

#1

That is correct. The ordering of the result set is based only on the outermost order by. This is true for union as for other operations.

这是正确的。结果集的顺序仅基于最外层的顺序。这对union和其他操作都是如此。

(SELECT CONCAT(Name,'(', SUBSTR(Occupation,1,1),')') AS total
 FROM Occupations
 ORDER BY Name
)
UNION ALL
(SELECT CONCAT('There are total ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') AS total
 FROM Occupations 
 GROUP BY Occupation 
)
ORDER BY (CASE WHEN total LIKE 'There are total%' THEN 1 ELSE 0 END),
         Total;

This assumes that Name never starts with 'There are total', which seems likely.

这就假设了这个名字从来都不是以“There are total”开头的，这似乎是可能的。

#2

The result gets ordered by the final ORDER BY clause. This is ORDER BY total, i.e. by the first column. (You only give this name in the second part of UNION, which would probably not work in another DBMS. You should name the columns you select in a UNION query in the first part.)

结果由最后的ORDER by子句排序。这是一个数的顺序，也就是第一列的顺序。(您只在UNION的第二部分中给出这个名称，这在另一个DBMS中可能不适用。您应该在第一部分的UNION查询中为所选择的列命名。

You want to get names first, then the aggregates. Then you want names alphabetically, aggregates by count (i.e. not alphabetically, not 1 -> 10 -> 11 -> 2 -> 20 ..., but 1 -> 2 -> 10 -> 11 -> 20 ...) then by job name. You can create sortkeys for this task. I assume you really want UNION ALL, not UNION. If I am wrong, change it :-)

你想先得到名字，然后是集合。然后按字母顺序排列名称，按计数累加(即不是按字母顺序排列，不是1 -> 10 -> 11 -> 2 -> 20……)，但1 -> 2 -> 10 -> 11 -> 20…)然后按工作名称。您可以为这个任务创建sortkeys。我猜你真的想要联盟，而不是联盟。如果我错了，改变它:

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;

#3

@gordon answer is fine

@gordon回答很好

But for a more generic case you have to create a "dummy" field to separate each group.

但是对于更一般的情况，您必须创建一个“虚拟”字段来分隔每个组。

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

#4

Based on the answers and my best understanding I came with this solution that works for this case:

基于这些答案和我的理解，我提出了这个适用于这个案例的解决方案:

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

#1