mysql - 案例 When 案例之间的联合

Question

假设我有一个如下所示的 SQL 语句:

select supplier, case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end as platform,
       count(*) as clicks
from supplier_table
group by supplier, platform;

这为我提供了每个供应商在不同平台上收到的点击次数的细目分类示例:供应商“ABC”的[("ABC",mobile, 200),("ABC", tablet, 300)]

我的目标是执行此操作并找到两次点击的总和并将其放在名为“全部”的平台中。

我尝试通过添加一个额外的大小写来使用相同的 SQL 语句来做到这一点。

select supplier, case when platform in (5,6) then 'mobile' 
        when platform in (7,8) then 'tablet' when platform in (5,6,7,8) then 'all' 
       end as platform,
       count(*) as clicks
from supplier_table
group by supplier, platform;

但这不起作用，并且在没有“所有”平台的情况下给出与上面相同的结果。是否可以通过使用 Case When 语句来实现，或者我是否需要通过选择原始 SQL 结果来进行上层聚合？

谢谢。

更新:

它是 MYsql (RDBMS)，是的，我需要一个值为“all”的单独行(这将包含“移动”和“平板电脑”的点击总和。

例如:[("ABC",mobile, 200),("ABC", tablet, 300), ("ABC", all, 500)]

Answer 1

如果你想要这样的结果

supplier platform clicks
abc      tablet   12
abc      mobile   34

将您的案例陈述添加到 GROUP BY

select supplier, case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end as platform,
       count(*) as clicks
from supplier_table
group by supplier,case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end

或者，如果您想要这样的结果:

supplier tablet_clicks mobile_clicks total_clicks
abc      12            34            46

您可以将案例拆分为 1 的总和，就像这样的条件计数:

select supplier, sum(case when platform in (5,6) then 1 end) as mobile_clicks,
               sum(case when platform in (7,8) then 1 end) as tablet_clicks,
               count(1) total_clicks
from supplier_table
group by supplier

在看到你的更新后，如果你想要这样的结果

supplier platform clicks total_clicks
abc      tablet   12     46
abc      mobile   34     46

你需要像这样使用子查询

select supplier, case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end as platform,
       count(*) as clicks,
       (select count(1) from supplier_table as x where x.supplier=supplier_table.supplier where x.platform in (5,6,7,8)) as total_clicks
from supplier_table
group by supplier,case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end

如果你想要这样的结果

supplier platform clicks
abc      tablet   12
abc      mobile   34
abc      all      46

你确实使用了 UNION 作为一个单独的非隔离查询怀疑

select supplier, case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end as platform,
       count(*) as clicks
from supplier_table
group by supplier,case when platform in (5,6) then 'mobile' 
               when platform in (7,8) then 'tablet' end
UNION ALL
select supplier,'all',count(1) from supplier_table 
where platform in (5,6,7,8)
group by supplier