本文共 635 字，大约阅读时间需要 2 分钟。
with tempT as (select t.* from tablexxx t where layer_code = '18' and state = 0)select t.id,t.city_code,count(0) from tempT tjoin tempT a on a.city_code = t.city_code and a.area < t.areagroup by t.id,t.city_code having count(0) <= 3order by city_code,count(0)
sql要求
查询每个城市里面积最大的几条数据
1.构造基础数据，避免全表关联查耗时
with tempT as
(select t.* from tablexxx t where layer_code = '18' and state = 0)
2.查出每条数据city_code一致的情况下(a.city_code = t.city_code) ，面积比自己小的数量
select t.id,t.city_code,count(0) from tempT t
join tempT a on a.city_code = t.city_code and a.area < t.area group by t.id,t.city_code
3.取topN
having count(0) <= 3 order by city_code,count(0)
转载地址：http://yriob.baihongyu.com/