我想知道我们是否可以做相交条件。
有一些查询，但结果是错误的（始终为空）。
我写下它应该产生的结果。
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。
有条件的INTERSECT将需要动态查询或临时表，如下所示：
初始化
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;
Result
1
2
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
