Oracle中如何从单列中选择数据并在多列中显示结果？

Question

我的桌子；

Date       |  Cost

01.01.2010 |  100

02.01.2010 |  200

03.01.2010 |  300

04.01.2010 |  400

10.01.2010 |  800

11.01.2010 |  800

12.01.2010 |  800

25.01.2010 |  500

26.01.2010 |  500

05.02.2010 |  600

13.02.2010 |  700

15.02.2010 |  700

我想制作“'01.01.2010'和'28.02.2010'之间的日期”每周视图

Week 1 | Week 2 | week 3 | week . .. . 

1000   | 2400   |    0   | 32432.... . . 

如何制作请帮忙谢谢？

Answer 1

SQL> create table mytable (the_date,cost)
  2  as
  3  select date '2010-01-01', 100 from dual union all
  4  select date '2010-01-02', 200 from dual union all
  5  select date '2010-01-03', 300 from dual union all
  6  select date '2010-01-04', 400 from dual union all
  7  select date '2010-01-10', 800 from dual union all
  8  select date '2010-01-11', 800 from dual union all
  9  select date '2010-01-12', 800 from dual union all
 10  select date '2010-01-25', 500 from dual union all
 11  select date '2010-01-26', 500 from dual union all
 12  select date '2010-02-05', 600 from dual union all
 13  select date '2010-02-13', 700 from dual union all
 14  select date '2010-02-15', 700 from dual
 15  /

Table created.

此查询使用 MAX-DECODE 作为标准主元技术。如果您使用的是版本 11，还可以使用 PIVOT 运算符。以下版本适用于任何版本。

SQL> select nvl(max(decode(the_week,'01',cost)),0) "Week 1"
  2       , nvl(max(decode(the_week,'02',cost)),0) "Week 2"
  3       , nvl(max(decode(the_week,'03',cost)),0) "Week 3"
  4       , nvl(max(decode(the_week,'04',cost)),0) "Week 4"
  5       , nvl(max(decode(the_week,'05',cost)),0) "Week 5"
  6       , nvl(max(decode(the_week,'06',cost)),0) "Week 6"
  7       , nvl(max(decode(the_week,'07',cost)),0) "Week 7"
  8       , nvl(max(decode(the_week,'08',cost)),0) "Week 8"
  9       , nvl(max(decode(the_week,'09',cost)),0) "Week 9"
 10    from ( select to_char(the_date,'ww') the_week
 11                , sum(cost) cost
 12             from mytable
 13            where the_date between date '2010-01-01' and date '2010-02-28'
 14            group by to_char(the_date,'ww')
 15         )
 16  /

    Week 1     Week 2     Week 3     Week 4     Week 5     Week 6     Week 7     Week 8     Week 9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      1000       2400          0       1000          0        600       1400          0          0

1 row selected.

问候，
抢。

Answer 2

select to_char(date, 'ww'), sum(cost)
from table
group by to_char(date, 'ww');

或者类似的东西应该按周计算总和，并在结果中包含周数。 链接到 Oracle 11g to_char 语法和链接来格式化值。如果这不起作用并且您不需要周数 trunc(date, 'DAY') 可能就是您正在寻找的。

Answer 3

这不是优雅的解决方案，但它的作品......

   SELECT SUM(Week1) Week1, SUM(Week2) Week2 ... SUM(Week36) Week36,
   SUM(Week36) Week37
   FROM   (SELECT DECODE(WeekNo, 1, Cost, 0) Week1,
            DECODE(WeekNo, 2, Cost, 0) Week2,
            ...
            DECODE(WeekNo, 36, Cost, 0) Week36,
            DECODE(WeekNo, 37, Cost, 0) Week37
     FROM   (SELECT to_char(DateFrom, 'IW') WeekNo, SUM(cost) Cost
              FROM   (SELECT trunc(SYSDATE) + LEVEL - 1 DateFrom,
                              LEVEL * 100 Cost
                       FROM   dual
                       CONNECT BY LEVEL < 40)
              GROUP  BY to_char(DateFrom, 'IW')))