高频 SQL 50 题（基础版）| 585. 2016年的投资

Insurance 表：

±------------±------+
| Column Name | Type |
±------------±------+
| pid | int |
| tiv_2015 | float |
| tiv_2016 | float |
| lat | float |
| lon | float |
±------------±------+
pid 是这张表的主键(具有唯一值的列)。
表中的每一行都包含一条保险信息，其中：
pid 是投保人的投保编号。
tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。
lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
lon 是投保人所在城市的经度。题目数据确保 lon 不为空。

编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：

• 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
• 他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。

tiv_2016 四舍五入的 两位小数 。

查询结果格式如下例所示。

示例 1：

输入：
Insurance 表：
±----±---------±---------±----±----+
| 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 |
±---------+
解释：
表中的第一条记录和最后一条记录都满足两个条件。
tiv_2015 值为 10 与第三条和第四条记录相同，且其位置是唯一的。

第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同，并且位置与第三条记录相同，这也导致了第三条记录不符合题目要求。
因此，结果是第一条记录和最后一条记录的 tiv_2016 之和，即 45 。

-- 解1 EXISTS NOT 和 EXISTS
SELECT round(SUM(tiv_2016),2) AS tiv_2016
FROM (
    SELECT p.tiv_2016
    FROM Insurance p
    WHERE EXISTS (
        SELECT 1
        FROM Insurance
        WHERE tiv_2015 = p.tiv_2015 AND pid != p.pid
    )
    AND NOT EXISTS (
        SELECT 1
        FROM Insurance
        WHERE lat = p.lat AND lon = p.lon AND pid != p.pid
    )
) AS t;

-- 解2 IN NOT 和 IN
SELECT
		round( SUM( tiv_2016 ), 2 ) AS tiv_2016 
FROM
		insurance i1 
WHERE 
	tiv_2015 IN ( SELECT tiv_2015 
		FROM insurance i2 
		WHERE i1.pid != i2.pid
)
AND ( lat, lon ) NOT IN (
		SELECT	lat,lon 
		FROM insurance i3 
		WHERE i1.pid != i3.pid
)

-- 解3 开窗函数
SELECT ROUND(SUM(tiv_2016),2) tiv_2016
FROM 
(
	SELECT *,
	COUNT(*) OVER(PARTITION BY tiv_2015 ) cnt_tiv_2015,
	COUNT(*) OVER(PARTITION BY lat,lon ) cnt_lat
	FROM Insurance 
) t
WHERE cnt_tiv_2015 > 1 AND cnt_lat = 1

-- 解4 不用开窗函数版
SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM Insurance
WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
)
AND (lat, lon) IN (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
)