SQL中“OR”和“INTERSECT”的使用

我只有一個 Table TableA 如下所示：

我想實作如下輸出：

我有如下查詢，但沒有看到任何輸出：

Select ID from TableA 
WHERE 
(DATE BETWEEN '13-APR-22' AND '11-MAY-22') 
AND
(
(TYPE = 'BS' AND TYPE = 'GH') 
OR
(TYPE = 'GH' AND TYPE = 'MPN') 
OR
(TYPE = 'BS' AND TYPE = 'MPN')

);

我也嘗試了以下方法，但在輸出中出現錯誤：

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'BS')

INTERSECT

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'GH')

OR

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'GH')

INTERSECT

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'MPN')

OR

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'BS')

INTERSECT

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'MPN')

請幫助我實作我正在尋找的輸出。提前致謝！

uj5u.com熱心網友回復：

我會在這里使用聚合方法：

WITH cte AS (
    SELECT ID
    FROM TableA
    WHERE "DATE" BETWEEN date '2022-04-13' AND date '2022-05-11'
    GROUP BY ID
    HAVING
        (COUNT(CASE WHEN TYPE = 'BS' THEN 1 END) > 0 AND
         COUNT(CASE WHEN TYPE = 'GH' THEN 1 END) > 0)
        OR
        (COUNT(CASE WHEN TYPE = 'GH' THEN 1 END) > 0 AND
         COUNT(CASE WHEN TYPE = 'MPN' THEN 1 END) > 0)
        OR
        (COUNT(CASE WHEN TYPE = 'BS' THEN 1 END) > 0 AND
         COUNT(CASE WHEN TYPE = 'MPN' THEN 1 END) > 0)
)

SELECT *
FROM TableA
WHERE ID IN (SELECT ID FROM cte);

uj5u.com熱心網友回復：

使用分析函式和條件聚合，因此您不必查詢表兩次：

SELECT id, type, "DATE"
FROM   (
  SELECT t.*,
         COUNT(
           CASE
           WHEN TYPE = 'BS'
           AND  "DATE" >= DATE '2022-04-13'
           AND  "DATE" <  DATE '2022-05-12'
           THEN 1
           END
         ) OVER (PARTITION BY id) AS num_bs,
         COUNT(
           CASE
           WHEN TYPE = 'GH'
           AND  "DATE" >= DATE '2022-04-13'
           AND  "DATE" <  DATE '2022-05-12'
           THEN 1
           END
         ) OVER (PARTITION BY id) AS num_gh,
         COUNT(
           CASE
           WHEN TYPE = 'MPN'
           AND  "DATE" >= DATE '2022-04-13'
           AND  "DATE" <  DATE '2022-05-12'
           THEN 1
           END
         ) OVER (PARTITION BY id) AS num_mpn
  FROM   tableA t
)
WHERE (num_bs > 0 AND num_gh > 0)
OR    (num_bs > 0 AND num_mpn > 0)
OR    (num_gh > 0 AND num_mpn > 0)

其中，對于樣本資料：

CREATE TABLE tablea (ID, Type, "DATE") AS
SELECT 'First',  'BS',  DATE '2022-04-12' FROM DUAL UNION ALL
SELECT 'First',  'GH',  DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'First',  'MPN', DATE '2022-04-15' FROM DUAL UNION ALL
SELECT 'Second', 'BS',  DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'Second', 'MPN', DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'Third',  'GH',  DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'Third',  'BS',  DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'Fourth', 'MPN', DATE '2022-05-15' FROM DUAL UNION ALL
SELECT 'Fourth', 'GH',  DATE '2022-05-15' FROM DUAL UNION ALL
SELECT 'Fifth',  'GH',  DATE '2022-05-06' FROM DUAL;

輸出：

ID	型別	日期
第一的	生長激素	22 年 5 月 10 日
第一的	MPN	22 年 4 月 15 日
第一的	學士學位	22 年 4 月 12 日
第二	學士學位	22 年 5 月 10 日
第二	MPN	22 年 5 月 10 日
第三	生長激素	22 年 5 月 10 日
第三	學士學位	22 年 5 月 10 日

db<>在這里擺弄

標籤：sql 甲骨文

上一篇：空值在映射后給出錯誤值

下一篇：如何取消管道行？