赞
踩
Create Table If Not Exists Insurance (pid int, tiv_2015 float, tiv_2016 float, lat float, lon float)
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')
写一个查询语句,将 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 。
题解一
# Write your MySQL query statement below
select round(sum(TIV_2016),2) TIV_2016 from (select TIV_2016,count(*) over(partition by TIV_2015) as count_tiv_2015,count(*) over(partition by LAT,LON) as count_lat_lon from insurance) as temp where count_tiv_2015 > 1 and count_lat_lon=1;
筛选出一个人在 2015 年投保额至少和任何一个人在 2015 年的投保额相同,就是按照 2015 年的投保额进行分组,并计算个数
count(*) over(partition by tiv_2015) count_tiv_2015
筛选不同的地点,就是按照经纬度进行分组,计算每个每个经纬度的个数
count(*) over(partition by lat, lon) count_lat_lon
题解二
select round(sum(TIV_2016),2) TIV_2016 from insurance where TIV_2015 in (select b.TIV_2015 from insurance b group by b.TIV_2015 having count(*) > 1) and concat(lat,lon) in (select concat(lat,lon) from insurance group by lat,lon having count(*) = 1 );
使用子查询
筛选出一个人在 2015 年投保额至少和任何一个人在 2015 年的投保额相同,使用 group by 按照 tiv_2015 分组,并且使用 count() 计算,筛选出大于 1 的数据,因为大于 1 代表至少有两个人在 2015 年投保额相同
select tiv_2015 from insurance group by tiv_2015 having count(*) > 1;
筛选不同的地点,就是按照 lat 和 lon 进行分组,这里查询的字段是使用 concat() 连接 la
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。