如何在WHERE中只運行第一個條件OR？-有解無憂

我有一個查詢，我只想獲取滿足第一個條件的那些行 OR。我想檢查條件，例如第一個OR不滿足條件，那么我應該看到第二個OR等等。

這是一個簡單的例子。但是，這些確實是具有 3000 萬行的大表。我不能使用完全加入。我的代碼應該投入使用。

我的樣本資料：

DECLARE @tbl_1 TABLE (Id INT)

DECLARE @tbl_2 TABLE (Id INT)

DECLARE @tbl_3 TABLE (Id INT)

DECLARE @tbl_4 TABLE (Id INT)

及其資料：

INSERT INTO @tbl_1 ([Id]) VALUES
(1), (2), (3), (4), (5), (6)

INSERT INTO @tbl_2 ([Id]) VALUES (8)

INSERT INTO @tbl_3 ([Id]) VALUES (3)

INSERT INTO @tbl_4 ([Id]) VALUES (4)

SELECT  * FROM @tbl_1 AS t
WHERE t.Id IN (SELECT Id FROM @tbl_2 AS t2)
    OR t.Id IN (SELECT Id FROM @tbl_3 AS t2)
    OR t.Id IN (SELECT Id FROM @tbl_4 AS t2)

然后我只想看一個專案：

但它給出了： 3, 4

或者另一個例子：

INSERT INTO @tbl_1 ([Id]) VALUES
(1), (2), (3), (4), (5), (6)

INSERT INTO @tbl_2 ([Id]) VALUES (1), (2) 

INSERT INTO @tbl_3 ([Id]) VALUES (3)

INSERT INTO @tbl_4 ([Id]) VALUES (4)

SELECT  * FROM @tbl_1 AS t
WHERE t.Id IN (SELECT Id FROM @tbl_2 AS t2)
    OR t.Id IN (SELECT Id FROM @tbl_3 AS t2)
    OR t.Id IN (SELECT Id FROM @tbl_4 AS t2)

然后我只想看兩個第一專案：

1, 2

但它給出了： 1, 2, 3, 4

我試過用OR. 但是，它需要所有滿足條件的專案。如何撰寫WHERE只執行一個第一 OR條陳述句的陳述句？

uj5u.com熱心網友回復：

你想要一個排名。@tbl_2 中的行優先于表@tbl_3 中的行，并且這些行再次優先于表@tbl_4 中的行。

理想情況下，您將只有一張桌子而不是三張桌子，并將優先級存盤在一起：

INSERT INTO @ids (id, priority) VALUES (1, 1), (2, 1), (3, 2), (4, 3);

當我們在尋找最佳的整體優先級時（與尋找每個產品的最佳匹配等相反），我們可以加入，按優先級排序我們的行，并使用TOP WITH TIES僅保留具有最佳優先級的行。

SELECT  TOP(1) WITH TIES t.* 
FROM @tbl_1 AS t
JOIN @ids ids ON ids.id = t.id
ORDER BY ids.priority;

如果您想堅持使用三個選項卡，請即時添加優先級：

WITH ids AS
(
  SELECT id, 1 AS priority FROM @tbl_2
  UNION ALL
  SELECT id, 2 AS priority FROM @tbl_3
  UNION ALL
  SELECT id, 3 AS priority FROM @tbl_4
)
SELECT  TOP(1) WITH TIES t.* 
FROM @tbl_1 AS t
JOIN ids ON ids.id = t.id
ORDER BY ids.priority;

uj5u.com熱心網友回復：

試試這個：

SELECT * FROM tbl_1 AS t
WHERE
EXISTS(SELECT 1 FROM tbl_2 t2 WHERE t2.id = t.id)
AND
(SELECT COUNT(1) FROM tbl_1 t1
 JOIN tbl_2 t2
 ON t2.id = t1.id) > 0
OR (
    EXISTS(SELECT 1 FROM tbl_3 t3 WHERE t3.id = t.id)
    AND
    (SELECT COUNT(1) FROM tbl_1 t1
     JOIN tbl_2 t2
     ON t2.id = t1.id) = 0
    AND
    (SELECT COUNT(1) FROM tbl_1 t1
     JOIN tbl_3 t3
     ON t3.id = t1.id) > 0
)
OR (
EXISTS(SELECT 1 FROM tbl_4 t4 WHERE t4.id = t.id)
AND
(SELECT COUNT(1) FROM tbl_1 t1
 JOIN tbl_2 t2
 ON t2.id = t1.id) = 0
AND
(SELECT COUNT(1) FROM tbl_1 t1
 JOIN tbl_3 t3
 ON t3.id = t1.id) = 0
AND
(SELECT COUNT(1) FROM tbl_1 t1
 JOIN tbl_4 t4
 ON t4.id = t1.id) > 0
)

這樣你就有了 OR 獨占

Sql Fiddle #1（帶有資料集 1、2；3；4）

Sql Fiddle #2（帶有資料集 8；3；4）

uj5u.com熱心網友回復：

一個超過3TOP (1) WITH TIES個表 ID 的 A 就可以完成這項作業。ORDER BYCASE WHEN

如果沒有任何匹配項，@tbl_2則匹配項@tbl_3將排在首位。如果與@tbl_2&不匹配，@tbl_3則將@tbl_4排在首位。

DECLARE @tbl_1 TABLE (Id INT)
DECLARE @tbl_2 TABLE (Id INT)
DECLARE @tbl_3 TABLE (Id INT)
DECLARE @tbl_4 TABLE (Id INT)
INSERT INTO @tbl_1 ([Id]) VALUES (1), (2), (3), (4), (5), (6)
INSERT INTO @tbl_2 ([Id]) VALUES (8)
INSERT INTO @tbl_3 ([Id]) VALUES (3)
INSERT INTO @tbl_4 ([Id]) VALUES (4)

SELECT TOP (1) WITH TIES t.*
FROM @tbl_1 AS t
LEFT JOIN @tbl_2 t2 ON t2.Id = t.Id
LEFT JOIN @tbl_3 t3 ON t3.Id = t.Id
LEFT JOIN @tbl_4 t4 ON t4.Id = t.Id
WHERE t.Id IN (t2.Id, t3.Id, t4.Id)
ORDER BY CASE 
         WHEN t2.Id IS NOT NULL THEN 2
         WHEN t3.Id IS NOT NULL THEN 3
         WHEN t4.Id IS NOT NULL THEN 4
         END;

ID

3

ID
3

DECLARE @tbl_1 TABLE (Id INT)
DECLARE @tbl_2 TABLE (Id INT)
DECLARE @tbl_3 TABLE (Id INT)
DECLARE @tbl_4 TABLE (Id INT)
INSERT INTO @tbl_1 ([Id]) VALUES (1), (2), (3), (4), (5), (6)
INSERT INTO @tbl_2 ([Id]) VALUES (1), (2) 
INSERT INTO @tbl_3 ([Id]) VALUES (3)
INSERT INTO @tbl_4 ([Id]) VALUES (4)

SELECT TOP (1) WITH TIES t.*
FROM @tbl_1 AS t
LEFT JOIN @tbl_2 t2 ON t2.Id = t.Id
LEFT JOIN @tbl_3 t3 ON t3.Id = t.Id
LEFT JOIN @tbl_4 t4 ON t4.Id = t.Id
WHERE t.Id IN (t2.Id, t3.Id, t4.Id)
ORDER BY CASE 
         WHEN t2.Id IS NOT NULL THEN 2
         WHEN t3.Id IS NOT NULL THEN 3
         WHEN t4.Id IS NOT NULL THEN 4
         END;

ID

1

2

ID
1
2

在這里測驗db<>fiddle

uj5u.com熱心網友回復：

假設 t2、t3 和 t4 具有唯一值。您可能想要驗證執行計劃是否基于合并連接/散列連接而不是嵌套回圈。

select  top 1 with ties
        t1.*
        
from              @tbl_1 AS t1
        left join @tbl_2 as t2 on t2.id = t1.id
        left join @tbl_3 as t3 on t3.id = t1.id
        left join @tbl_4 as t4 on t4.id = t1.id
        
where   coalesce(t2.id,t3.id,t4.id) is not null

order by case 
             when t2.Id is not null then 1
             when t3.Id is not null then 2
             when t4.Id is not null then 3
         end

小提琴

另一種完全沒有 JOIN 的解決方案。只有 GROUP BY :-)

select      top 1 with ties

            id
        
from        (         select id, 999 from @tbl_1
            union all select id, 1   from @tbl_2
            union all select id, 2   from @tbl_3
            union all select id, 3   from @tbl_4
            ) t (id,priority)
        
group by    id 

having      max(priority) = 999

order by    min(priority)

小提琴

uj5u.com熱心網友回復：

您可能會接受的另一個想法雖然不是很優雅，但它是一種更加程式化的方法，因此您只有在需要時才最終觸摸一張桌子，如果這是一個問題，那么它的性能會更高。

將符合條件的 ID 插入到臨時表中，并且只有在每個階段都沒有結果時才繼續，例如

create table #results (id int);

insert into #results 
select id
from t1 where exists (select * from t2 where t2.id=t1.id)

if @@RowCount=0
begin
    insert into #results 
    select id
    from t1 where exists (select * from t3 where t3.Id=t1.Id)

    if @@RowCount=0
    begin
        insert into #results 
        select id
        from t1 where exists (select * from t4 where t4.Id=t1.Id)
    end
end

select id
from #results

示例小提琴

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/422318.html

標籤：

上一篇：Seleniumweb自動化使用python：如何使用selenium處理表以通過匹配文本來查找特定行并洗掉該行

下一篇：SQLServer子表中有資料時如何發送true或false？