我用pl/sql,数据库:oracle10g在一张表里有 storecode 店铺号,storedata 店铺营业额,txdate 日期怎么通过sql查询,能得到如下效果店铺号 5/5/2012 6/5/2012 7/5/2012 8/5/2012 9/5/2012 10/5/2012 总计
CN01 2,800.00 2,900.00 3,000.00 3,100.00 3,200.00 3,300.00 18,300.00
CN02 2,900.00 3,000.00 3,100.00 3,200.00 3,300.00 3,400.00 18,900.00
CN03 3,000.00 3,100.00 3,200.00 3,300.00 3,400.00 3,500.00 19,500.00
CN04 3,100.00 3,200.00 3,300.00 3,400.00 3,500.00 3,600.00 20,100.00
CN05 3,200.00 3,300.00 3,400.00 3,500.00 3,600.00 3,700.00 20,700.00
CN06 3,300.00 3,400.00 3,500.00 3,600.00 3,700.00 3,800.00 21,300.00
CN07 3,400.00 3,500.00 3,600.00 3,700.00 3,800.00 3,900.00 21,900.00
CN08 3,500.00 3,600.00 3,700.00 3,800.00 3,900.00 4,000.00 22,500.00
总计 25,200.00 26,000.00 26,800.00 27,600.00 28,400.00 29,200.00 163,200.00
CN01 2,800.00 2,900.00 3,000.00 3,100.00 3,200.00 3,300.00 18,300.00
CN02 2,900.00 3,000.00 3,100.00 3,200.00 3,300.00 3,400.00 18,900.00
CN03 3,000.00 3,100.00 3,200.00 3,300.00 3,400.00 3,500.00 19,500.00
CN04 3,100.00 3,200.00 3,300.00 3,400.00 3,500.00 3,600.00 20,100.00
CN05 3,200.00 3,300.00 3,400.00 3,500.00 3,600.00 3,700.00 20,700.00
CN06 3,300.00 3,400.00 3,500.00 3,600.00 3,700.00 3,800.00 21,300.00
CN07 3,400.00 3,500.00 3,600.00 3,700.00 3,800.00 3,900.00 21,900.00
CN08 3,500.00 3,600.00 3,700.00 3,800.00 3,900.00 4,000.00 22,500.00
总计 25,200.00 26,000.00 26,800.00 27,600.00 28,400.00 29,200.00 163,200.00
nvl(storecode,'总计'),
sum(case when txdate='5/5/2012' then storedata else 0 end ) as '5/5/2012',
sum(case when txdate='6/5/2012' then storedata else 0 end ) as '6/5/2012',
sum(case when txdate='7/5/2012' then storedata else 0 end ) as '7/5/2012',
sum(case when txdate='8/5/2012' then storedata else 0 end ) as '8/5/2012',
sum(case when txdate='9/5/2012' then storedata else 0 end ) as '9/5/2012',
sum(case when txdate='10/5/2012' then storedata else 0 end ) as '10/5/2012',
sum(storedata ) 总计
from 表名
GROUP BY GROUPING SETS((storecode),())
然后根据时间参数,来循环拼接
sum(case when txdate='5/5/2012' then storedata else 0 end ) as '5/5/2012',
这个
,
加上 where 时间 >= and 时间 < 这个条件
然后利用动态语句执行就可以了,
GROUPING SETS((storecode),()) 这句什么意思呢?