排序:ran() over (order by ) ,dense_rank() over(order by ) ,qualify row_number()over(partition by columns1,columns2,3,4 order by columns desc,columns DESC)=1
ran() over (order by ) :出现相同位次后,跳过之后的位次 例如:1,2,3,5,6,7
dense_rank() over(order by ):出现相同位次后,连续排序 例如::1,2,2,3
qualify row_number()over(partition by columns1,columns2,3,4 order by columns desc,columns DESC)=1:分组并排序,取第几位,Teradata 数据库常用
表:将各地区商品价格由高到低排序,结果如下
第一种:利用rank()over() 函数
select district ,name ,price,
dense_rank() over (partition by district order by price desc) as rank
from districtproducts;
第二种:标量子查询
select t1.district ,t1.name ,t1.price,
(select count(distinct t2.price ) --加distinct 则连续排序
from districtproducts t2
where t1.district=t2.district
and t1.price <t2.price
)+1 as rank
from districtproducts t1
order by t1.district,rank;
第三种:自关联
select t1.district ,t1.name ,max(t1.price),count(distinct t2.price )+1 as rank
from districtproducts t1
left join districtproducts t2
on t1.district=t2.district
and t1.price>t2.price
group by t1.district ,t1.name
order by t1.district ,rank;