2016年总投资额写一条查询语句打印2016年总投资额(TIV_2016),并保留2位小数,并且投保人需满足以下条件:
1:和一个或多个投保人有相同的TIV_2015。
2:不和其他投保人在同一城市(就是说(纬度,经度)必须唯一)。
备注:sql_competition数据库insurance表完成
SELECT TIV_2015,sum(TIV_2016)
FROM
(SELECT TIV_2015,TIV_2016,CONCAT(LAT,LON) as sit2
FROM
(
SELECT CONCAT(LAT,LON) as sit,count(TIV_2015) FROM insurance
GROUP BY CONCAT(LAT,LON) HAVING count(TIV_2015) = 1
) AS a
JOIN insurance ON CONCAT(LAT,LON)=a.sit
JOIN
(
SELECT TIV_2015 as TIV2015,COUNT(TIV_2015) FROM insurance
GROUP BY TIV_2015 HAVING COUNT(TIV_2015)>1
) AS b ON insurance.TIV_2015 = b.TIV2015
) AS c
GROUP BY TIV_2015
做题笔记
1 两个表变量名一定要重新定义 ,表名一定要定义。
2 having写在groupby之后
3 关于内存的优化(待考证)用a表会比较小,减少对insurance加载的内存。