mybatis 排名持续更新中.....

1、平均分排名SELECTcolumn_1,AVG(column_2) avgNumFROMevaluation_detailWHEREeval_id = #{evalId}GROUP BY column_1ORDER BY avgNum desc2、多项数量统计排名SELECTcolumn_1,sum((case when column_2=2 then 1 else 0 end)) excel

小丑的忧伤

546人浏览 · 2021-04-11 09:44:07

小丑的忧伤 · 2021-04-11 09:44:07 发布

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