LeetCode--585. 2016年的投资

建表

CREATE TABLE insurance ( PID INT, TIV_2015 INT, TIV_2016 INT, LAT INT, LON INT ); INSERT INTO insurance VALUES(1, 10, 5, 10, 10); INSERT INTO insurance VALUES(2, 20, 20, 20, 20); INSERT INTO insurance VALUES(3, 10, 30, 20, 20); INSERT INTO insurance VALUES(4, 10, 40, 40, 40);

开窗函数

PARTITION BY TIV_2015，如果大于1，就说明至少跟一个其他投保人在 2015 年的投保额相同

PARTITION BY LAT, LON，符合等于1的，说明经纬度独一无二

SELECT ROUND(SUM(TIV_2016), 2) AS TIV_2016 FROM( SELECT *, COUNT(1) over(PARTITION BY TIV_2015) AS cnt_1, COUNT(1) over(PARTITION BY LAT, LON) AS cnt_2 FROM insurance ) a WHERE a.cnt_1 > 1 AND a.cnt_2 = 1

子查询

SELECT SUM(A.TIV_2016) AS `TIV_2016` FROM ( SELECT DISTINCT A.* FROM insurance AS A JOIN insurance AS B ON (B.PID != A.PID AND B.TIV_2015 = A.TIV_2015) ) AS A LEFT JOIN ( SELECT DISTINCT A.pid FROM insurance AS A JOIN insurance AS B ON (B.PID != A.PID AND B.LAT = A.LAT AND B.LON = A.LON) ) AS B ON (A.pid = B.pid) WHERE B.pid IS NULL

自连接

SELECT SUM(i4.TIV_2016)as TIV_2016 FROM insurance as i4 JOIN ( SELECT i1.PID as PID FROM insurance as i1,insurance as i2 WHERE i1.TIV_2015=i2.TIV_2015 GROUP BY i1.PID HAVING count(*)>1 )AS t ON i4.PID=t.PID WHERE i4.PID IN ( SELECT MAX(i3.PID) FROM insurance as i3 GROUP BY i3.LAT,i3.LON HAVING COUNT(*)=1 )