力扣585(MySQL)-2016年的投资（中等）

就如最后一个投保人，第一个投保人同时满足两个条件：
1. 他在 2015 年的投保金额 TIV_2015 为 '10' ，与第三个和第四个投保人在 2015 年的投保金额相同。
2. 他所在城市的经纬度是独一无二的。

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

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

1 create table if not exists insurance_585 (pid integer(11), tiv_2015 numeric(15,2), tiv_2016 numeric(15,2), lat numeric(5,2), lon numeric(5,2) );
2 truncate table insurance_585;
3 insert into insurance_585 (pid, tiv_2015, tiv_2016, lat, lon) values ('1', '10', '5', '10', '10'), ('2', '20', '20', '20', '20'),('3', '10', '30', '20', '20'),('4', '10', '40', '40', '40');

1 SELECT DISTINCT a.* 
2 FROM insurance_585 a
3 JOIN insurance_585 b 
4 ON a.pid != b.pid AND a.tiv_2015 = b.tiv_2015;

1 SELECT DISTINCT a.pid 
2 FROM insurance_585 a
3 JOIN insurance_585 b
4 ON a.pid != b.pid AND a.lat = b.lat AND a.lon = b.lon 

 1 SELECT
 2     sum( temp1.tiv_2016 ) AS tiv_2016 
 3 FROM
 4     (
 5     SELECT DISTINCT a.* 
 6     FROM insurance_585 a
 7     JOIN insurance_585 b 
 8     ON a.pid != b.pid 
 9     AND a.tiv_2015 = b.tiv_2015 
10     ) AS temp1
11 LEFT JOIN (
12   SELECT DISTINCT a.pid 
13   FROM
14       insurance_585 a
15   JOIN insurance_585 b
16   ON a.pid != b.pid AND a.lat = b.lat AND a.lon = b.lon 
17     ) AS temp2 
18 ON temp1.pid = temp2.pid 
19 WHERE
20     temp2.pid IS NULL;

 1 SELECT
 2     sum(a.tiv_2016 ) AS tiv_2016 
 3 FROM insurance_585 a
 4 where EXISTS(
 5     SELECT DISTINCT b.* 
 6     FROM insurance_585 b
 7     WHERE a.pid != b.pid AND a.tiv_2015 = b.tiv_2015 
 8     ) 
 9 AND NOT EXISTS (
10    SELECT DISTINCT a.pid 
11     FROM insurance_585 b
12     WHERE a.pid != b.pid AND a.lat = b.lat AND a.lon = b.lon 
13  ) ;