【力扣sql】高频 SQL 50 题（基础版）

585

找出2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和。

Insurance 表：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| pid         | int   |
| tiv_2015    | float |
| tiv_2016    | float |
| lat         | float |
| lon         | float |
+-------------+-------+

select round(sum(tiv_2016),2) 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(lat)=1 and count(lon)=1)