sql - ORACLE 上棘手的 GROUP BY 问题

Question

我目前面临一个我的 Oracle 知识无法解决的问题，我绝对不是数据库专家，这就是为什么我问你是否知道如何解决我的 SQL 查询问题。

这是我的问题，我有两个表，我们称它们为 DEVICE_TABLE 和 COUNT_TABLE

COUNT_TABLE 看起来像:

设备 (Int) PK |数量(整数)
- - - - - - - - - - - - - - - - - - - - - - - - - -
1001 | 4
- - - - - - - - - - - - - - - - - - - - - - - - - -
1002 | 20
- - - - - - - - - - - - - - - - - - - - - - - - - -
1003 | 1
…

DEVICE_TABLE 看起来像:

ID (Int) PK |无线网络(字符串)|电子邮件(字符串)|蓝牙(字符串)| …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1001 |是 |没有 |没有 | …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1002 |是 |是 |没有 | …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1003 |未知 |未知 |是 | …
…

约束是:

DEVICE_TABLE.ID = COUNT_TABLE.DEVICE

WiFi、电子邮件、蓝牙……是只能是:"is"、“否”或“未知”的字符串

最后，我的 SQL 请求结果预期是(基于我的示例):

特色 |是 |没有 |未知
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
无线网络 | 24 | 0 | 1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
电子邮件 | 20 | 4 | 1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
蓝牙 | 1 | 24 | 0
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
…

简而言之，此请求的目的是对与特定功能兼容的所有设备计数求和。

如果您对如何实现这一点有任何线索，请提前感谢您! (也许不可能……)

Answer 1

在 Oracle 11 中，您可以使用 pivot条款连同 unpivot条款:

with 
count_table as (
     select 1001 device_id,  4 quantity from dual union all
     select 1002 device_id, 20 quantity from dual union all
     select 1003 device_id,  1 quantity from dual 
),
device_table as (
     select 1001 id, 'Yes'     wifi, 'No'       email, 'No'  bluetooth from dual union all
     select 1002 id, 'Yes'     wifi, 'Yes'      email, 'No'  bluetooth from dual union all
     select 1003 id, 'Unknown' wifi, 'Unknown'  email, 'Yes' bluetooth from dual 
)
----------------------------------------
select * from (
      select
        feature,
        yes_no_unknown,
        sum(quantity)  quantity
      from 
         count_table  c join 
         device_table d on c.device_id = d.id
      unpivot  ( yes_no_unknown
                 for feature in (wifi, email, bluetooth)
      ) 
      group by 
      feature,
      yes_no_unknown
)  
pivot ( sum (quantity)
        for yes_no_unknown in ('Yes' as yes, 'No' as no, 'Unknown' as unknown)
)
;

或者，您可能希望将两个现有表连接到包含三个所需行的值的第三个表。它也可能更容易阅读:

with 
count_table as (
     select 1001 device_id,  4 quantity from dual union all
     select 1002 device_id, 20 quantity from dual union all
     select 1003 device_id,  1 quantity from dual 
),
device_table as (
     select 1001 id, 'Yes'     wifi, 'No'       email, 'No'  bluetooth from dual union all
     select 1002 id, 'Yes'     wifi, 'Yes'      email, 'No'  bluetooth from dual union all
     select 1003 id, 'Unknown' wifi, 'Unknown'  email, 'Yes' bluetooth from dual 
)
----------------------------------------
select
   f.txt,
   sum(case when ( f.txt = 'wifi'      and d.wifi      = 'Yes' ) or
                 ( f.txt = 'email'     and d.email     = 'Yes' ) or
                 ( f.txt = 'bluetooth' and d.bluetooth = 'Yes' ) 
            then   c.quantity
            else   0 end
      ) yes,
   sum(case when ( f.txt = 'wifi'      and d.wifi      = 'No' ) or
                 ( f.txt = 'email'     and d.email     = 'No' ) or
                 ( f.txt = 'bluetooth' and d.bluetooth = 'No' ) 
            then   c.quantity
            else   0 end
      ) no,
   sum(case when ( f.txt = 'wifi'      and d.wifi      = 'Unknown' ) or
                 ( f.txt = 'email'     and d.email     = 'Unknown' ) or
                 ( f.txt = 'bluetooth' and d.bluetooth = 'Unknown' ) 
            then   c.quantity
            else   0 end
      ) unknown
from 
   count_table  c                                   join 
   device_table d on c.device_id = d.id     cross   join
   (
        select 'wifi'      txt from dual union all
        select 'email'     txt from dual union all
        select 'bluetooth' txt from dual
   ) f
group by 
    f.txt;