1饭入、排名
假設(shè)表products內(nèi)容如下
1.PNG
需求1：按照price對name進(jìn)行排名【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
返回結(jié)果
2.PNG
需求2：按照price對name進(jìn)行排名【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
注：與多表之間進(jìn)行的普通連接相比矛辕，自連接的性能開銷更大【特別是與非等值連接結(jié)合使用的時(shí)候】漠酿，因此用于自連接的列推薦使用主鍵或者在相關(guān)列上建立索引哈恰。
以上內(nèi)容來自《SQL進(jìn)階教程》
