我有一個型別為 1、2、3、4、5、6、7 以及許多其他列的不同事務 ID 的資料集
我正在嘗試做的是創建不同的場景，例如
- 僅包含交易 1
- 包含 1,5 和 7
我從一個名為 ALL_CONTRACTS 的 CTE 開始，其中包含型別為 1、2、3、4、5、6、7 的事務 ID
WITH CONTRACTS AS (
SELECT
CONT.AGREEMENT_ID,
CONT.ACCOUNT_NUMBER,
TRAN.TRAN_ID
FROM CONTRACTS CONT
JOIN TRANSACTIONS TRAN
ON CONT.AGREEMENT_ID = TRAN.AGREEMENT_ID
WHERE TRAN.TRAN_ID IN (1,2,3,4,5,6,7)
)
SELECT
CT1.AGREEMENT_ID
,CT1.ACCOUNT_NUMBER
FROM CONTRACTS CT1
WHERE CT1.TRAN_ID IN ('1') AND CT1.TRAN_ID NOT IN (2,3,4,5,6,7)
正在發生的事情是其他包含 2,3,4,5,6,7 交易的合約正在顯示，因為它們也包含 1
那么我如何只提取只有 1 而沒有其他的合同呢？
uj5u.com熱心網友回復：
假設您正在嘗試查找TRAN_ID每個AGREEMENT_ID/ACCOUNT_NUMBER對，那么您可以使用：
SELECT AGREEMENT_ID,
ACCOUNT_NUMBER,
TRAN_ID
FROM (
SELECT c.AGREEMENT_ID,
c.ACCOUNT_NUMBER,
t.TRAN_ID,
COUNT(
CASE WHEN t.TRAN_ID IN (2,3,4,5,6,7) THEN 1 END
) OVER (PARTITION BY c.AGREEMENT_ID, c.ACCOUNT_NUMBER)
AS num_other_tarnsactions
FROM CONTRACTS c
INNER JOIN TRANSACTIONS t
ON (c.AGREEMENT_ID = t.AGREEMENT_ID)
WHERE t.TRAN_ID IN (1,2,3,4,5,6,7)
)
WHERE tran_id = 1
AND num_other_tarnsactions = 0;
其中，對于樣本資料：
CREATE TABLE contracts (agreement_id, account_number) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 5, 5 FROM DUAL;
CREATE TABLE transactions (agreement_id, tran_id) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 3 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 3, 4 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL UNION ALL
SELECT 3, 6 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 4, 2 FROM DUAL UNION ALL
SELECT 4, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 4, 5 FROM DUAL UNION ALL
SELECT 4, 6 FROM DUAL UNION ALL
SELECT 4, 7 FROM DUAL UNION ALL
SELECT 5, 1 FROM DUAL UNION ALL
SELECT 5, 1 FROM DUAL;
輸出：
AGREEMENT_ID 帳號 TRAN_ID 1 1 1 5 5 1 5 5 1
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/457332.html
上一篇：如果自上次符合條件的條目以來已過去5天或更長時間，則將輸出設定為1
下一篇：誰能幫我解決這個錯誤？