如何在SQL中從多個表中查詢不匹配的資料

輸出應該是這樣的

如何比較具有相同 key1 但不同 key2 的兩行，其中 key2 的 val 相同。

我嘗試按查詢進行分組，但由于此結果是從視圖和 3 個不同的表中呈現的，這對我來說很難理解。任何幫助將不勝感激

添加：其中 key1 與 key2 相同的 val 需要排除，但是當 key1 與 key2 相同時，需要顯示為結果

我的查詢：

select  ta1.orig_deal_advice_id,  ta1.ISSUE_TRANCHE_ID, t4.ACTIVE, 
COUNT (*) cnt,
t2.ORIGINATOR_ID ,t3.name ,t4.MULTI_TRANCHE_FLAG,v1.SOURCE_OF_DEAL,ta1.PRICING_DATE 
      from vw_origination_deal_advice ta1   left join vw_orig_deal_advice_summary v1 on v1.deal_id=ta1.orig_deal_advice_id
    left join tbl_issue_tranche t4 on t4.id=ta1.ISSUE_TRANCHE_ID
    left join tbl_issue_originator t2 on ta1.issue_tranche_id=t2.ISSUE_TRANCHE_ID
    left join tbl_staff t3 on t2.originator_id=t3.staff_id    
     where ta1.ISSUE_TRANCHE_ID in
        (select  ta2.ISSUE_TRANCHE_ID from vw_origination_deal_advice ta2 group by ta2. ISSUE_TRANCHE_ID ) AND  ta1.PRICING_DATE Like '%-21%' AND t4.MULTI_TRANCHE_FLAG=1
 GRoup by  ta1.orig_deal_advice_id,  ta1.ISSUE_TRANCHE_ID,  t4.ACTIVE,
  t2.ORIGINATOR_ID ,t3.name ,t4.MULTI_TRANCHE_FLAG,v1.SOURCE_OF_DEAL,ta1.PRICING_DATE;

uj5u.com熱心網友回復：

對我來說，它看起來像

SQL> WITH
  2     test (key1, key2, val)
  3     AS
  4        -- sample data
  5        (SELECT '23', 215, 4489 FROM DUAL
  6         UNION ALL
  7         SELECT '23', 216, 4489 FROM DUAL
  8         UNION ALL
  9         SELECT '86', 326, 5245 FROM DUAL
 10         UNION ALL
 11         SELECT '86', 325, 4489 FROM DUAL
 12         UNION ALL
 13         SELECT '86', 323, 4489 FROM DUAL
 14         UNION ALL
 15         SELECT '04', 369, 1200 FROM DUAL
 16         UNION ALL
 17         SELECT '04', 370, 1673 FROM DUAL
 18         UNION ALL
 19         SELECT '04', 368, 4489 FROM DUAL
 20         UNION ALL
 21         SELECT '10', 402, 1673 FROM DUAL
 22         UNION ALL
 23         SELECT '10', 400, 5971 FROM DUAL
 24         UNION ALL
 25         SELECT '10', 404, 1200 FROM DUAL
 26         UNION ALL
 27         SELECT '10', 401, 9189 FROM DUAL),

 28     temp
 29     AS
 30        -- count > 1 means that there are values that match
 31        (  SELECT key1, val, COUNT (*) cnt
 32             FROM test
 33         GROUP BY key1, val)
 34    SELECT a.key1, a.key2, a.val
 35      FROM test a
 36     WHERE (a.key1, a.val) IN (SELECT b.key1, b.val
 37                                 FROM temp b
 38                                WHERE b.cnt = 1)     --> no match
 39  ORDER BY a.key1, a.key2;

KEY1           KEY2        VAL
-------- ---------- ----------
04              368       4489
04              369       1200
04              370       1673
10              400       5971
10              401       9189
10              402       1673
10              404       1200
86              326       5245

8 rows selected.

SQL>

如果示例資料來自您已經撰寫的查詢，那么您可以將其用作 CTE：

with your_query as
  (select ... --> put your query in here
   from ...
   where ...
  ),
-- code I wrote goes here, starting from line #28
temp as
...

在您發布查詢后，它會是這樣的；請注意，我不知道您要比較哪些列，因為不再有KEY1,KEY2和VAL列；你必須自己修復它。

WITH
   your_query
   AS
      (  SELECT COUNT (*) cnt,
                t2.ORIGINATOR_ID,
                t3.name,
                t4.MULTI_TRANCHE_FLAG,
                v1.SOURCE_OF_DEAL,
                ta1.PRICING_DATE
           FROM vw_origination_deal_advice ta1
                LEFT JOIN vw_orig_deal_advice_summary v1
                   ON v1.deal_id = ta1.orig_deal_advice_id
                LEFT JOIN tbl_issue_tranche t4 ON t4.id = ta1.ISSUE_TRANCHE_ID
                LEFT JOIN tbl_issue_originator t2
                   ON ta1.issue_tranche_id = t2.ISSUE_TRANCHE_ID
                LEFT JOIN tbl_staff t3 ON t2.originator_id = t3.staff_id
          WHERE     ta1.ISSUE_TRANCHE_ID IN
                       (  SELECT ta2.ISSUE_TRANCHE_ID
                            FROM vw_origination_deal_advice ta2
                        GROUP BY ta2.ISSUE_TRANCHE_ID)
                AND ta1.PRICING_DATE LIKE '%-21%'
                AND t4.MULTI_TRANCHE_FLAG = 1
       GROUP BY ta1.orig_deal_advice_id,
                ta1.ISSUE_TRANCHE_ID,
                t4.ACTIVE,
                t2.ORIGINATOR_ID,
                t3.name,
                t4.MULTI_TRANCHE_FLAG,
                v1.SOURCE_OF_DEAL,
                ta1.PRICING_DATE),
   temp
   AS
      -- count > 1 means that there are values that match.
      -- As KEY1, KEY2, VAL don't exist in your query, you'll have to use appropriate
      -- names from in the rest of the query
      (  SELECT key1, val, COUNT (*) cnt               
           FROM test
       GROUP BY key1, val)
  SELECT a.key1, a.key2, a.val
    FROM test a
   WHERE (a.key1, a.val) IN (SELECT b.key1, b.val
                               FROM temp b
                              WHERE b.cnt = 1)                    --> no match
ORDER BY a.key1, a.key2;

標籤：

上一篇：有沒有辦法將物化視圖從一個資料庫服務名稱復制/移動到Oracle中的另一個？

下一篇：OracleSQLFor回圈選項