创建交叉报表
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;
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/
