1.查询多列数据的最大值
- CASE WHEN 方式
select 'key' ,
case when
case when X < Y then Y else X end
< Z then Z else case when X < Y then Y else X end
end as greatest from greatests;
- 转换行格式后使用MAX函数
select k, max(col) as greatest from (
select k, x as col from greatests
union
select k, y as col from greatests
union
select k, z as col from greatests
) as temp
group by k;
- 使用GREATEST函数
select k, GREATEST(X, GREATEST(Y,Z)) as greatest from greatests;
2.排序
- 跳过位次排序
select name, price,
RANK() OVER (ORDER BY price DESC) as rank from product;
select p1.name, p1.price, (select count(p2.price) from product p2
where p2.price > p1.price) + 1 as rank
from product p1 order by rank;
- 连续排序
select name, price,
DENSE_RANK() OVER (ORDER BY price DESC) as rank from product;
select p1.name, p1.price, (select count(distinct(p2.price)) from product p2
where p2.price > p1.price) + 1 as rank
from product p1 order by rank;
- 分组排序
select district, name, price,
RANK() OVER (PARTITION BY district
ORDER BY price DESC) as rank from product;
