首页 文章

带静态列的交叉表

提问于
浏览 次
1

如何在交叉表中创建静态列/行？见下面的例子;我可以有一个固定的jan，feb，march，...列而不是动态生成吗？

location       jan   feb   mar   apr   may   jun   jul   aug   sep   oct   nov   dec 
london         500   62    200   50    0     60    100   46    89    200   150   210
paris          50    26    20    500   50    70    40    200   0     40    250   50

我希望列（jan，feb，mar，apr，...）始终显示，无论它们的度量为零还是有值 . 就像他们是固定的 .

这是我正在使用的查询：

select sum("AMOUNT"), "REQUESTDATE","description" 
from(
SELECT SUM(e.AMOUNT)"AMOUNT",TO_CHAR(REQUESTDATE,'MM')"REQUESTDATE", CA.DESCR "description"
FROM PC_PAYMENTTRXNLOG PC,GLB_TYPE ca, PC_ESERVICEINQUIRY e
where PC.ESERVICE_ID = E.ID 
AND trunc(REQUESTDATE) between trunc(to_date('2012-01-01','yyyy-mm-dd')) and trunc(to_date('2012-06-30','yyyy-mm-dd'))
GROUP BY TO_CHAR(REQUESTDATE,'MM'),CA.DESCR 
 )
group by "REQUESTDATE","description"

和输出

SUM("amount") Requestdate    Description
    2550405         04           A
    2550405         04           B
    23893281        05           C
    614977          06           A
    614977          06           E
    2550405         04           C

现在更新后的查询

select sum("AMOUNT"), month,"description" 
  from(
  SELECT SUM(e.AMOUNT)"AMOUNT",TO_CHAR(REQUESTDATE,'MM')"REQUESTDATE", CA.DESCR "description"
  FROM PC_PAYMENTTRXNLOG PC,GLB_TYPE ca, PC_ESERVICEINQUIRY e
  where PC.ESERVICE_ID = E.ID 
  AND trunc(REQUESTDATE) between trunc(to_date('2012-01-01','yyyy-mm-dd')) and trunc(to_date('2012-06-30','yyyy-mm-dd'))
  GROUP BY TO_CHAR(REQUESTDATE,'MM'),CA.DESCR 
   ) 
 full outer join (select to_char(date '1970-01-01'
+ numtoyminterval(level - 1, 'month'), 'mm') as month
  from dual
  connect by level <= 12) on month="REQUESTDATE"
  group by month,"description"

当运行查询时，它会显示所有月份，无论它们的度量为零还是有值 .

但现在输出就是这样

location       jan   feb   mar   apr   may   jun   jul   aug   sep   oct   nov   dec 
london         500   62    200   50    0     60    100   46    89    200   150   210
paris          50    26    20    500   50    70    40    200   0     40    250   50
null            0     0     0     0     0     0     0     0    0      0     0     0

我怎么能限制/隐藏最后一个空行？

sql oracle jasper-reports crosstab

1 回答

  • 1

    没有测试过..但尝试这样的事情

    select sum("AMOUNT"), month,"description" 
from(SELECT SUM(e.AMOUNT)"AMOUNT",TO_CHAR(REQUESTDATE,'MM')"REQUESTDATE", CA.DESCR "description"
FROM PC_PAYMENTTRXNLOG PC,GLB_TYPE ca, PC_ESERVICEINQUIRY e
where PC.ESERVICE_ID = E.ID 
AND trunc(REQUESTDATE) between trunc(to_date('2012-01-01','yyyy-mm-dd')) and trunc(to_date('2012-06-30','yyyy-mm-dd'))
GROUP BY TO_CHAR(REQUESTDATE,'MM'),CA.DESCR 
 ) 
full outer join (select to_char(date '1970-01-01'
+ numtoyminterval(level - 1, 'month'), 'mm') as month
from dual
connect by level <= 12) on month="REQUESTDATE"
group by month,"description"

    单击此处获取SQL Fiddle演示以在Oracle中生成1到12

    一旦你生成了这个..全外连接你的主查询与这个系列查询并从系列查询中取月份，就像我在主查询中所做的那样 .

    使用此查询，您将获得所有月份的所有数据，其中包含测量值的空值 .

    对于 Description 列 - iReport set属性的isRemoveLineWhenBlank和isBlankWhenNull为True，这将删除在iReport中打印的空值

    对于 Measure 以这种方式使用Print when expression，当描述为null时返回false . 因此，这将阻止在iReport中打印 0 值 .

    回复于

相关问题