oracle非常量不能用于privot_PL/SQL Challenge 每日一题：2016-11-10 行列转换

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引：

http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处：

http://www.plsqlchallenge.com/

作者：        Kim Berg Hansen

运行环境：SQLPLUS, SERVEROUTPUT已打开

注：本题给出答案时候要求给予简要说明才能得到奖品

我有一张表，保存着每个员工的制服尺码：

create table plch_uniforms (

employee    varchar2(10)

, shirtsize   varchar2(10)

constraint plch_shirtsizes check(

shirtsize in (

'XXXS'

, 'XXS'

, 'XS'

, 'S'

, 'M'

, 'L'

, 'XL'

, 'XXL'

, 'XXXL'

, 'XXXXL'

)

)

)

/

insert into plch_uniforms values ('Albert'  , 'XS')

/

insert into plch_uniforms values ('Benjamin', 'S')

/

insert into plch_uniforms values ('Charles' , 'M')

/

insert into plch_uniforms values ('Donald'  , 'M')

/

insert into plch_uniforms values ('Egbert'  , 'L')

/

insert into plch_uniforms values ('Francis' , 'XL')

/

insert into plch_uniforms values ('George'  , 'XL')

/

insert into plch_uniforms values ('Howard'  , 'XXL')

/

insert into plch_uniforms values ('Jonah'   , 'XXXL')

/

commit

/

我想要一个报表，显示穿着不同尺码的员工数，但是我只需要一个总数字。我需要尺码S, M, L 和 XL的数量, 而所有比S更小的尺码都被归入一个名为"< S" 的列，所有比XL更大的则累加到一个名为 "> XL"的列。

哪些选项能够给我如下的输出：

< S          S          M          L         XL       > XL

---------- ---------- ---------- ---------- ---------- ----------

1          1          2          1          2          2

(A)

select count(

case when regexp_like(shirtsize, '^X+S$') then 1 end

) as "< S"

, count(case shirtsize when 'S'  then 1 end) as "S"

, count(case shirtsize when 'M'  then 1 end) as "M"

, count(case shirtsize when 'L'  then 1 end) as "L"

, count(case shirtsize when 'XL' then 1 end) as "XL"

, count(

case when regexp_like(shirtsize, '^X{2,}L$') then 1 end

) as "> XL"

from plch_uniforms

/

(B)

select "< S", "S", "M", "L", "XL", "> XL"

from (

select shirtsize

from plch_uniforms

)

pivot (

count(*)

for shirtsize in (

'XXXS'  as "< S"

, 'XXS'  as "< S"

, 'XS'  as "< S"

, 'S'  as "S"

, 'M'  as "M"

, 'L'  as "L"

, 'XL' as "XL"

, 'XXL' as "> XL"

, 'XXXL' as "> XL"

, 'XXXXL' as "> XL"

)

)

/

(C)

select "< S", "S", "M", "L", "XL", "> XL"

from (

select shirtsize

from plch_uniforms

)

pivot (

count(*)

for shirtsize in (

regexp_like(shirtsize, '^X+S$') as "< S"

, 'S'  as "S"

, 'M'  as "M"

, 'L'  as "L"

, 'XL' as "XL"

, regexp_like(shirtsize, '^X{2,}L$') as "> XL"

)

)

/

(D)

select "< S", "S", "M", "L", "XL", "> XL"

from (

select regexp_replace(

regexp_replace(

shirtsize, '^X+S$', '< S'

), '^X{2,}L$', '> XL'

) as modifiedsize

from plch_uniforms

)

pivot (

count(*)

for modifiedsize in (

'< S' as "< S"

, 'S'  as "S"

, 'M'  as "M"

, 'L'  as "L"

, 'XL' as "XL"

, '> XL' as "> XL"

)

)

/

(E)

with modifier(shirtsize, modifiedsize) as (

select 'XXXS' , '< S'  from dual union all

select 'XXS'  , '< S'  from dual union all

select 'XS'   , '< S'  from dual union all

select 'S'    , 'S'    from dual union all

select 'M'    , 'M'    from dual union all

select 'L'    , 'L'    from dual union all

select 'XL'   , 'XL'   from dual union all

select 'XXL'  , '> XL' from dual union all

select 'XXXL' , '> XL' from dual union all

select 'XXXXL', '> XL' from dual

)

select "< S", "S", "M", "L", "XL", "> XL"

from (

select modifier.modifiedsize

from plch_uniforms

join modifier

on modifier.shirtsize = plch_uniforms.shirtsize

)

pivot (

count(*)

for modifiedsize in (

'< S' as "< S"

, 'S'  as "S"

, 'M'  as "M"

, 'L'  as "L"

, 'XL' as "XL"

, '> XL' as "> XL"

)

)

/

(F)

select (

select count(*)

from plch_uniforms

where regexp_like(shirtsize, '^X+S$')

) as "< S"

, (

select count(*)

from plch_uniforms

where shirtsize = 'S'

) as "S"

, (

select count(*)

from plch_uniforms

where shirtsize = 'M'

) as "M"

, (

select count(*)

from plch_uniforms

where shirtsize = 'L'

) as "L"

, (

select count(*)

from plch_uniforms

where shirtsize = 'XL'

) as "XL"

, (

select count(*)

from plch_uniforms

where regexp_like(shirtsize, '^X{2,}L$')

) as "> XL"

from dual

/