SQL 在 SELECT 子句中的列之间使用相等性检查
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15410762/
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
Using an equality check between columns in a SELECT clause
提问by dumbledad
I am using Microsoft SQL Server 2012 and I would like to run this seemingly simple query:
我正在使用 Microsoft SQL Server 2012，我想运行这个看似简单的查询：
SELECT
FirstEvent.id AS firstEventID,
SecondEvent.id AS secondEventID,
DATEDIFF(second, FirstEvent.WndFGEnd, SecondEvent.WndFGStart) AS gap,
FirstEvent.TitleID = SecondEvent.TitleID AS titlesSameCheck
FROM VibeFGEvents AS FirstEvent
RIGHT OUTER JOIN VibeFGEvents AS SecondEvent
ON
FirstEvent.intervalMode = SecondEvent.intervalMode
AND FirstEvent.id = SecondEvent.id - 1
AND FirstEvent.logID = SecondEvent.logID
However FirstEvent.TitleID = SecondEvent.TitleID AS titlesSameCheckin the SELECTclause is incorrect syntax. But the SELECT Clause (Transact-SQL) documentationincludes this syntax:
但是FirstEvent.TitleID = SecondEvent.TitleID AS titlesSameCheck在SELECT子句中是不正确的语法。但是SELECT 子句 (Transact-SQL) 文档包含以下语法：
SELECT [ ALL | DISTINCT ]
[ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ]
<select_list>
<select_list> ::=
{
*
| { table_name | view_name | table_alias }.*
| {
[ { table_name | view_name | table_alias }. ]
{ column_name | $IDENTITY | $ROWGUID }
| udt_column_name [ { . | :: } { { property_name | field_name }
| method_name ( argument [ ,...n] ) } ]
| expression
[ [ AS ] column_alias ]
}
| column_alias = expression
} [ ,...n ]
I think that means an expression is valid in the select clause and indeed the examples given include things like 1 + 2. Looking at the documentation for expressions:
我认为这意味着表达式在 select 子句中是有效的，并且给出的示例确实包括诸如1 + 2. 看着为表现形式的文档：
{ constant | scalar_function | [ table_name. ] column | variable
| ( expression ) | ( scalar_subquery )
| { unary_operator } expression
| expression { binary_operator } expression
| ranking_windowed_function | aggregate_windowed_function
}
boolean equality checks are valid expressions and indeed the example expression given in the = (Equals) (Transact-SQL) documentationincludes one:
布尔相等性检查是有效的表达式，实际上= (Equals) (Transact-SQL) 文档中给出的示例表达式包括一个：
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE GroupName = 'Manufacturing'
albeit in the WHEREclause not the SELECTclause. It looks like I cannot use =the equality operator to compare expressions in my SELECTclause as they are being wrongly interpreted as assignment.
尽管在WHERE子句中不是SELECT子句。看起来我不能使用=相等运算符来比较我的SELECT子句中的表达式，因为它们被错误地解释为赋值。
How do I include a Boolean equality column comparison equivalent to FirstEvent.TitleID = SecondEvent.TitleID AS titlesSameCheckin my SELECTclause?
如何FirstEvent.TitleID = SecondEvent.TitleID AS titlesSameCheck在我的SELECT子句中包含等效于的布尔相等列比较？
回答by Dumitrescu Bogdan
Like this:
像这样：
case when FirstEvent.TitleID = SecondEvent.TitleID then 1 else 0 end as titlesSameCheck
回答by pyrospade
You cannot use the Boolean type directly except in conditional statements (case, where, having, etc.)
您不能直接使用布尔类型，除非在条件语句（case、where、have 等）中
Best way to solve your problem is to do something like
解决您的问题的最佳方法是做类似的事情
select case when x = y then 'true' else 'false' end
The bittype is probably the closest to boolean.
该bit类型可能最接近布尔值。
select CAST(case when x = y then 1 else 0 end as bit)
Of course, use whichever two values best represent what you are after.
当然，使用最能代表您所追求的任何两个值。
回答by Allan
As the two existing answers state, boolean values can't be returned as a column value. This is documented in the Comparison Operators section:
正如两个现有答案所述，布尔值不能作为列值返回。这记录在比较运算符部分：
Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.
与其他 SQL Server 数据类型不同，布尔数据类型不能指定为表列或变量的数据类型，也不能在结果集中返回。
Given that restriction, using CASEto transform the value to something that can be displayed is your best alternative.
鉴于此限制，使用CASE将值转换为可以显示的内容是您最好的选择。
