Oracle 的棘手视图

Question

我有一个包含以下列的“价格”表：（

year, janprc, janqty, febprc, febqty ...

一年中所有月份的价格和数量）

我需要的是创建一个包含以下列的视图“每月价格”：

year, month, price, quantity 

使用上表中的数据。 我怎么能那样做呢？

谢谢！

Answer 1

以下是如何使用一个 UNPIVOT 语句而不使用 UNION 来完成此操作。

with t as (
  select 2008 year, 1 janprc, 500 janqty, 1 febprc, 600 febqty  from dual
  union
  select 2009,      50,       1000,       20,       3000        from dual
  union
  select 2010,      60,       1000,       25,       3000        from dual
)
SELECT *
FROM   t
UNPIVOT (
  (price, quantity) FOR month IN
  (
    (janprc, janqty) AS 'jan',
    (febprc, febqty) AS 'feb'
  )
)
order by
  year, month
;

Answer 2

这与编写 12 个子查询并将其结果 UNION 组合在一起非常简单：

CREATE VIEW MONTHLYPRICES AS

SELECT
  year       AS year,
  'January'  AS month,
  janprc     AS price,
  janqty     AS quantity
FROM
  PRICES

UNION ALL

SELECT
  year       AS year,
  'February' AS month,
  febprc     AS price,
  febqty     AS quantity
FROM
  PRICES

UNION ALL

SELECT
  year       AS year,
  'March'    AS month,
  marprc     AS price,
  marqty     AS quantity
FROM
  PRICES

UNION ALL

  ... and so on ...

您可以使用 UNION ALL 因为您知道不会有任何重复项。

Answer 3

使用 11 个 UNION 一次构建您想要一个月的表，

with t as (
  select 2008 year, 1 janprc, 1 janqty, 1 febprc, 1 febqty  from dual
  union
  select 2009,      50,       10,       20,       30        from dual
  union
  select 2010,      60,       10,       25,       30        from dual
)
select year, 'jan' month, janprc price, janqty quantity from t
union
select year, 'feb',       febprc,       febqty          from t
;

这假设没有超过一条记录每年。如果每年有多个记录，请使用 UNION ALL 保留重复的行。

Answer 4

您或许可以利用 Oracle 11g 的 UNPIVOT 操作，我没有 11g 实例尽管我很害怕，但我还是在四处进行测试。

Answer 5

工会的做法对我来说看起来有点痛苦。您可以这样做，将真实的表名替换为 so_4164416 并选择您想要表示月份的方式 - 也许不是全名（而且我怀疑无论如何都有更好的方法来生成月份名称！）：

create or replace view monthlyprices as
with tmp_month_num as
    (select rownum as month_num from dual connect by level <= 12)
select so.year,
    trim(to_char(to_date('01/' || tmn.month_num || '/2010','DD/MM/YYYY'),
        'Month')) month,
    case tmn.month_num
        when 01 then so.janprc
        when 02 then so.febprc
        when 03 then so.marprc
        when 04 then so.aprprc
        when 05 then so.mayprc
        when 06 then so.junprc
        when 07 then so.julprc
        when 08 then so.augprc
        when 09 then so.sepprc
        when 10 then so.octprc
        when 11 then so.novprc
        when 12 then so.decprc end as price,
    case tmn.month_num
        when 01 then so.janqty
        when 02 then so.febqty
        when 03 then so.marqty
        when 04 then so.aprqty
        when 05 then so.mayqty
        when 06 then so.junqty
        when 07 then so.julqty
        when 08 then so.augqty
        when 09 then so.sepqty
        when 10 then so.octqty
        when 11 then so.novqty
        when 12 then so.decqty end as quantity
from so_4164416 so, tmp_month_num tmn
order by so.year, tmn.month_num;

select * from monthlyprices where year = 2009 and month = 'January';

Answer 6

如果 UNPIVOT 可用，您绝对应该使用它。对于早期版本的 Oracle，您可以将表与月份名称表（生成的或预构建的）交叉连接，然后使用解码或 case 语句选择正确的月份、价格和数量。这是它的样子。

create table prices (Year Varchar2(4), JanPrc Number(3), JanQty Number(3),
   FebPrc Number(5,2), FebQty Number(3), MarPrc Number(3), MarQty Number(3));
insert into prices values ('2008',1,500,1,600,1,700);
insert into prices values ('2009',50,100,20,300,30,800);
insert into prices values ('2010',60,5,70,10,80,15);

SELECT Year, Month, DECODE(MonthNumber,1,JanPrc,2,FebPrc,MarPrc) Price,
   DECODE(MonthNumber,1,JanQty,2,FebQty,MarQty) Quantity
FROM Prices
  CROSS JOIN (
     SELECT rownum MonthNumber, 
           to_char(to_date(to_char(rownum,'FM00') || '2000','MMYYYY'),
              'FMMonth') Month
        FROM dual CONNECT BY rownum <= 3
  )
ORDER BY Year, MonthNumber;