table1表字段如下
column1(int) column2(date) column3
10 2002-10-01 10:10:10 2
10 2002-10-02 10:10:10 3
10 2002-10-03 10:10:10 4
10 2002-11-01 10:10:10 2
10 2002-11-02 10:10:10 3
10 2002-11-03 10:10:10 4
11 2002-10-01 10:10:10 2
11 2002-10-02 10:10:10 3
11 2002-10-03 10:10:10 4
12 2002-11-01 10:10:10 2
12 2002-11-02 10:10:10 3
12 2002-11-03 10:10:10 4
.
.
.
要求得到这样的查询结果
column1 年 月 d1 d2 d3 d4 d5 d6 ... d31
10 2002 10 2 3 4 0 0 0 ... 0
10 2002 11 2 3 4 0 0 0 ... 0
11 2002 10 2 3 4 0 0 0 ... 0
12 2002 11 2 3 4 0 0 0 ... 0这是我的笨办法:
select column1, ye as 年, mo as 月,
(select column3 from table1 where column1 = t.column1 and extract(year from column2) = t.ye and extract(month from column2) = t.mo and extract(day from column2) = 1) as d1,
(select column3 from table1 where column1 = t.column1 and extract(year from column2) = t.ye and extract(month from column2) = t.mo and extract(day from column2) = 2) as d2,
.
.
.
(select column3 from table1 where column1 = t.column1 and extract(year from column2) = t.ye and extract(month from column2) = t.mo and extract(day from column2) = 31) as d31
from (select column1, extract(year from column2) as ye, extract(month from column2) as mo from table1) t
看看大家是否有更好的解决方法?谢谢...
column1(int) column2(date) column3
10 2002-10-01 10:10:10 2
10 2002-10-02 10:10:10 3
10 2002-10-03 10:10:10 4
10 2002-11-01 10:10:10 2
10 2002-11-02 10:10:10 3
10 2002-11-03 10:10:10 4
11 2002-10-01 10:10:10 2
11 2002-10-02 10:10:10 3
11 2002-10-03 10:10:10 4
12 2002-11-01 10:10:10 2
12 2002-11-02 10:10:10 3
12 2002-11-03 10:10:10 4
.
.
.
要求得到这样的查询结果
column1 年 月 d1 d2 d3 d4 d5 d6 ... d31
10 2002 10 2 3 4 0 0 0 ... 0
10 2002 11 2 3 4 0 0 0 ... 0
11 2002 10 2 3 4 0 0 0 ... 0
12 2002 11 2 3 4 0 0 0 ... 0这是我的笨办法:
select column1, ye as 年, mo as 月,
(select column3 from table1 where column1 = t.column1 and extract(year from column2) = t.ye and extract(month from column2) = t.mo and extract(day from column2) = 1) as d1,
(select column3 from table1 where column1 = t.column1 and extract(year from column2) = t.ye and extract(month from column2) = t.mo and extract(day from column2) = 2) as d2,
.
.
.
(select column3 from table1 where column1 = t.column1 and extract(year from column2) = t.ye and extract(month from column2) = t.mo and extract(day from column2) = 31) as d31
from (select column1, extract(year from column2) as ye, extract(month from column2) as mo from table1) t
看看大家是否有更好的解决方法?谢谢...
sum(decode(dd, 1, column3, 0)) d1,
sum(decode(dd, 2, column3, 0)) d2,
sum(decode(dd, 3, column3, 0)) d3,
.
.
.
sum(decode(dd, 29, column3, 0)) d29,
sum(decode(dd, 30, column3, 0)) d30,
sum(decode(dd, 31, column3, 0)) d31,
FROM table1
group by column1, ye, mo年月日可以用to_char截取出来。