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
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;