ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 创建交叉报表(转)

创建交叉报表(转)

原创 Linux操作系统 作者:jcszjswkzhou 时间:2019-05-19 22:42:06 0 删除 编辑
创建交叉报表

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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10294527/viewspace-122252/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-08-29

  • 博文量
    2756
  • 访问量
    2018442