ランキングを求める

ranking.sql

/* **************************
ランキングを求める : MySQL
************************** */
create table products (
    name varchar(32) not null,
    price int not null
)
;

insert into products values
    ('りんご', 50), ('みかん', 100), ('ぶどう', 50),
    ('スイカ', 80), ('レモン', 30), ('バナナ', 50)
;

-- 相関サブクエリ
select P1.name, P1.price, 
    (select count(P2.price)
        from products P2
        where P2.price > P1.price) + 1 as rank_1
from
    products P1
order by rank_1
;

-- LEFT JOINを使う。まずは集合の内容を見てみる。
select
    P1.name, P1.price, P2.name, P2.price
from
    products P1
    left outer join products P2
        on P1.price < P2.price
order by
    P1.name
;

-- これを集約すればランキングになる。
select
    P1.name, max(P1.price), count(P2.name) + 1 as rank_1
from
    products P1 LEFT JOIN products P2
        on P1.price < P2.price
group by
    P1.name
order by
    rank_1
;

/* *** Clearance ***
drop table if exists foo.products
;
*/