在MySQL中，如何使用case语句重写查询？

Question

我有一张MySQL桌：

create table tbl (
  amount int
);

insert into tbl (amount) values (1);
insert into tbl (amount) values (2);
insert into tbl (amount) values (3);
insert into tbl (amount) values (4);

我的目标是通过使用case状态来报告下面的桶中有多少值。

桶A:值0-1

桶B:值2-5

桶C:数值6-9

首先，让我们尝试一个简单的查询：

select "Bucket A" as Bucket, count(amount) "Count"
from tbl
where amount in (0,1)
union
select "Bucket B" as Bucket, count(amount) "Count"
from tbl
where amount in (2,3,4,5)
union
select "Bucket C" as Bucket, count(amount) "Count"
from tbl
where amount in (6,7,8,9);

结果：

+----------+-------+
| Bucket   | Count |
+----------+-------+
| Bucket A |     1 |
| Bucket B |     3 |
| Bucket C |     0 |
+----------+-------+

结果是完美的，但我想要一份案例陈述。

所以我试试看：

select 
sum(case when amount in (0,1) then 1 else 0 end) as "Bucket A",
sum(case when amount in (2,3,4,5) then 1 else 0 end) as "Bucket B",
sum(case when amount in (6,7,8,9) then 1 else 0 end) as "Bucket C"
from tbl;

结果：

+----------+----------+----------+
| Bucket A | Bucket B | Bucket C |
+----------+----------+----------+
|        1 |        3 |        0 |
+----------+----------+----------+

值是正确的，伟大的是，我有一个案例陈述，但问题是，价值得到了旋转。

我怎样才能

1. 用例语句
2. 没有枢轴吗？

Answer 1

您可以使用聚合来完成这一任务：

select (case when amount in (0, 1) then 'Bucket A'
             when amount in (2, 3,4, 5) then 'Bucket B'
             when amount in (6, 7, 8, 9) then 'Bucket C'
        end) as bucket, count(*) as `count`
from tbl
where amount in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
group by (case when amount in (0, 1) then 'Bucket A'
               when amount in (2,3,4,5) then 'Bucket B'
               when amount in (6,7,8,9) then 'Bucket C'
           end);

编辑：

数字克里斯提出了一个非常好的观点。这可以通过使用left outer join来解决

select (case when tbl.amount in (0, 1) then 'Bucket A'
             when tbl.amount in (2, 3,4, 5) then 'Bucket B'
             when tbl.amount in (6, 7, 8, 9) then 'Bucket C'
        end) as bucket, count(tbl.amount) as `count`
from (select 0 as amount union all
      select 2 as amount union all
      select 6 as amount
     ) throwaway left outer join
     tbl
     on throwaway.amount = tbl.amount
where tbl.amount in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
group by (case when tbl.amount in (0, 1) then 'Bucket A'
               when tbl.amount in (2,3,4,5) then 'Bucket B'
               when tbl.amount in (6,7,8,9) then 'Bucket C'
           end);

或者，也许更清楚地说，通过使用原始查询作为子查询：

select buckets.bucket, coalesce(`count`, 0) as `count`
from (select 'Bucket A' as bucket union all
      select 'Bucket B' union all
      select 'Bucket C'
     ) buckets left outer join
     (select (case when amount in (0, 1) then 'Bucket A'
                   when amount in (2, 3,4, 5) then 'Bucket B'
                   when amount in (6, 7, 8, 9) then 'Bucket C'
              end) as bucket, count(*) as `count`
      from tbl
      where amount in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
      group by (case when amount in (0, 1) then 'Bucket A'
                     when amount in (2,3,4,5) then 'Bucket B'
                     when amount in (6,7,8,9) then 'Bucket C'
                 end)
     ) g
     on buckets.bucket = g.bucket;

Answer 2

select 
"Bucket A" as "Bucket", sum(case when amount in (0,1) then 1 else 0 end) as "Count" from tbl
UNION
select "Bucket B", sum(case when amount in (2,3,4,5) then 1 else 0 end) from tbl
UNION
select "Bucket C", sum(case when amount in (6,7,8,9) then 1 else 0 end) from tbl;

像这样？木琴

Answer 3

SELECT "Bucket A" AS Bucket ,
(SELECT SUM(CASE WHEN amount IN (0,1) THEN 1 ELSE 0 END) FROM tbl) AS "COUNT" 
UNION
SELECT "Bucket B" AS Bucket ,
(SELECT SUM(CASE WHEN amount IN (2,3,4,5) THEN 1 ELSE 0 END) FROM tbl) AS "COUNT" 
UNION
SELECT "Bucket C" AS Bucket ,
(SELECT SUM(CASE WHEN amount IN (6,7,8,9) THEN 1 ELSE 0 END) FROM tbl) AS "COUNT" 

方形小提琴演示