SQL 是否可以直接选择 EXISTS 作为位？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2759756/
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
is it possible to select EXISTS directly as a bit?
提问by jcollum
I was wondering if it's possible to do something like this (which doesn't work):
我想知道是否有可能做这样的事情（这不起作用）：
select cast( (exists(select * from theTable where theColumn like 'theValue%') as bit)
select cast( (exists(select * from theTable where theColumn like 'theValue%') as bit)
Seems like it should be doable, but lots of things that should work in SQL don't ;) I've seen workarounds for this (SELECT 1 where... Exists...) but it seems like I should be able to just cast the result of the exists function as a bit and be done with it.
似乎它应该是可行的，但是很多应该在 SQL 中工作的东西都没有；) 我已经看到了解决方法（SELECT 1 where... Exists...）但似乎我应该能够将exists 函数的结果转换为一点并完成它。
回答by Alex K.
No, you'll have to use a workaround.
不，您必须使用解决方法。
If you must return a conditional bit 0/1 another way is to:
如果必须返回条件位 0/1，另一种方法是：
SELECT CAST(
CASE WHEN EXISTS(SELECT * FROM theTable where theColumn like 'theValue%') THEN 1
ELSE 0
END
AS BIT)
Or without the cast:
或者没有演员：
SELECT
CASE
WHEN EXISTS( SELECT 1 FROM theTable WHERE theColumn LIKE 'theValue%' )
THEN 1
ELSE 0
END
回答by gbn
SELECT CAST(COUNT(*) AS bit) FROM MyTable WHERE theColumn like 'theValue%'
When you cast to bit
当你投掷到位时
- 0 -> 0
- everything else -> 1
- And NULL -> NULL of course, but you can't get NULL with COUNT(*) without a GROUP BY
- 0 -> 0
- 其他所有 -> 1
- 和 NULL -> NULL 当然，但你不能用 COUNT(*) 得到 NULL 没有 GROUP BY
bitmaps directly to booleanin .net datatypes, even if it isn't really...
bit直接映射到boolean.net 数据类型，即使它不是真的......
This looks similar but gives no row (not zero) if no matches, so it's not the same
这看起来很相似，但如果没有匹配项，则不会给出任何行（不是零），因此它不一样
SELECT TOP 1 CAST(NumberKeyCOlumn AS bit) FROM MyTable WHERE theColumn like 'theValue%'
回答by JohnLBevan
I'm a bit late on the uptake for this; just stumbled across the post. However here's a solution which is more efficient & neat than the selected answer, but should give the same functionality:
我对此的理解有点晚了；刚刚偶然发现了这个帖子。然而，这是一个比所选答案更高效和整洁的解决方案，但应该提供相同的功能：
declare @t table (name nvarchar(16))
declare @b bit
insert @t select N'Simon Byorg' union select N'Roe Bott'
select @b = isnull((select top 1 1 from @t where name = N'Simon Byorg'),0)
select @b whenTrue
select @b = isnull((select top 1 1 from @t where name = N'Anne Droid'),0)
select @b whenFalse
回答by Jaider
You can use IIFand CAST
您可以使用IIF和CAST
SELECT CAST(IIF(EXISTS(SELECT * FROM theTable
where theColumn like 'theValue%'), 1, 0) AS BIT)
回答by Nelson
You can also do the following:
您还可以执行以下操作：
SELECT DISTINCT 1
FROM theTable
WHERE theColumn LIKE 'theValue%'
If there are no values starting with 'theValue' this will return null (no records) rather than a bit 0 though
如果没有以 'theValue' 开头的值，这将返回 null（无记录）而不是位 0
回答by Martin Smith
No it isn't possible. The bit data type is not a boolean data type. It is an integer data type that can be 0,1, or NULL.
不，这是不可能的。位数据类型不是布尔数据类型。它是一种整数数据类型，可以是 0,1 或 NULL。
回答by MEC
SELECT IIF(EXISTS(SELECT * FROM theTable WHERE theColumn LIKE 'theValue%'), 1, 0)
回答by anar khalilov
Another solution is to use ISNULLin tandem with SELECT TOP 1 1:
另一种解决方案是ISNULL与SELECT TOP 1 1以下一起使用：
SELECT ISNULL((SELECT TOP 1 1 FROM theTable where theColumn like 'theValue%'), 0)
回答by ScottK
I believe existscan only be used in a where clause, so you'll have to do a workaround (or a subquery with exists as the where clause). I don't know if that counts as a workaround.
我相信exists只能用在 where 子句中，所以你必须做一个解决方法（或者一个以exists 作为 where 子句的子查询）。我不知道这是否算作一种解决方法。
What about this:
那这个呢：
create table table1 (col1 int null)
go
select 'no items',CONVERT(bit, (select COUNT(*) from table1) ) -- returns 'no items', 0
go
insert into table1 (col1) values (1)
go
select '1 item',CONVERT(bit, (select COUNT(*) from table1) ) --returns '1 item', 1
go
insert into table1 (col1) values (2)
go
select '2 items',CONVERT(bit, (select COUNT(*) from table1) ) --returns '2 items', 1
go
insert into table1 (col1) values (3)
go
drop table table1
go
