1、平均分排名
SELECT
column_1,
AVG(column_2) avgNum
FROM
evaluation_detail
WHERE
eval_id = #{evalId}
GROUP BY column_1
ORDER BY avgNum desc
2、多项数量统计排名
SELECT
column_1,
sum((case when column_2=2 then 1 else 0 end)) excellent,
sum((case when column_3=2 then 1 else 0 end)) qualified,
sum((case when column_4=2 then 1 else 0 end)) baseQualified,
sum((case when column_5=2 then 1 else 0 end)) unqualified
FROM
evaluation_detail
WHERE
eval_id = #{evalId}
GROUP BY column_1
order by excellent desc,qualified desc,baseQualified desc,unqualified desc
3:使用 with rollup 可以实现在分组统计数据基础上再进行相同的统计
场景说明:如统计所有销售员所销售的商品数量排名
COALESCE ( u.nick_name, "总数" ),当u.nick_name == null时会被总数替换
SELECT
u.id,
COALESCE ( u.nick_name, "总数" ),
COALESCE ( sum( s.sale_num ), 0 ) AS total
FROM
app_user u
LEFT JOIN app_user_server s ON s.user_id = u.id
WHERE
u.identification != "USER"
GROUP BY
u.nick_name WITH ROLLUP
HAVING
total < 30
ORDER BY
total DESC