創建交叉報表
create table t1(
goodid number(10) not null,
saledate date not null,
salesum number(10)
);
要求生成本年度每個月的產品銷售狀況表
m1 m2 m3 ... m12
g1
g2
.
.
.
gn
下面是生成報表的sql
select goodid,
sum(decode(to_char(saledate,'mm'),'01',salesum)) "01",
sum(decode(to_char(saledate,'mm'),'02',salesum)) "02",
sum(decode(to_char(saledate,'mm'),'03',salesum)) "03",
sum(decode(to_char(saledate,'mm'),'04',salesum)) "04",
sum(decode(to_char(saledate,'mm'),'05',salesum)) "05",
sum(decode(to_char(saledate,'mm'),'06',salesum)) "06",
sum(decode(to_char(saledate,'mm'),'07',salesum)) "07",
sum(decode(to_char(saledate,'mm'),'08',salesum)) "08",
sum(decode(to_char(saledate,'mm'),'09',salesum)) "09",
sum(decode(to_char(saledate,'mm'),'10',salesum)) "10",
sum(decode(to_char(saledate,'mm'),'11',salesum)) "11",
sum(decode(to_char(saledate,'mm'),'12',salesum)) "12"
from t1
where to_char(saledate,'yyyy') = '2004'
group by goodid
order by goodid;
本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。
