每日sql-求2016年成功的投资总和

每日sql-求2016年成功的投资总和

背景

2016年成功的投资条件：
1.2015年投保额至少与另外一个投保人在2015年投保的额度相同
2.此次投保的坐标必须是独一无二的。

DDL

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) );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);

sql

SELECT SUM(insurance.TIV_2016) AS TIV_2016
FROM insurance
WHERE insurance.TIV_2015 IN( SELECT TIV_2015 FROM insurance GROUP BY TIV_2015 HAVING COUNT(*) > 1 ) 
AND CONCAT(LAT, LON) IN( SELECT CONCAT(LAT, LON) FROM insurance GROUP BY LAT , LON HAVING COUNT(*) = 1 );