mysql - 在 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.使用case语句
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);

编辑:

数字克里斯提出了一个非常好的观点。这可以通过使用左外连接来解决:

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;