Tricky GROUP BY在Oracle上的问题 [英] Tricky GROUP BY issue on ORACLE
问题描述
我目前面临着我的Oracle知识无法解决的问题，我绝对不是数据库专家，这就是为什么我问您是否有解决SQL查询问题的原因.
I’m currently facing an issue that my Oracle knowledge cannot solve, I’m definitely not DB expert and that’s why I ask you if you have any idea how to solve my SQL query issue.
这是我的问题，我有两个表，我们称它们为DEVICE_TABLE和COUNT_TABLE
Here’s my problem, I have two tables, let’s call them DEVICE_TABLE and COUNT_TABLE
COUNT_TABLE如下:
COUNT_TABLE looks like :
DEVICE (Int) PK | QUANTITY (Int)
- - - - - - - - - - - - - - - - - - - - - - - - - - -
1001 | 4
- - - - - - - - - - - - - - - - - - - - - - - - - - -
1002 | 20
- - - - - - - - - - - - - - - - - - - - - - - - - - -
1003 | 1
…
DEVICE_TABLE如下:
DEVICE_TABLE looks like :
ID (Int) PK | WiFi (String) | Email (String) | Bluetooth(String) | …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1001 | Yes | No | No | …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1002 | Yes | Yes | No | …
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1003 | Unknown | Unknown | Yes | …
…
约束为:
DEVICE_TABLE.ID = COUNT_TABLE.DEVICE
DEVICE_TABLE.ID = COUNT_TABLE.DEVICE
WiFi，电子邮件，蓝牙…是只能为是"，否"或未知"的字符串
WiFi, Email, Bluetooth… are Strings that can only be : "Yes", "No" or "Unknown"
最后，我期望的SQL请求结果是(根据我的示例):
Finally, my SQL request result expected is (based on my example):
Feature | Yes | No | Unknown
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
WiFi | 24 | 0 | 1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Email | 20 | 4 | 1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Bluetooth | 1 | 24 | 0
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
…
简而言之，此请求的目的是汇总与特定功能兼容的所有设备数.
In few words, aim of this request is to sum all devices count that are compatible with a particular feature.
如果您对如何实现此目标有任何线索，请提前感谢！ (也许不可能...)
Thank you in advance if you have any clue on how to achieve this ! (Maybe it is not possible…)
推荐答案
在Oracle 11中，可以将pivot子句与unpivot子句一起使用:
In Oracle 11, you can use the pivot clause together with the unpivot clause:
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)
)
;
或者，您可能希望将两个现有表连接到包含三个所需行的值的第三个表.也可能更容易阅读:
Alternatively, you might want to join the two existing tables to a third table that containts the values for the three desired rows. It's probably a bit easier to read, too:
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;
这篇关于Tricky GROUP BY在Oracle上的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
