詢問 CASE WHEN 的時候、GROUP BY 搞糊塗了

group by

mike51620 2023-08-02 17:21:09 ‧ 878 瀏覽

分享至

(圖1)
上區塊搜尋為主檔、下區塊為歷史檔
主檔綠色銀光為未異動、其餘白底皆是歷史檔異動過才新增置主檔的(等於主檔有2筆)

小弟我想查詢該日期有異動過帳號的TXNT_NO 給撈出來
想呈現如下 (圖2)

可是下SQL 時卻卡住了 = =

             SELECT 
             CAST(CMMAPH.TXNT_DATE AS CHAR(8)) AS TXNT_DATE,
             CAST(CMMAPH.TXNT_NO AS CHAR(16)) AS TXNT_NO,
             CAST(CASE WHEN CMMAPH.TXNT_NO = CMMAPM.TXNT_NO AND CMMAPH.LOAN_KIND = CMMAPM.LOAN_KIND THEN CMMAPH.CRD_ACNO  END AS CHAR(14)) AS CRD_ACNO_NEW,
             CAST(CASE WHEN CMMAPH.TXNT_NO = CMMAPM.TXNT_NO AND CMMAPH.LOAN_KIND <> CMMAPM.LOAN_KIND THEN CMMAPM.CRD_ACNO  END AS CHAR(14)) AS CRD_ACNO_OLD
			 FROM CMMAPH CMMAPH
             JOIN WPSRPM WPSRPM ON WPSRPM.MESG_OUT = 'O' AND WPSRPM.BSN_DATE = CMMAPH.TXNT_DATE 
             JOIN CMMAPM CMMAPM ON CMMAPM.TXNT_NO = CMMAPH.TXNT_NO

試著 GROUP BY TXNT_NO 就會卡死
想撈出有更新過 CRD_ACNO 的 TXNT_NO 一行列出來，但變2行這樣...

想問問大大我該怎麼改會比較好，謝謝

aaaa0976355849 iT邦新手 5 級 ‧ 2023-08-02 17:36:03 檢舉

@@用TXNT_NO 來 JOIN A B不能嗎? 條件下日期

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

純真的人

iT邦大師 1 級 ‧ 2023-08-02 23:28:36

試試@@...

select TXNT_DATE
,TXNT_NO
,max(CRD_ACNO_NEW) CRD_ACNO_NEW
,max(CRD_ACNO_OLD) CRD_ACNO_OLD
from (
	SELECT 
	CAST(CMMAPH.TXNT_DATE AS CHAR(8)) AS TXNT_DATE,
	CAST(CMMAPH.TXNT_NO AS CHAR(16)) AS TXNT_NO,
	CAST(CASE WHEN CMMAPH.TXNT_NO = CMMAPM.TXNT_NO AND CMMAPH.LOAN_KIND = CMMAPM.LOAN_KIND THEN CMMAPH.CRD_ACNO  END AS CHAR(14)) AS CRD_ACNO_NEW,
	CAST(CASE WHEN CMMAPH.TXNT_NO = CMMAPM.TXNT_NO AND CMMAPH.LOAN_KIND <> CMMAPM.LOAN_KIND THEN CMMAPM.CRD_ACNO  END AS CHAR(14)) AS CRD_ACNO_OLD
	FROM CMMAPH CMMAPH
	JOIN WPSRPM WPSRPM ON WPSRPM.MESG_OUT = 'O' AND WPSRPM.BSN_DATE = CMMAPH.TXNT_DATE 
	JOIN CMMAPM CMMAPM ON CMMAPM.TXNT_NO = CMMAPH.TXNT_NO 
) k
group by TXNT_DATE
,TXNT_NO

回應
分享
檢舉

登入發表回應

shing_pascal

iT邦新手 2 級 ‧ 2023-08-04 10:13:58

你的問題應該是無法用 group解決。
因為2個欄位各有一個null ，無法用條件判斷。
只能先取前面三個欄位做成一個table，過濾null值。
再用這個table去 LEFT JOIN ，取後第四個值，並過濾 null 值
是 MS SQL嗎？
可以試試以下程式，概念上應該不問題。可能實際執行時，要小修一下。
重點是程式中的 where 條件式

WITH CMMAPH_LIST AS (
SELECT CMMAPH.TXNT_DATE AS TXNT_DATE _CHECK ,
CMMAPH.TXNT_NO AS CMMAPH.TXNT_NO_CHECK,
CAST(CMMAPH.TXNT_DATE AS CHAR(8)) AS TXNT_DATE,
CAST(CMMAPH.TXNT_NO AS CHAR(16)) AS TXNT_NO,
CAST(CASE
WHEN CMMAPH.TXNT_NO = CMMAPM.TXNT_NO AND CMMAPH.LOAN_KIND = CMMAPM.LOAN_KIND THEN CMMAPH.CRD_ACNO
END AS CHAR(14)) AS CRD_ACNO_NEW
FROM CMMAPH CMMAPH
JOIN WPSRPM WPSRPM ON WPSRPM.MESG_OUT = 'O' AND WPSRPM.BSN_DATE = CMMAPH.TXNT_DATE
JOIN CMMAPM CMMAPM ON CMMAPM.TXNT_NO = CMMAPH.TXNT_NO
WHERE CMMAPH.CRD_ACNO IS NOT NULL
)
SELECT TXNT_DATE AS TXNT_DATE ,
TXNT_NO AS TXNT_NO ,
CRD_ACNO_NEW AS CRD_ACNO_NEW ,
CAST(CASE WHEN CMMAPH.TXNT_NO = CMMAPM.TXNT_NO AND CMMAPH.LOAN_KIND <> CMMAPM.LOAN_KIND THEN CMMAPM.CRD_ACNO END AS CHAR(14)) AS CRD_ACNO_OLD
FROM CMMAPH_LIST
JOIN WPSRPM WPSRPM ON WPSRPM.MESG_OUT = 'O' AND WPSRPM.BSN_DATE = TXNT_DATE_CHECK
JOIN CMMAPM CMMAPM ON CMMAPM.TXNT_NO = TXNT_NO_CHECK
WHERE CMMAPM.CRD_ACNO IS NOT NULL