查询简化Oracle Northwind

Question

任务:

找出最大客户所属的国家。

查询

SELECT country,
       count(*)
FROM customers
GROUP BY country
HAVING count(*) =
  (SELECT max(max_sal)
   FROM
     (SELECT count(*) max_sal
      FROM customers
      GROUP BY country)) ;

结果

​

​

结果是正确的，但我认为编写查询是困难的。

问题:是否有任何简单的方法来重写这个查询。

Answer 1

我可能漏掉了什么东西，但可以这么简单：

SELECT *
  FROM (  SELECT country, COUNT (*) max_sal
            FROM customers
        GROUP BY country
        ORDER BY COUNT (*) DESC)
 WHERE ROWNUM <= 1;

Answer 2

您可以使用WITH子句：

WITH
  c AS ( 
    SELECT country, Count(1) n
    FROM customers
    GROUP BY country)
SELECT country, n 
FROM c
WHERE n = (SELECT Max(n) FROM c)

Answer 3

U可以在()上使用解析函数，得到最大值(avg，min等)的结果。在每一行结果中，然后在哪里比较计数(1)和最大(计数(1))

下面是一个例子：

SELECT country, cnt, max_cnt
  FROM (SELECT country, COUNT(1) AS cnt, MAX(COUNT(1)) over() max_cnt
           FROM customers
          GROUP BY country)
 WHERE cnt = max_cnt