[SQL]586(输出某特性众数的对应ID)+585(两个临时表的差)

586. 订单最多的客户

题目

在表 orders 中找到订单数最多客户对应的 customer_number 。

数据保证订单数最多的顾客恰好只有一位。

SELECT customer_number
FROM orders
GROUP BY customer_number
ORDER BY COUNT(customer_number) DESC
LIMIT 1 OFFSET 0;   --取1行数据，从第0行开始

585. 2016年的投资

思路

分步骤：

1. 找出2015年投资相同的人，临时表记为A

(SELECT DISTINCT a.*
      FROM Insurance a, Insurance b 
      WHERE a.TIV_2015 = b.TIV_2015
      AND a.PID != b.PID) A

2. 找出所有坐标一样的人，临时表记为B

(SELECT DISTINCT b.PID
           FROM Insurance a, Insurance b
           WHERE a.LAT = b.LAT
           AND a.LON = b.LON
           AND a.PID != b.PID) B 

3. 采用左联结，保留所有A表中的信息，此时筛选出A表有而B表没有的用户，把他们的TIV_2016用SUM()加一下

最后代码

SELECT SUM(A.TIV_2016) AS TIV_2016
FROM (SELECT DISTINCT a.*
      FROM Insurance a, Insurance b 
      WHERE a.TIV_2015 = b.TIV_2015
      AND a.PID != b.PID) A
LEFT JOIN (SELECT DISTINCT b.PID
           FROM Insurance a, Insurance b
           WHERE a.LAT = b.LAT
           AND a.LON = b.LON
           AND a.PID != b.PID) B 
ON (A.PID = B.PID)
WHERE B.PID IS NULL;