oracle使用LEFT JOIN关联产生的问题在查询结果中使用CASE WHEN 无法判断

oracle使用LEFT JOIN关联产生的问题在查询结果中使用CASE WHEN 无法判断

查询方式一:

 1 SELECT 
 2 CASE WHEN (SELECT CAST(SUM(CASE
 3 WHEN (ALLOCABLE_PRIME_CURRENCY_VALUE IS NULL AND STATE_IND = 1) THEN
 4 NVL(PRIME_CURRENCY_VALUE, 0)
 5 ELSE
 6 NVL(ALLOCABLE_PRIME_CURRENCY_VALUE, 0) END) AS NUMBER(18,6)) AS ALLOCABLE_PRIME_CURRENCY_VALUE
 7 FROM RP_LEDGER_ITEM R
 8 WHERE R.SOURCE_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
 9 AND SOURCE_TYPE = 'CASH') = 0 THEN 0 ELSE 1 END,
10 (SELECT SUM(CASE
11 WHEN (ALLOCABLE_PRIME_CURRENCY_VALUE IS NULL AND STATE_IND = 1) THEN
12 NVL(PRIME_CURRENCY_VALUE, 0)
13 ELSE
14 NVL(ALLOCABLE_PRIME_CURRENCY_VALUE, 0) END) AS ALLOCABLE_PRIME_CURRENCY_VALUE
15 FROM RP_LEDGER_ITEM R
16 WHERE R.SOURCE_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
17 AND SOURCE_TYPE = 'CASH'),
18 RP_LEDGER_ITEM.LEDGER_ITEM_ID 
19 FROM RP_CASH_MOVEMENT
20 LEFT JOIN RP_LEDGER_ITEM
21 ON RP_LEDGER_ITEM.CASH_MOVEMENT_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
22 AND RP_LEDGER_ITEM.CASH_MOVEMENT_ID IS NOT NULL
23 WHERE NVL(RP_CASH_MOVEMENT.IS_RESERVE_FUND, '0') = '0'
24 AND 1 = 1
25 AND RP_CASH_MOVEMENT.RP_ID = 'R'
26 AND RP_CASH_MOVEMENT.INPUT_DATE >=
27 TO_DATE('2014/2/1 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
28 AND (RP_CASH_MOVEMENT.OFFICE_ID = '0B4B12XOG33MO')

查询结果如下:

 

查询方式二:

SELECT 
CASE WHEN (SELECT CAST(SUM(CASE
WHEN (ALLOCABLE_PRIME_CURRENCY_VALUE IS NULL AND STATE_IND = 1) THEN
NVL(PRIME_CURRENCY_VALUE, 0)
ELSE
NVL(ALLOCABLE_PRIME_CURRENCY_VALUE, 0) END) AS NUMBER(18,6)) AS ALLOCABLE_PRIME_CURRENCY_VALUE
FROM RP_LEDGER_ITEM R
WHERE R.SOURCE_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
AND SOURCE_TYPE = 'CASH') = 0 THEN 0 ELSE 1 END,
(SELECT SUM(CASE
WHEN (ALLOCABLE_PRIME_CURRENCY_VALUE IS NULL AND STATE_IND = 1) THEN
NVL(PRIME_CURRENCY_VALUE, 0)
ELSE
NVL(ALLOCABLE_PRIME_CURRENCY_VALUE, 0) END) AS ALLOCABLE_PRIME_CURRENCY_VALUE
FROM RP_LEDGER_ITEM R
WHERE R.SOURCE_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
AND SOURCE_TYPE = 'CASH'),
'' LEDGER_ITEM_ID 
FROM RP_CASH_MOVEMENT
LEFT JOIN RP_LEDGER_ITEM
ON RP_LEDGER_ITEM.CASH_MOVEMENT_ID = RP_CASH_MOVEMENT.CASH_MOVEMENT_ID
AND RP_LEDGER_ITEM.CASH_MOVEMENT_ID IS NOT NULL
WHERE NVL(RP_CASH_MOVEMENT.IS_RESERVE_FUND, '0') = '0'
AND 1 = 1
AND RP_CASH_MOVEMENT.RP_ID = 'R'
AND RP_CASH_MOVEMENT.INPUT_DATE >=
TO_DATE('2014/2/1 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND (RP_CASH_MOVEMENT.OFFICE_ID = '0B4B12XOG33MO')

查询结果如图:

2条sql语句最大的区别在于

有些可能表达不清楚,

主要原因我也不知道,如果谁有兴趣可以回复我!

转载于:https://www.cnblogs.com/xzxy/p/3732402.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值