如何比较 SQL Server 中两列的相等性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1632792/
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
How do I compare two columns for equality in SQL Server?
提问by Orion Adrian
I have two columns that are joined together on certain criteria, but I would also like to check if two other columns are identical and then return a bit field if they are.
我有两列根据某些条件连接在一起，但我还想检查其他两列是否相同，如果相同则返回一个位字段。
Is there a simpler solution than using CASE WHEN?
有没有比使用 CASE WHEN 更简单的解决方案？
Ideally I could just use:
理想情况下，我可以使用：
SELECT Column1 = Column2 AS MyDesiredResult
FROM Table1
INNER JOIN Table2 ON Table1.PrimaryKey = Table2.ForeignKey
回答by Ken White
What's wrong with CASE for this? In order to see the result, you'll need at least a byte, and that's what you get with a single character.
CASE 有什么问题？为了查看结果，您至少需要一个字节，这就是您使用单个字符获得的结果。
CASE WHEN COLUMN1 = COLUMN2 THEN '1' ELSE '0' END AS MyDesiredResult
should work fine, and for all intents and purposes accomplishes the same thing as using a bit field.
应该可以正常工作，并且出于所有意图和目的，可以完成与使用位字段相同的事情。
回答by MUHASIN BABU
CASE WHENis the better option
CASE WHEN是更好的选择
SELECT
CASE WHEN COLUMN1 = COLUMN2
THEN '1'
ELSE '0'
END
AS MyDesiredResult
FROM Table1
INNER JOIN Table2 ON Table1.PrimaryKey = Table2.ForeignKey
回答by profaisal
A solution avoiding CASE WHENis to use COALESCE.
避免的解决方案CASE WHEN是使用COALESCE.
SELECT
t1.Col2 AS t1Col2,
t2.Col2 AS t2Col2,
COALESCE(NULLIF(t1.Col2, t2.Col2),NULLIF(t2.Col2, t1.Col2)) as NULL_IF_SAME
FROM @t1 AS t1
JOIN @t2 AS t2 ON t1.ColID = t2.ColID
NULL_IF_SAMEcolumn will give NULLfor all rows where t1.col2 = t2.col2(including NULL).
Though this is not more readable than CASE WHENexpression, it is ANSI SQL.
NULL_IF_SAME列将为NULL所有行提供t1.col2 = t2.col2（包括NULL）。尽管这并不比CASE WHEN表达式更具可读性，但它是 ANSI SQL。
Just for the sake of fun, if one wants to have boolean bit values of 0 and 1 (though it is not very readable, hence not recommended), one can use (which works for all datatypes):
只是为了好玩，如果想要布尔位值为 0 和 1（虽然它不是很可读，因此不推荐），可以使用（适用于所有数据类型）：
1/ISNULL(LEN(COALESCE(NULLIF(t1.Col2, t2.Col2),NULLIF(t2.Col2, t1.Col2)))+2,1) as BOOL_BIT_SAME.
Now if you have one of the numeric data types and want bits, in the above LENfunction converts to string first which may be problematic,so instead this should work:
现在，如果您有一种数字数据类型并需要位，则在上述LEN函数中首先转换为字符串，这可能有问题，因此这应该起作用：
1/(CAST(ISNULL(ABS(COALESCE(NULLIF(t1.Col2, t2.Col2),NULLIF(t2.Col2, t1.Col2)))+1,0)as bit)+1) as FAST_BOOL_BIT_SAME_NUMERIC
Above will work for Integers without CAST.
以上将适用于没有CAST.
NOTE: also in SQLServer 2012, we have IIFfunction.
注意：也在 SQLServer 2012 中，我们有IIF函数。
回答by Lucero
I'd go with the CASE WHENalso.
我CASE WHEN也去。
Depending on what you actually want to do, there may be other options though, like using an outer join or whatever, but that doesn't seem to be what you need in this case.
根据您实际想要做什么，可能还有其他选择，例如使用外连接或其他任何方式，但这似乎不是您在这种情况下需要的。
回答by Rob Garrison
Regarding David Elizondo's answer, this can give false positives. It also does not give zeroes where the values don't match.
关于 David Elizondo 的回答，这可能会产生误报。它也不会在值不匹配的情况下给出零。
Code
代码
DECLARE @t1 TABLE (
ColID int IDENTITY,
Col2 int
)
DECLARE @t2 TABLE (
ColID int IDENTITY,
Col2 int
)
INSERT INTO @t1 (Col2) VALUES (123)
INSERT INTO @t1 (Col2) VALUES (234)
INSERT INTO @t1 (Col2) VALUES (456)
INSERT INTO @t1 (Col2) VALUES (1)
INSERT INTO @t2 (Col2) VALUES (123)
INSERT INTO @t2 (Col2) VALUES (345)
INSERT INTO @t2 (Col2) VALUES (456)
INSERT INTO @t2 (Col2) VALUES (2)
SELECT
t1.Col2 AS t1Col2,
t2.Col2 AS t2Col2,
ISNULL(NULLIF(t1.Col2, t2.Col2), 1) AS MyDesiredResult
FROM @t1 AS t1
JOIN @t2 AS t2 ON t1.ColID = t2.ColID
Results
结果
t1Col2 t2Col2 MyDesiredResult
----------- ----------- ---------------
123 123 1
234 345 234 <- Not a zero
456 456 1
1 2 1 <- Not a match
回答by Raymondo
The use of IIF? And it depends on version of SQL Server.
IIF的使用？这取决于 SQL Server 的版本。
SELECT
IIF(Column1 = Column2, 1, 0) AS MyDesiredResult
FROM Table;
回答by David Elizondo
The closest approach I can think of is NULLIF:
我能想到的最接近的方法是NULLIF：
SELECT
ISNULL(NULLIF(O.ShipName, C.CompanyName), 1),
O.ShipName,
C.CompanyName,
O.OrderId
FROM [Northwind].[dbo].[Orders] O
INNER JOIN [Northwind].[dbo].[Customers] C
ON C.CustomerId = O.CustomerId
GO
NULLIFreturns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
如果两个表达式不相等，则 NULLIF返回第一个表达式。如果表达式相等，则 NULLIF 返回第一个表达式类型的空值。
So, above query will return 1for records in which that columns are equal, the first expressionotherwise.
因此，对于列相等的记录，上面的查询将返回1，否则返回第一个表达式。
