JOIN ON 子句中的 T-SQL Case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8361183/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL Case Statement in a JOIN ON Clause
提问by Jared
I am trying to build a case/if statement in a JOIN ONclause.
我正在尝试在JOIN ON子句中构建 case/if 语句。
LEFT JOIN [CTSTRC] [Statuses] ON RIGHT([Statuses].[STRID], 3) = [CTE].[F61]
The problem is that the column [Statuses].[STRID]contains text and numbers. The column I am comparing it to [CTE].[F61]is an integer.
问题是该列[Statuses].[STRID]包含文本和数字。我与之比较的列[CTE].[F61]是一个整数。
Is there a way to detect if column [Statuses].[STRID]has a character or a number and THEN set it to 0 if it is a character?
有没有办法检测列[Statuses].[STRID]是否有字符或数字，如果是字符，则将其设置为 0？
Here is a pseudo query to help:
这是一个伪查询来帮助：
LEFT JOIN [CTSTRC] [Statuses] ON RIGHT((CASE [Statuses].[STRID] WHEN TEXT THEN 0 ELSE CAST([Statuses].[STRID] AS INT) END), 3) = [CTE].[F61]
Can someone point me in the right direction?
有人可以指出我正确的方向吗？
Thanks!
谢谢！
回答by Conrad Frix
You're looking for IsNumeric but it doesn't always work (+,- and . are numeric) so you need to use the solution described by GBNwhich is to add .0e0 to your varchar
您正在寻找 IsNumeric，但它并不总是有效（+、- 和 . 是数字），因此您需要使用GBN 描述的解决方案，即将 .0e0 添加到您的 varchar
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]
回答by KM.
create a persisted computed column and add an index on it.
创建一个持久化计算列并在其上添加索引。
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
you can now just join to the new NewIntID column as if it were the proper numeric ID now.
您现在可以加入新的 NewIntID 列，就好像它现在是正确的数字 ID 一样。
回答by Dave
You can try to create an expression column in the Statuses table that converts the right 3 characters to a number and then try to join on the expression column.
您可以尝试在 Statuses 表中创建一个表达式列，将正确的 3 个字符转换为数字，然后尝试连接表达式列。
回答by HLGEM
Wouldn't something like this work:
不会像这样的工作：
LEFT JOIN [CTSTRC] [Statuses] ON RIGHT([Statuses].[STRID], 3) = cast([CTE].[F61] as varchar(3))
All you really care about is whether you have a match, so why not convert the numeric to varchar? You would have to test both ideas to see which one is faster.
您真正关心的是您是否有匹配项，那么为什么不将数字转换为 varchar 呢？你必须测试这两种想法，看看哪个更快。
I do agree with @KM, fixing a bad design like this is the best solution. Having functions and Case statements in a join is an indicatior that your design is fatally flawed and should be fixed.
我同意@KM，修复像这样的糟糕设计是最好的解决方案。在连接中包含函数和 Case 语句表明您的设计存在致命缺陷，应该予以修复。
回答by Jeff Hornby
You're looking for the ISNUMERIC function (I believe it was introduced in SQL 2005):
您正在寻找 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]
回答by Bert
You might want to try something like this
你可能想尝试这样的事情
select ...
from CTE
inner join
(
select ...
from [Statuses]
where ISNUMERIC(STRID + '.0e0') = 1
) rsNumeric on CTE.F61 = rsNumeric.STRID
