本文介绍了sql 在一个查询中获取总数和过滤计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我希望能够说明每个团队中得分超过 10 分的用户百分比.目前这需要两个查询:
I want to be able to tell what percentage of users for each team have more than 10 points. This currently requires two queries:
SELECT COUNT(*) as winners, team FROM users WHERE points > 10 GROUP BY team SELECT COUNT(*) as total, team FROM users GROUP BY team我可以一并执行此操作,以便得到如下结果:
Can I do this in one so I get a result like this:
winners, total, team 5, 16, A 推荐答案您可以使用 Case .. When 检查特定行的 points 是否超过 10,并相应地计数(使用 Sum()).
You can use Case .. When to check if points are more than 10 for a particular row, and count it accordingly (using Sum()).
SELECT COUNT(*) as total, SUM(CASE WHEN points > 10 THEN 1 ELSE 0 END) AS winners, team FROM users GROUP BY team在 MySQL 中,我们可以进一步缩短为 Sum() 函数可以简单地将条件运算符/函数的结果转换为 0/1(分别为 false/true):
In MySQL, we can shorten it further as Sum() function can simply cast results of conditional operators/functions to 0/1 (for false/true respectively):
SELECT COUNT(*) as total, SUM(points > 10) AS winners, team FROM users GROUP BY team更多推荐
sql 在一个查询中获取总数和过滤计数
发布评论