小弟做报表统计的时候遇到个问题
有table1 table2 table3 三张表,表结构一样,有time area amount 三个字段且都是varchar型table1:
time area amount
20110501 北京 100
20110501 上海 111
20110502 北京 100
20110502 上海 111
20110503 北京 100
20110503 上海 111
20110504 北京 100
20110504 上海 111table2:
time area amount
20110501 北京 200
20110501 上海 222
20110502 北京 200
20110502 上海 222
20110503 北京 200
20110503 上海 222
20110504 北京 200
20110504 上海 222table3:
time area amount
20110501 北京 300
20110501 上海 333
20110502 北京 300
20110502 上海 333
20110503 北京 300
20110503 上海 333
20110504 北京 300
20110504 上海 333现在我想实现查询
time area table1.amount table2.amount table3.amount
20110501~20110503 北京 300 600 900
20110501~20110503 上海 333 666 999以后还可能有更多的表,所以希望查询能适应,求高手指点...操作系统 WIN7旗舰版7601
oracle版本 11G R2
有table1 table2 table3 三张表,表结构一样,有time area amount 三个字段且都是varchar型table1:
time area amount
20110501 北京 100
20110501 上海 111
20110502 北京 100
20110502 上海 111
20110503 北京 100
20110503 上海 111
20110504 北京 100
20110504 上海 111table2:
time area amount
20110501 北京 200
20110501 上海 222
20110502 北京 200
20110502 上海 222
20110503 北京 200
20110503 上海 222
20110504 北京 200
20110504 上海 222table3:
time area amount
20110501 北京 300
20110501 上海 333
20110502 北京 300
20110502 上海 333
20110503 北京 300
20110503 上海 333
20110504 北京 300
20110504 上海 333现在我想实现查询
time area table1.amount table2.amount table3.amount
20110501~20110503 北京 300 600 900
20110501~20110503 上海 333 666 999以后还可能有更多的表,所以希望查询能适应,求高手指点...操作系统 WIN7旗舰版7601
oracle版本 11G R2
解决方案 »
- 有没有oracle性能优化方面的好数据介绍啊!!!!!!!!
- 如何理解oracle执行sql语句的过程!
- oracle某表的一个字段有多值,怎么把这值变成多行,其它数据为一样
- 什么时候会用到动态sql语句,大家能举个例子吗?
- 各位高手,寻找object browser软件
- to_char(YQZCSJ,'mm/dd/hh') 可以 为什么 to_char(YQZCSJ,'mm月dd日hh时') 却不行? 有什么方法解决?
- 数据库间的转换问题
- oracle9i安装成功后,运行总是提示“没有监听器”?
- oracle Intelligent Agent 在linux上启动不了
- oracle9.2.0在AIX5L上问题--急需!!
- windows server 2008 R2 x64(64位)系统下安装Oracle10g,安装窗口消失
- 如何分析2个用户下的对象的差异?
2、如果非要这样做,可以使用动态拼接SQL。
WITH base_table AS(
SELECT TIME,AREA,AMOUNT,1 type_id FROM table1
UNION ALL SELECT TIME,AREA,AMOUNT,2 FROM table2
UNION ALL SELECT TIME,AREA,AMOUNT,3 FROM table3
)SELECT min_time||'~'||max_time data_time,area,
sum(decode(type_id,1,AMOUNT,0)) AMOUNT1,
sum(decode(type_id,2,AMOUNT,0)) AMOUNT2,
sum(decode(type_id,3,AMOUNT,0)) AMOUNT3 FROM
(SELECT MIN(TIME) min_time, max(TIME) max_time,area,SUM(AMOUNT) AMOUNT,type_id FROM base_table
GROUP BY area,type_id)
GROUP BY min_time,max_time,area;
我自己改改实现了功能,这里先多谢5楼lizhuxin2008的指教。以下是我改后的SQLWITH base_table AS(
SELECT TIME,AREA,AMOUNT,1 type_id FROM table1 WHERE TIME between '20110501' and '20110503'
UNION ALL SELECT TIME,AREA,AMOUNT,2 FROM table2 WHERE TIME between '20110501' and '20110503'
UNION ALL SELECT TIME,AREA,AMOUNT,3 FROM table3 WHERE TIME between '20110501' and '20110503'
)SELECT '20110501' || '~' || '20110503' data_time,
area,
sum(decode(type_id, 1, AMOUNT, 0)) AMOUNT1,
sum(decode(type_id, 2, AMOUNT, 0)) AMOUNT2,
sum(decode(type_id, 3, AMOUNT, 0)) AMOUNT3
FROM (SELECT '20110501' min_time,
'20110503' max_time,
area,
SUM(AMOUNT) AMOUNT,
type_id
FROM base_table
GROUP BY area, type_id)
GROUP BY min_time, max_time, area;这是在程序里拼接的String startTime = "20110501"; //Model.getStartTime();
String endTime = "20110503"; //Model.getEndTime();
String sql = "WITH base_table AS( "
+ "SELECT TIME,AREA,AMOUNT,1 type_id FROM table1 WHERE TIME between '" + startTime + "' and '" + endTime + "' "
+ "UNION ALL SELECT TIME,AREA,AMOUNT,2 FROM table2 WHERE TIME between '" + startTime + "' and '" + endTime + "' "
+ "UNION ALL SELECT TIME,AREA,AMOUNT,3 FROM table3 WHERE TIME between '" + startTime + "' and '" + endTime + "' ) "
+ "\r\n "
+ "SELECT '" + startTime + "' || '~' || '" + endTime + "' data_time, "
+ "area, "
+ "sum(decode(type_id, 1, AMOUNT, 0)) AMOUNT1, "
+ "sum(decode(type_id, 2, AMOUNT, 0)) AMOUNT2, "
+ "sum(decode(type_id, 3, AMOUNT, 0)) AMOUNT3 "
+ "FROM (SELECT '" + startTime + "' min_time, "
+ "'" + endTime + "' max_time, "
+ "area, "
+ "SUM(AMOUNT) AMOUNT, "
+ "type_id "
+ "FROM base_table "
+ "GROUP BY area, type_id) "
+ "GROUP BY min_time, max_time, area ";