MySQL查询技巧3

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;