在JOIN ON子句中使用T-SQL Case语句

Question

我正在尝试在JOIN ON子句中构建一个条件/ if 语句。

LEFT JOIN [CTSTRC] [Statuses] ON RIGHT([Statuses].[STRID], 3) = [CTE].[F61]

问题在于列[Statuses].[STRID]包含文本和数字。我正在将其与[CTE].[F61]这一整数进行比较。

有没有办法检测列[Statuses].[STRID]是字符还是数字，然后如果它是字符，将其设置为0？

以下是伪查询以帮助说明：

LEFT JOIN [CTSTRC] [Statuses] 
    ON RIGHT((
        CASE [Statuses].[STRID] 
            WHEN TEXT THEN 0 
            ELSE CAST([Statuses].[STRID] AS INT) END), 3) = [CTE].[F61]

- Jared

---

你是在使用 RIGHT(STRID, 3) 将 A001 转换为 1，还是将 1001 转换为 1？如果是前者，请记住 STRID 不是数字。如果是后者，请考虑使用模运算符（% 1000）。 - user743382

我对 SQL 一点也不精通，所以我不确定模数运算符是什么。我试图比较 190 = 190，但我的 RIGHT 调用中的一些数据得到了 ABC = 190。 - Jared

好的。我想问一下在STRID列中，190剩下的是什么？STRID全部都是数字吗？因为如果不是，如果它可以是A190，那么你的伪代码“CASE STRID WHEN TEXT”条件将会触发，你将得不到190。至于模运算符，它表示除法后的余数。如果STRID全部都是（正）数字，则最后三位数字是该数字除以1000的余数。 - user743382

哦，我明白了。这是190的完整文本：IDS_ENUM_Change_262147_190，另一个结果是：IDS_STATUS_HOLD。 - Jared

那么请使用Conrad Frix的解决方案，该方案检查最后三个字符是否为数字，而不是整个字符串，并且没有其他答案的问题。 - user743382

Answer 1

您正在寻找IsNumeric函数，但它并不总是有效（+、-和.是数字），因此您需要使用由GBN描述的解决方案（链接），即在您的字符型变量中添加.0e0。

LEFT JOIN [CTSTRC] [Statuses] ON 
    (CASE WHEN ISNUMERIC(RIGHT([Statuses].[STRID], 3) + '.0e0) = 1 
          THEN  CAST(RIGHT([Statuses].[STRID], 3) AS INT) 
          ELSE 0  END) = [CTE].[F61] 

- Conrad Frix

---

从技术上讲是正确的，但是，在连接中使用CASE太丑陋了，呵呵。 - Bert

修复这个可怕的设计的正确方法是将数字和字符串拆分成两列。如果这不可能（因为是遗留系统），最好创建一个持久化计算列，如有必要，可以为您的使用添加索引。我在下面的答案中详细说明。 - KM.

Answer 2

创建一个持久化计算列并在其上添加索引。

ALTER TABLE YourTable ADD
    NewIntID AS (CASE ISNUMERIC(RIGHT([Statuses].[STRID], 3) + '.0e0)
                     WHEN 1 THEN CAST(RIGHT([Statuses].[STRID], 3) AS INT) 
                     ELSE 0
                 END) PERSISTED
GO

CREATE INDEX IX_YourTable_NewIntID 
ON YourTable (NewIntID ); 
GO

现在，您可以像使用正确的数字ID一样加入到新的NewIntID列中。

- KM.

---

很遗憾，原帖的作者已经评论说他们无法修改表格。但是如果原帖作者可以修改表格，那么这绝对是最好的方法。如果包括计算列的DDL，则加1分。 - Conrad Frix

@Conrad Frix，然后是一个持久化的索引视图...或者一个只包括PK和NewIntID的新的1对1表。 - KM.

Answer 3

您可以尝试在“状态”表中创建一个表达式列，将右侧3个字符转换为数字，然后尝试使用该表达式列进行连接。

- Dave

---

我不能修改实际的SQL表。你是指查询中吗？ - Jared

那样做行不通。这只会在存储不以数字结尾的STRID值时生成服务器错误。 - user743382

Answer 4

这样的东西不是可以工作吗：

LEFT JOIN [CTSTRC] [Statuses] ON RIGHT([Statuses].[STRID], 3) = cast([CTE].[F61] as varchar(3))

你真正关心的是是否有匹配，那么为什么不将数字转换为varchar？你需要测试这两个想法，看哪一个更快。

我同意@KM的观点，修复这样的糟糕设计是最好的解决方案。在连接中使用函数和Case语句表明你的设计存在致命缺陷，应该进行修复。

- HLGEM

---

我喜欢这个答案；从另一个角度看待解决方案（转换为字符串）；巧妙--对我有用！ - Omar

Answer 5

您正在寻找ISNUMERIC函数（我相信它是在SQL 2005中引入的）：

LEFT JOIN [CTSTRC] [Statuses] ON 
    (CASE ISNUMERIC(RIGHT([Statuses].[STRID], 3)) WHEN 0 THEN 0 ELSE CAST(RIGHT([Statuses].[STRID], 3) AS INT) END) = [CTE].[F61] 

- Jeff Hornby

---

ISNUMERIC 非常不可靠。尝试检查 SELECT ISNUMERIC('2233e4')。 - JNK

@JNK，这有什么不可靠的？我期望它能识别指数符号。 - Jeff Hornby

因为如果他的字段的最后3个字符是3e3，它们将通过ISNUMERIC，但在int上无法通过JOIN并出现错误。 - JNK

Answer 6

你可能想尝试这样做

select ...
from CTE
inner join
(
    select ...
    from [Statuses]
    where ISNUMERIC(STRID + '.0e0') = 1
) rsNumeric on CTE.F61 = rsNumeric.STRID

- Bert

---

ISNUMERIC 非常不可靠。尝试检查 SELECT ISNUMERIC('2233e4')。 - JNK

@jnk 你可以提出这个观点，即科学计数法中的*本质上是一个数字。 - Bert

看我的评论，我知道它是为什么起作用的，但在这种情况下，它不是一个安全检查。 - JNK

@JNK 我会购买它。修改以满足要求。 - Bert