我正在尝试编写一个mysql查询,该查询将返回一列的值的总数,以及基于同一列的where子句的值的总数。

我有一张这样的桌子:

+------------------------+-------+
| color                  | code  |
+------------------------+-------+
| red                    |   200 |
| red                    |   202 |
| blue                   |   105 |
| yellow                 |   136 |
| green                  |   561 |
| red                    |   198 |
| blue                   |   414 |
| green                  |   11  |
| yellow                 |   600 |
| green                  |   155 |
| red                    |   865 |
| blue                   |   601 |
| green                  |   311 |
+------------------------+-------+


如果我运行此查询:

select
    color,
    count(*) as count
from colors
where code > 0 &&
    code <= 500
group by color
order by count(*) desc;


我得到的结果很好,因为这几乎是我想要的:

+------------------------+-------+
| color                  | count |
+------------------------+-------+
| red                    |     3 |
| green                  |     3 |
| blue                   |     2 |
| yellow                 |     1 |
+------------------------+-------+


我还需要返回的是列中值的总数,因此结果表将如下所示。

+------------------------+--------------+-------+
| color                  | total        | count |
+------------------------+--------------+-------+
| red                    |            4 |     3 |
| green                  |            4 |     3 |
| blue                   |            3 |     2 |
| yellow                 |            2 |     1 |
+------------------------+--------------+-------+


因此,总数是颜色列中每个值的数量,计数是与where子句匹配的总数。

谢谢 :)

这是SQLFiddle的链接。

http://sqlfiddle.com/#!9/777f93/2

最佳答案

您需要使用条件聚合来处理计数,并让引擎处理总数。

SELECT color
     , count(*) as Total
     , sum(case when code > 0 and code <= 500  then 1 else 0 end) as cnt
FROM  colors
GROUP BY color
ORDER BY cnt desc;

关于mysql - mysql从where子句获取总数和总数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44006273/

10-13 03:43