SQL按城市升序排列，按人口降序排列

Question

我遇到了一个复杂的问题。我不知道这个SQL的版本，它是学校版。但现在已经不是相关信息了。

我希望城市升序，数字降序。我的意思是，当同一个城市出现几次时，它会先订购最大的数字。

我还需要行号，我尝试过SELECT ROW_NUMBER() OVER(ORDER BY COUNT(FIRST_NAME)) row，但没有成功。

我有两个表，叫做CUSTOMERS和EMPLOYEES。它们都具有FIRST_NAME、LAST_NAME、CITY。

现在我有这样的代码：

SELECT 
CITY, COUNT(FIRST_NAME),
CASE WHEN COUNT(FIRST_NAME) >= 0 THEN 'CUSTOMERS'
END
FROM CUSTOMERS
GROUP BY CITY
UNION
SELECT 
CITY, COUNT(FIRST_NAME),
CASE WHEN COUNT(FIRST_NAME) >= 0 THEN 'EMPLOYEES'
END
FROM EMPLOYEES
GROUP BY CITY

这个SQL代码给我的列表如下：

CITY
NEW YORK    2   CUSTOMERS
MIAMI       1   CUSTOMERS
MIAMI       4   EMPLOYEES
LOS ANGELES 1   CUSTOMERS
CHIGACO     1   CUSTOMERS
HOUSTON     1   CUSTOMERS
DALLAS      2   CUSTOMERS
SAN JOSE    2   CUSTOMERS
SEATTLE     2   CUSTOMERS
SEATTLE     5   EMPLOYEES
BOSTON      1   CUSTOMERS
BOSTON      3   EMPLOYEES

我希望它看起来像这样：

ROW  CITY
1    NEW YORK    2  CUSTOMERS
2    MIAMI       4  EMPLOYEES
3    MIAMI       1  CUSTOMERS
4    LOS ANGELES 1  CUSTOMERS
5    CHIGACO     1  CUSTOMERS
6    HOUSTON     1  CUSTOMERS
7    DALLAS      2  CUSTOMERS
8    SAN JOSE    2  CUSTOMERS
9    SEATTLE     5  EMPLOYEES
10   SEATTLE     2  CUSTOMERS
11   BOSTON      3  EMPLOYEES
12   BOSTON      1  CUSTOMERS

Answer 1

您可以在ORDER BY:中使用窗口函数

SELECT c.*
FROM ((SELECT CITY, COUNT(*) as cnt, 'CUSTOMERS' as WHICH
FROM CUSTOMERS
GROUP BY CITY
) UNION ALL
(SELECT CITY, COUNT(*), 'EMPLOYEES'
FROM EMPLOYEES
GROUP BY CITY
)
) c
ORDER BY MAX(cnt) OVER (PARTITION BY city) DESC,
city,
cnt DESC;