两个类似查询之间的反透视问题

Question

试图在oracle 12c中解决问题(SQL：查询以显示有多少用户拥有每个属性(分组))。

with data (id, name, a, b, c, d) as
(
select 1, 'name1', 'yes', 'yes', '', 'yes' from dual union all
select 2, 'name2', 'yes', '', '', 'yes' from dual union all
select 3, 'name3', '', 'yes', '', 'yes' from dual union all
select 4, 'name4', 'yes', 'yes', '', 'yes' from dual union all
select 5, 'name5',  '', '', 'yes', 'yes' from dual 
)
,
coll (a,b,c,d) as
(
select count(a) a, count(b) b, count(c) c, count(d) d from data
)
select * from coll
unpivot 
(
val for (col) in (a, b, c, d)
);

工作正常并产生所需的结果。

而

with data (id, name, a, b, c, d) as
(
select 1, 'name1', 'yes', 'yes', '', 'yes' from dual union all
select 2, 'name2', 'yes', '', '', 'yes' from dual union all
select 3, 'name3', '', 'yes', '', 'yes' from dual union all
select 4, 'name4', 'yes', 'yes', '', 'yes' from dual union all
select 5, 'name5',  '', '', 'yes', 'yes' from dual 
)
--,
--coll (a,b,c,d) as
--(
select count(a) a, count(b) b, count(c) c, count(d) d from data -- Line 1685
--)
--select * from coll
unpivot 
(
val for (col) in (a, b, c, d)
);

产生以下错误。

ORA-00904："D"：标识符无效 00904. 00000 - "%s： 无效标识符" *原因：
*操作： 行错误：1,685 列：50

有人可以帮忙找到为什么会这样吗？

Answer 1

有问题的查询是不同的。

1)在第一个查询中，您最初计算各自列中非null的数量并对其进行unpivot。

2)在第二个中，您首先unpivot，并尝试计算它们。unpivot后，没有列 a，b，c，d。他们将受到col.正如你所说val for col in (a,b,c,d).要获得与第一个相同的结果，请使用

select col,count(*) as val 
from data -- Line 1685
unpivot (
val for col in (a,b,c,d)
)
group by col

Answer 2

with data (id, name, a, b, c, d) as (
select 1, 'name1', 'yes', 'yes', '',    'yes' from dual union all
select 2, 'name2', 'yes', '',    '',    'yes' from dual union all
select 3, 'name3', '',    'yes', '',    'yes' from dual union all
select 4, 'name4', 'yes', 'yes', '',    'yes' from dual union all
select 5, 'name5',  '',   '',    'yes', 'yes' from dual 
)
select * from data
unpivot (  val for (col) in (a, b, c, d) );

输出：

ID NAME  COL VAL
------ ----- --- ---
1 name1 A   yes
1 name1 B   yes
1 name1 D   yes
2 name2 A   yes
2 name2 D   yes
3 name3 B   yes
3 name3 D   yes
4 name4 A   yes
4 name4 B   yes
4 name4 D   yes
5 name5 C   yes
5 name5 D   yes

输出中没有要计数的列A、B、C或D。

如果要获得相同的输出，则需要按COL分组：

with data (id, name, a, b, c, d) as (
select 1, 'name1', 'yes', 'yes', '',    'yes' from dual union all
select 2, 'name2', 'yes', '',    '',    'yes' from dual union all
select 3, 'name3', '',    'yes', '',    'yes' from dual union all
select 4, 'name4', 'yes', 'yes', '',    'yes' from dual union all
select 5, 'name5',  '',   '',    'yes', 'yes' from dual 
)
select COL, COUNT(*) AS VAL from data
unpivot (  val for (col) in (a, b, c, d) )
GROUP BY col
ORDER BY col;

输出：

COL        VAL
--- ----------
A            3
B            3
C            1
D            5

Answer 3

显然，给定您编写的 SELECT 子句，如果第二个查询要工作，结果将全部在一行中，在标记为 A、B、C、D 的四列中。如果这是你想要的，那么你需要条件 COUNT：

with data (id, name, a, b, c, d) as
(
select 1, 'name1', 'yes', 'yes', ''   , 'yes' from dual union all
select 2, 'name2', 'yes', ''   , ''   , 'yes' from dual union all
select 3, 'name3', ''   , 'yes', ''   , 'yes' from dual union all
select 4, 'name4', 'yes', 'yes', ''   , 'yes' from dual union all
select 5, 'name5',  ''  , ''   , 'yes', 'yes' from dual 
)
select count(case col when 'A' then 1 end) as a,
count(case col when 'B' then 1 end) as b,
count(case col when 'C' then 1 end) as c,
count(case col when 'D' then 1 end) as d
from data
unpivot 
(
val for (col) in (a, b, c, d)
);
A  B  C  D
-  -  -  -
3  3  1  5

请注意，要检查的值是"A"、"B"、"C"和"D"，而不是小写版本。在 UNPIVOT 子句中，如果在 IN 子句中不使用别名，则 COL 列中的值将只是 IN 子句 (a， b， c， d) 中列的名称，始终为大写(与 Oracle 对未括在双引号中的所有列名所做的相同)。