sql在一个查询中获取总计和筛选计数

你可以用 Case .. When points 对于特定的行,大于10,并相应地计算它(使用 Sum()

SELECT COUNT(*) as total, 
       SUM(CASE WHEN points > 10 THEN 1 ELSE 0 END) AS winners, 
       team 
FROM users 
GROUP BY team

Sum() 函数可以简单地将条件运算符/函数的结果强制转换为0/1(分别为false/true):

SELECT COUNT(*) as total, 
       SUM(points > 10) AS winners, 
       team 
FROM users 
GROUP BY team

SELECT COUNT(*) as total    
, SUM(case when points > 10 then 1 else 0 end) as winners
, team 
FROM users 
GROUP BY team

也可以做:

SELECT COUNT(*) as total    
, COUNT(case when points > 10 then 1 else NULL end) as winners
, team 
FROM users 
GROUP BY team

你可以在下面试试

SELECT COUNT(*) as winners, 
team, (select count(*) from users) as total FROM users 
WHERE points > 10 GROUP BY team

SELECT team, COUNT(*) as total , 
       count(CASE WHEN points > 10 THEN 1 end) AS winners  

FROM users 
GROUP BY team