partition分组函数,简化分组
原来语句
select ROUND(SUM(TIV_2016),2) TIV_2016
from (select a.*,b.TIV_2015_count,c.LAT_LON_count from insurance a
left join (select TIV_2015,count(*) TIV_2015_count from insurance group by TIV_2015) b
on a.TIV_2015=b.TIV_2015
left join (select CONCAT(LAT,LON) LAT_LON,count(*) LAT_LON_count from insurance group by LAT,LON) c
on CONCAT(a.LAT,a.LON)=c.LAT_LON ) d
where d.TIV_2015_count>1 and d.LAT_LON_count=1
使用分组函数
select ROUND(sum(TIV_2016),2) as TIV_2016 from(
select *,
count(PID) over (partition by TIV_2015) as num_tiv,
count(PID) over(partition by LAT,LON) as num_city
from insurance)h
where h.num_tiv >1 and num_city=1
标签:count,TIV,partition,LON,---,2015,sql,LAT,select
From: https://www.cnblogs.com/Zhouya/p/17408315.html