SQL与条件相交 [英] Sql intersect conditional

问题描述

我想知道我们是否可以有条件地相交。
Theres有一些查询，但是结果是错误的（总是空的）。
我写了应该得到的结果。

I want to know if we can do an intersect conditional. theres is somes query, but the result is wrong (always empty). I write what it should result.

DECLARE @CAN_USE_TABLE1 BIT
DECLARE @CAN_USE_TABLE2 BIT
DECLARE @CAN_USE_TABLE3 BIT
DECLARE @CAN_USE_TABLE4 BIT

DECLARE @TABLE1 AS TABLE ( ABC INT ) -- values will be 1,2,3
DECLARE @TABLE2 AS TABLE ( ABC INT ) -- values will be 1,2
DECLARE @TABLE3 AS TABLE ( ABC INT ) --EMPTY TABLE
DECLARE @TABLE4 AS TABLE ( ABC INT ) --EMPTY TABLE
INSERT INTO @TABLE1 VALUES (1)
INSERT INTO @TABLE1 VALUES (2)
INSERT INTO @TABLE1 VALUES (3)
INSERT INTO @TABLE2 VALUES (1)
INSERT INTO @TABLE2 VALUES (2)

SET @CAN_USE_TABLE1 = 1
SET @CAN_USE_TABLE2 = 1
SET @CAN_USE_TABLE3 = 1
SET @CAN_USE_TABLE4 = 0

SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1
INTERSECT
SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1
INTERSECT 
SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1
INTERSECT
SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1

--RESULT SHOULD BE :
--  NO RESULT
--
--  BECAUSE, AT THIS STAGE, TABLE1 AND TABLE2 AND TABLE3 SHOULD BE INTERSECTED. AND BECAUSE TABLE3 IS EMPTY, THE RESULT IS EMPTY.

SET @CAN_USE_TABLE1 = 1
SET @CAN_USE_TABLE2 = 1
SET @CAN_USE_TABLE3 = 0
SET @CAN_USE_TABLE4 = 0

SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1
INTERSECT
SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1
INTERSECT 
SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1
INTERSECT
SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1

--RESULT SHOULD BE :
--  1
--  2
--
--  BECAUSE, AT THIS STAGE, TABLE1 AND TABLE2 SHOULD BE INTERSECTED

SET @CAN_USE_TABLE1 = 0
SET @CAN_USE_TABLE2 = 1
SET @CAN_USE_TABLE3 = 0
SET @CAN_USE_TABLE4 = 0

SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1
INTERSECT
SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1
INTERSECT 
SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1
INTERSECT
SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1

--RESULT SHOULD BE :
--  1
--  2
--
--  BECAUSE, AT THIS STAGE, ONLY TABLE 2 SHOULD BE USED

推荐答案

与任何其他集合 INTERSECT ed组成的空集合将始终为空。就像乘以0。您总是得到0。

An empty set INTERSECTed with any other set will always be empty. It's like multiplying by 0. You always get 0.

有条件的 INTERSECT ing需要动态查询，或者临时表，例如：

Conditional INTERSECTing will require either a dynamic query, or a staging table, like this:

初始化

DECLARE @CAN_USE_TABLE1 BIT
DECLARE @CAN_USE_TABLE2 BIT
DECLARE @CAN_USE_TABLE3 BIT
DECLARE @CAN_USE_TABLE4 BIT

DECLARE @TABLE1 AS TABLE ( ABC INT )
DECLARE @TABLE2 AS TABLE ( ABC INT )
DECLARE @TABLE3 AS TABLE ( ABC INT )
DECLARE @TABLE4 AS TABLE ( ABC INT )
DECLARE @RESULT AS TABLE ( ABC INT ) --Adding this result table
INSERT INTO @TABLE1 VALUES (1)
INSERT INTO @TABLE1 VALUES (2)
INSERT INTO @TABLE1 VALUES (3)
INSERT INTO @TABLE2 VALUES (1)
INSERT INTO @TABLE2 VALUES (2)

SET @CAN_USE_TABLE1 = 1
SET @CAN_USE_TABLE2 = 1
SET @CAN_USE_TABLE3 = 0
SET @CAN_USE_TABLE4 = 0

正在处理

INSERT INTO @RESULT
SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1=1 UNION
SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2=1 UNION
SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3=1 UNION
SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4=1

DELETE r FROM @RESULT r
WHERE NOT EXISTS(SELECT 1 FROM @TABLE1 WHERE ABC=r.ABC)
AND @CAN_USE_TABLE1=1;

DELETE r FROM @RESULT r
WHERE NOT EXISTS(SELECT 1 FROM @TABLE2 WHERE ABC=r.ABC)
AND @CAN_USE_TABLE2=1;

DELETE r FROM @RESULT r
WHERE NOT EXISTS(SELECT 1 FROM @TABLE3 WHERE ABC=r.ABC)
AND @CAN_USE_TABLE3=1;

DELETE r FROM @RESULT r
WHERE NOT EXISTS(SELECT 1 FROM @TABLE4 WHERE ABC=r.ABC)
AND @CAN_USE_TABLE4=1;

SELECT * FROM @RESULT;

结果

1
2

这篇关于SQL与条件相交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！