Leetcode585. 2016年的投资（中等）

题目
写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。

对于一个投保人，他在 2016 年成功投资的条件是：

他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。
输入格式:
表 insurance 格式如下：

Column Name	Type
PID	INTEGER(11)
TIV_2015	NUMERIC(15,2)
TIV_2016	NUMERIC(15,2)
LAT	NUMERIC(5,2)
LON	NUMERIC(5,2)

PID 字段是投保人的投保编号， TIV_2015 是该投保人在2015年的总投保金额， TIV_2016 是该投保人在2016年的投保金额， LAT 是投保人所在城市的维度， LON 是投保人所在城市的经度。

样例输入

PID	TIV_2015	TIV_2016	LAT	LON
1	10	5	10	10
2	20	20	20	20
3	10	30	20	20
4	10	40	40	40

样例输出

TIV_2016
45.00

解释

就如最后一个投保人，第一个投保人同时满足两个条件：

1. 他在 2015 年的投保金额 TIV_2015 为 '10' ，与第三个和第四个投保人在 2015 年的投保金额相同。
2. 他所在城市的经纬度是独一无二的。

第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。
且他所在城市的经纬度与第三个投保人相同。基于同样的原因，第三个投保人投资失败。

所以返回的结果是第一个投保人和最后一个投保人的 TIV_2016 之和，结果是 45 。

生成数据

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);

解答

-- 选出2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同的用户
SELECT DISTINCT I1.`PID`
FROM insurance AS I1,insurance AS I2
WHERE I1.`TIV_2015` = I2.`TIV_2015` AND I1.`PID` <> I2.`PID`;

-- 选出城市相同的用户
SELECT DISTINCT I1.`PID`
FROM insurance AS I1,insurance AS I2
WHERE I1.`LAT`= I2.`LAT`AND I1.`LON` = I2.`LON` AND I1.`PID` <> I2.`PID`; 

-- 选出15年有相同投保额的用户中去掉城市不唯一的用户即可
SELECT SUM(DISTINCT I1.`TIV_2016`) AS TIV_2016
FROM insurance AS I1,insurance AS I2
WHERE I1.`TIV_2015` = I2.`TIV_2015` AND I1.`PID` <> I2.`PID` 
AND I1.`PID` NOT IN (SELECT DISTINCT I1.`PID`
FROM insurance AS I1,insurance AS I2
WHERE I1.`LAT`= I2.`LAT`AND I1.`LON` = I2.`LON` AND I1.`PID` <> I2.`PID`);

自连接外加嵌套查询也可以

select sum(distinct a.TIV_2016) as 'TIV_2016'
from insurance as a,insurance as b
where a.PID<>b.PID and a.TIV_2015=b.TIV_2015 
and 0=(select count(*) from insurance as c where c.LAT=a.LAT and c.LON=a.LON and c.PID<>a.PID);

排除法

SELECT SUM(A.TIV_2016) AS `TIV_2016`
FROM insurance AS A
WHERE EXISTS(
    SELECT *
    FROM insurance AS B
    WHERE B.PID != A.PID AND B.TIV_2015 = A.TIV_2015 
) AND NOT EXISTS(
    SELECT *
    FROM insurance AS B
    WHERE B.PID != A.PID AND B.LAT = A.LAT AND B.LON = A.LON
)

集合差法

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