写一个查询语句,将 2016 年 (TIV_2016) 所有成功投资的金额加起来,对于一个投保人,他在 2016 年成功投资的条件是:
1他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
2他所在的城市必须与其他投保人都不同(也就是说维度和经度不能跟其他任何一个投保人完全相同)。
表 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 |
解释
就如最后一个投保人,第一个投保人同时满足两个条件:
- 他在 2015 年的投保金额 TIV_2015 为 ‘10’ ,与第三个和第四个投保人在 2015 年的投保金额相同。
- 他所在城市的经纬度是独一无二的。
第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。
且他所在城市的经纬度与第三个投保人相同。基于同样的原因,第三个投保人投资失败。
所以返回的结果是第一个投保人和最后一个投保人的 TIV_2016 之和,结果是 45 。
--建表语句
CREATE TABLE IF NOT EXISTS insurance (PID INTEGER(11), TIV_2015 NUMERIC(15,2), TIV_2016 NUMERIC(15,2), LAT NUMERIC(5,2), LON NUMERIC(5,2) )
Truncate table insurance
insert into insurance (PID, TIV_2015, TIV_2016, LAT, LON) values ('1', '10', '5', '10', '10')
insert into insurance (PID, TIV_2015, TIV_2016, LAT, LON) values ('2', '20', '20', '20', '20')
insert into insurance (PID, TIV_2015, TIV_2016, LAT, LON) values ('3', '10', '30', '20', '20')
insert into insurance (PID, TIV_2015, TIV_2016, LAT, LON) values ('4', '10', '40', '40', '40')
--答案1
select
sum(TIV_2016) as TIV_2016
from
insurance where
TIV_2015 in
(select
TIV_2015
from
insurance group by
TIV_2015 having count(1)>1) and
concat(lat,lon) in
(select
concat(lat,lon)
from
insurance group by concat(lat,lon) having count(1)=1)
--答案2
--巧用窗口和过滤简洁了很多
select
sum(TIV_2016) as TIV_2016
from
(select
TIV_2016,
count(1) over(partition by TIV_2015) as num,
count(1) over(partition by concat(lat,lon)) as `local`
from insurance) a where
num>1 and `local`=1