sql之自连接的神奇用法

1、排名
假设表products内容如下

1.PNG

需求1：按照price对name进行排名【price相同的名次一样且跳过名次】

select 
*,
(select count(*) from products p2 where p2.price>p1.price)+1 as rank_1
from products p1
order by rank_1;
-- 或者
select p1.name,max(p1.price),(count(p2.name)+1) rank_1
from products p1
left join
products p2
on p1.price<p2.price
GROUP BY p1.name
order by rank_1

返回结果

2.PNG

需求2：按照price对name进行排名【price相同的名次一样且不跳过名次】

select 
*,
(select count(distinct p2.price) from products p2 where p2.price>p1.price)+1 as rank_1
from products p1
order by rank_1;
-- 或者
select p1.name,max(p1.price),(count(distinct p2.price)+1) rank_1 
from products p1
left join
products p2
on p1.price<p2.price
GROUP BY p1.name
order by rank_1

返回

3.PNG