建表
CREATE TABLE orders ( order_number INT, customer_number INT, order_date DATE, required_date DATE, shipped_date DATE, STATUS VARCHAR(20), COMMENT VARCHAR(30) ); INSERT INTO orders VALUES(1, 1, '2017-04-09', '2017-04-13', '2017-04-12', 'Closed', NULL); INSERT INTO orders VALUES(2, 2, '2017-04-15', '2017-04-20', '2017-04-18', 'Closed', NULL); INSERT INTO orders VALUES(3, 3, '2017-04-16', '2017-04-25', '2017-04-20', 'Closed', NULL); INSERT INTO orders VALUES(4, 3, '2017-04-18', '2017-04-28', '2017-04-25', 'Closed', NULL);子查询
SELECT t.customer_number FROM ( SELECT customer_number, COUNT(order_number) FROM orders GROUP BY customer_number ORDER BY COUNT(order_number) DESC LIMIT 1 ) t;开窗函数
SELECT t.customer_number FROM ( SELECT customer_number, rank() over(ORDER BY COUNT(order_number ) DESC) ranks FROM orders GROUP BY customer_number ) t WHERE t.ranks = 1进阶
SELECT customer_number FROM ( SELECT customer_number, dense_rank() over (ORDER BY COUNT(order_number) DESC) ran FROM orders GROUP BY customer_number ) a WHERE ran=1;