LH_CODE_TB: LH_SUM_TB:ID CODE YEAR_MONTH CODE COST
------------------- ------------------------------
1 001 200801 001 1,000
2 002 200802 002 500
3 003 200805 001 3,000
200801 002 362
现要得到如下结果:
CODE 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
----------------------------------------------------------------------------------------------------
001 1000 0 0 0 3000 0 0 0 0 0 0 0
002 362 500 0 0 0 0 0 0 0 0 0 0
003 0 0 0 0 0 0 0 0 0 0 0 0
------------------- ------------------------------
1 001 200801 001 1,000
2 002 200802 002 500
3 003 200805 001 3,000
200801 002 362
现要得到如下结果:
CODE 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
----------------------------------------------------------------------------------------------------
001 1000 0 0 0 3000 0 0 0 0 0 0 0
002 362 500 0 0 0 0 0 0 0 0 0 0
003 0 0 0 0 0 0 0 0 0 0 0 0
解决方案 »
- oracle developer连接oracle数据库出现the network adapter cannot establish the connection
- oracle 定期删除数据并归档
- Oracle 写文件总是出错
- oracle 自定义函数小問題﹗﹗﹗
- 考考大家,怎么把下面的ms sql存储过程转换为对应的oracle里面的表达
- 关于oracle读取远程文件服务器中的csv文件的问题。。。
- 高分求form6i的资料,
- 用什么类型的字段,varchar2不够长
- toad中如何设置成自动提交语句呢?如何设置rollback区域呢
- ora92 TNSListener 不能成功启动
- 简单问题帮忙看看!
- ORA-01858错误
ID CODE YEAR_MONTH CODE COST
------------------- ------------------------------
1 001 200801 001 1,000
2 002 200802 002 500
3 003 200805 001 3000
200801 002 362
现要得到如下结果:
CODE 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
----------------------------------------------------------------------------------------------------
001 1000 0 0 0 3000 0 0 0 0 0 0 0
002 362 500 0 0 0 0 0 0 0 0 0 0
003 0 0 0 0 0 0 0 0 0 0 0 0(排版有问题重新整理一下)
SELECT A.CODE,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200801'),0) AS M1,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200802'),0) AS M2,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200803'),0) AS M3,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200804'),0) AS M4,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200805'),0) AS M5,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200806'),0) AS M6,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200807'),0) AS M7,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200808'),0) AS M8,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200809'),0) AS M9,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200810'),0) AS M10,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200811'),0) AS M11,
NVL((SELECT B.COST FROM LH_SUM_TB B WHERE B.CODE = A.CODE AND B.YEAR_MONTH ='200812'),0) AS M12
FROM LH_CODE_TB A这是我写的。
不知道有没有更好的,简洁的
SUM(DECODE(B.YEAR_MONTH,'200801',1,0)) AS M1,
SUM(DECODE(B.YEAR_MONTH,'200802',1,0)) AS M2,
.....
SUM(DECODE(B.YEAR_MONTH,'200812',1,0)) AS M12
FROM LH_CODE_TB A INNER JOIN LH_SUM_TB B ON A.CODE=B.CODE
GROUP BY A.CODE;
SQL codeSELECT A.CODE,
SUM(DECODE(B.YEAR_MONTH,'200801',1,0)) AS M1,
SUM(DECODE(B.YEAR_MONTH,'200802',1,0)) AS M2,
.....
SUM(DECODE(B.YEAR_MONTH,'200812',1,0)) AS M12
FROM LH_CODE_TB A LEFT JOIN LH_SUM_TB B ON A.CODE=B.CODE
GROUP BY A.CODE;
那就换成LEFT JOIN
SUM(DECODE(B.YEAR_MONTH,'200801',B.CODE,0)) AS M1,
SUM(DECODE(B.YEAR_MONTH,'200802',B.CODE,0)) AS M2,
.....
SUM(DECODE(B.YEAR_MONTH,'200812',B.CODE,0)) AS M12
FROM LH_CODE_TB A LEFT JOIN LH_SUM_TB B ON A.CODE=B.CODE
GROUP BY A.CODE;