LeetCode585.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&＃xff0c;如果大于1&＃xff0c;就说明至少跟一个其他投保人在 2015 年的投保额相同

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

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_2FROMinsurance
) a
WHERE a.cnt_1 > 1 AND a.cnt_2 &＃61; 1


子查询

SELECT SUM(A.TIV_2016) AS &＃96;TIV_2016&＃96;
FROM
(SELECT DISTINCT A.* FROM insurance AS AJOIN insurance AS B ON (B.PID !&＃61; A.PID AND B.TIV_2015 &＃61; A.TIV_2015)
) AS A
LEFT JOIN
(SELECT DISTINCT A.pidFROM insurance AS AJOIN insurance AS B ON (B.PID !&＃61; A.PID AND B.LAT &＃61; A.LAT AND B.LON &＃61; A.LON)
) AS BON (A.pid &＃61; B.pid)
WHERE B.pid IS NULL


自连接

SELECT SUM(i4.TIV_2016)as TIV_2016
FROM insurance as i4
JOIN
(SELECT i1.PID as PIDFROM insurance as i1,insurance as i2WHERE i1.TIV_2015&＃61;i2.TIV_2015GROUP BY i1.PIDHAVING count(*)>1
)AS t
ON i4.PID&＃61;t.PID
WHERE i4.PID IN
(SELECT MAX(i3.PID)FROM insurance as i3GROUP BY i3.LAT,i3.LONHAVING COUNT(*)&＃61;1
)