ORACLE上的Tricky GROUP BY问题

Question

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

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

COUNT_TABLE看起来像：

设备（国际）PK |数量（国际）------------------1001 |4------------------1002|20-----------------1003|1…

DEVICE_TABLE看起来像：

ID（Int）PK|WiFi（字符串）|电子邮件（字符串）|Bluetooth（字符串）…---------------------------1001 |是|否|否|…---------------------------1002|是|是|否|…---------------------------1003|未知|未知|是|……

限制条件是：

DEVICE_TABLE.ID=COUNT_TABLE.DEVICE

WiFi，电子邮件，蓝牙…是字符串，只能是："是"，"否"或"未知"

最后，我的SQL请求结果是（基于我的示例）：

功能|是|否|未知--------------------------WiFi |24|0|1--------------------------电子邮件|20|4|1--------------------------蓝牙|1|24|0--------------------------…

简而言之，这个请求的目的是求和和和特定功能兼容的所有设备数。

如果你对如何实现这一目标有任何线索，请提前感谢！（也许不可能…）

Answer 1

在Oracle11中，可以将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;

Answer 2

我很高兴能取悦你——你的数据库设计在相对数据库方面还远远不够完美。唯一可能的方法是使用UNION:

select 'WiFi' as Feature, (select count(*) from  DEVICE_TABLE where WiFi = 'Yes') as Yes, (select count(*) from  DEVICE_TABLE where WiFi = 'No') as No 
union
select 'Email' as Feature, (select count(*) from  DEVICE_TABLE where Email = 'Yes') as Yes, (select count(*) from  DEVICE_TABLE where Email = 'No') as No 
...

Answer 3

1）通过创建一个具有设备ID、引用device_table.ID和capability的设备能力表，数据模型可能会得到改进。

如果设备表中有"是"，请在设备功能中输入一行，并从设备表中删除功能/功能列。

除此之外：

with Capabilities as (
  select  ID, 'WiFi' as capability, Wifi as has_capability
  from    device_table
  union all 
  select  ID, 'Email', Email 
  from    device_table
  union all 
  select  ID, 'BlueTooth', BlueTooth
  from    device_table
)    
select  C.capability
        , sum(case when C.has_capability = 'Yes' then CNT.quantity end) as Yes
        , sum(case when C.has_capability = 'No' then CNT.quantity end) as No
from    device_table D
        inner join Capabilities C on C.ID = D.ID
        left outer join count_table CNT on CNT.DEVICE = D.ID
group by 
        C.capability
order by 
        C.capability

Answer 4

如果您使用的是Oracle 11g，则可以使用Pivot功能来获得解决方案。请参阅以下查询：

select features,nvl(yes,0) yes,nvl(no,0) no,nvl(unknown,0) unknown from (
select * from (select 'Wifi' as features,wifi,nvl(quantity,0) quantity from count_table, device_table where id = device_id)
pivot (sum(nvl(quantity,0)) for Wifi in ('Yes' as yes,'No' as no,'Unknown' as unknown))
Union all
select * from (select 'Bluetooth' as features,bluetooth,nvl(quantity,0) quantity from count_table, device_table where id = device_id)
pivot (sum(nvl(quantity,0)) for bluetooth in ('Yes' as yes,'No' as no,'Unknown' as unknown))
union all
select * from (select 'Email' as features,Email,nvl(quantity,0) quantity from count_table, device_table where id = device_id)
pivot (sum(nvl(quantity,0)) for Email in ('Yes' as yes,'No' as no,'Unknown' as unknown))
)
order by yes desc

请参阅此处的SQLFiddle：http://sqlfiddle.com/#！4/97793/1/0