Case 语句不会在未填充一个选择查询时返回值

Question

我以前尝试过用更具体的查询来问这个问题，但我想用一个非常简单的理论查询来分解它，问为什么。

当第二个表t2由于where条件而没有填充时，case语句就会崩溃。

假设tblA只有一行

这将起作用：返回1个

DECLARE @numA int SET @numA=1
DECLARE @numB int SET @numB=1
DECLARE @numC int SET @numC=2
SELECT
    CASE WHEN(t1.v1=1 or t2.v2=1)
    THEN 1
    ELSE 0
    END AS Test1
FROM
   (SELECT 1 as v1
   FROM tblA
   WHERE @numA=@numB) t1,
   (SELECT 0 as v2
   FROM tblA
   WHERE @numA<>@numC) t2

这将不起作用，因为t2不会填充：

DECLARE @numA int SET @numA=1
DECLARE @numB int SET @numB=1
DECLARE @numC int SET @numC=2
SELECT
    CASE WHEN(t1.v1=1 or t2.v2=1)
    THEN 1
    ELSE 0
    END AS Test1
FROM
   (SELECT 1 as v1
   FROM tblA
   WHERE @numA=@numB) t1,
   (SELECT 0 as v2
   FROM tblA
   WHERE @numA=@numC) t2

我怎么能做到这一点！？谢谢大家，我试过

Answer 1

您有一个交叉连接，其中一侧返回0行。合并后也会返回0行。考虑一下：

SELECT a = 1 WHERE 1 = 1;

结果：

a
----
1

现在考虑结果返回0行的情况：

SELECT a = 1 WHERE 1 = 2;

结果：

a
----

现在，交叉连接它们，或者隐含地：

SELECT * FROM
(SELECT a = 1 WHERE 1 = 1) AS t1,
(SELECT a = 1 WHERE 1 = 2) AS t2;

或者明确地：

SELECT * FROM
(SELECT a = 1 WHERE 1 = 1) AS t1
CROSS JOIN 
(SELECT a = 2 WHERE 1 = 2) AS t2;

在这两种情况下，都会得到零行，因为交叉联接的概念是"从一侧获取每一行，并为另一侧的每一行生成一行。"由于一侧没有行，所以就没有行，句号。这就像乘以任何数字*0。不管源是1、50还是6000，这个数字乘以零仍然是零。所以更进一步：

SELECT a = CASE WHEN t1.a = 1 THEN 1 ELSE 0 END 
FROM
(SELECT a = 1 WHERE 1 = 1) AS t1
CROSS JOIN 
(SELECT a = 2 WHERE 1 = 2) AS t2;

这仍然会产生0行，因此CASE表达式不会产生任何内容。也许你的意思是这样的：

SELECT a = CASE WHEN t1.a = 1 OR t2.a = 1 THEN 1 ELSE 0 END FROM
(SELECT a = 1 WHERE 1 = 1) AS t1
FULL OUTER JOIN 
(SELECT a = 2 WHERE 1 = 2) AS t2
ON 1 = 1;

然而，我不得不同意这些评论。与其编造毫无意义的查询来试图理解行为，不如让我们教你如何解决你试图解决的真正问题？