blocks|key|367931|text|如果用户不存在，那么您所拥有的内容将不会返回任何行。这是你需要的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|367932|SELECT+CASE+WHEN+EXISTS+(
++++SELECT+*
++++FROM+[User]
++++WHERE+UserID+=+20070022
)
THEN+CAST(1+AS+BIT)
ELSE+CAST(0+AS+BIT)+END|code-block|syntax|javascript|367933|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

What you have there will return no row at all if the user doesn't exist. Here's what you need:

<pre><code>SELECT CASE WHEN EXISTS (
 SELECT *
 FROM [User]
 WHERE UserID = 20070022
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END
</code></pre>

blocks|key|583758|text|可能会有这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|583759|SELECT+CAST(CASE+WHEN+COUNT(*)+>+0+THEN+1+ELSE+0+END+AS+BIT)
FROM+dummy+WHERE+id+=+1;|code-block|syntax|javascript|583760|http://sqlfiddle.com/#!3/5e555/1|offset|length|583761|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|W|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|T|8|@]|9|@$I|U|J|V|1|W]]|A|$]]|$1|K|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|H]]]]

Possibly something along these lines:

<pre><code>SELECT CAST(CASE WHEN COUNT(*) &gt; 0 THEN 1 ELSE 0 END AS BIT)
FROM dummy WHERE id = 1;
</code></pre>

<a href="http://sqlfiddle.com/#!3/5e555/1">http://sqlfiddle.com/#!3/5e555/1</a>

blocks|key|368021|text|考虑到通常的1+=+true和0+=+false，您所需要做的就是计算行数，并转换为boolean。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|368022|因此，您发布的代码只需要添加一个COUNT()函数：|368023|SELECT+CAST(COUNT(1)+AS+BIT)+AS+Expr1
FROM+[User]
WHERE+(UserID+=+20070022)|code-block|syntax|javascript|368024|entityMap^0|6|8|F|9|16|7|0|G|7|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

Given that commonly <code>1 = true</code> and <code>0 = false</code>, all you need to do is count the number of rows, and cast to a <code>boolean</code>.

Hence, your posted code only needs a <code>COUNT()</code> function added:

<pre><code>SELECT CAST(COUNT(1) AS BIT) AS Expr1
FROM [User]
WHERE (UserID = 20070022)
</code></pre>

blocks|key|368124|text|使用'Exists‘，它返回0或1。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|368125|查询将类似于：|368126|SELECT+EXISTS(SELECT+*+FROM+USER+WHERE+UserID+=+20070022)|code-block|syntax|javascript|368127|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Use 'Exists' which returns either 0 or 1.

The query will be like:

<pre><code>SELECT EXISTS(SELECT * FROM USER WHERE UserID = 20070022)
</code></pre>

blocks|key|583819|text|select+CAST(COUNT(*)+AS+BIT)+FROM+[User]+WHERE+(UserID+=+20070022)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|583820|If+count(*)+=0返回false。如果count(*)+>0，则返回true。|unstyled|583821|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select CAST(COUNT(*) AS BIT) FROM [User] WHERE (UserID = 20070022)
</code></pre>

If count(*) = 0 returns false. If count(*) > 0 returns true.

blocks|key|583874|text|我是这样做的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|583875|SELECT+1+FROM+[dbo].[User]+WHERE+UserID+=+20070022|code-block|syntax|javascript|583876|由于布尔值永远不可能为空(至少在.NET中是如此)，因此它应该缺省为false，或者如果它缺省为true，则可以自己设置。然而1=+true，所以null+=+false，没有额外的语法。|583877|注意:我使用Dapper作为我的micro，我想ADO应该也是这样的。|583878|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I do it like this:

<pre><code>SELECT 1 FROM [dbo].[User] WHERE UserID = 20070022
</code></pre>

Seeing as a boolean can never be null (at least in .NET), it should default to false or you can set it to that yourself if it's defaulting true. However 1 = true, so null = false, and no extra syntax.

Note: I use Dapper as my micro orm, I'd imagine ADO should work the same.

blocks|key|368253|text|对于那些对添加自定义列名获得值感兴趣的人来说，这对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|368254|CAST(
++++CASE+WHEN+EXISTS+(+
+++++++++++SELECT+*+
+++++++++++FROM+mytable+
+++++++++++WHERE+mytable.id+=+1
++++)+
++++THEN+TRUE+
++++ELSE+FALSE+
++++END+AS+bool)+
AS+"nameOfMyColumn"|code-block|syntax|javascript|368255|您可以跳过列名中的双引号，以防您对保持名称的大小写不感兴趣(在某些客户端中)。|368256|我稍微调整了@查德的回答。|368257|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For those of you who are interested in getting the value adding a custom column name, this worked for me:

<pre><code>CAST(
 CASE WHEN EXISTS ( 
 SELECT * 
 FROM mytable 
 WHERE mytable.id = 1
 ) 
 THEN TRUE 
 ELSE FALSE 
 END AS bool) 
AS "nameOfMyColumn"
</code></pre>

You can skip the double quotes from the column name in case you're not interested in keeping the case sensitivity of the name (in some clients).

I slightly tweaked @Chad's answer for this.

blocks|key|368201|text|注意另一个等价的问题:创建一个SQL查询，如果满足条件，则返回(1)，否则返回空结果。请注意，此问题的解决方案更为通用，可以很容易地与上面的答案结合使用，以实现您所提出的问题。由于这个问题更普遍，除了上面针对您的问题提出的漂亮解决方案之外，我还在证明它的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|368202|SELECT+DISTINCT+1+AS+Expr1
FROM+[User]
WHERE+(UserID+=+20070022)|code-block|syntax|javascript|368203|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Notice another equivalent problem: Creating an SQL query that returns (1) if the condition is satisfied and an empty result otherwise. Notice that a solution to this problem is more general and can easily be used with the above answers to achieve the question that you asked. Since this problem is more general, I am proving its solution in addition to the beautiful solutions presented above to your problem.

<pre><code>SELECT DISTINCT 1 AS Expr1
FROM [User]
WHERE (UserID = 20070022)
</code></pre>

blocks|key|584075|text|DECLARE+@isAvailable++++++BIT+=+0;

IF+EXISTS(SELECT+1++FROM+[User]+WHERE+(UserID+=+20070022))
BEGIN
+SET+@isAvailable+=+1
END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|584076|最初，isAvailable布尔值设置为0|unstyled|584077|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>DECLARE @isAvailable BIT = 0;

IF EXISTS(SELECT 1 FROM [User] WHERE (UserID = 20070022))
BEGIN
 SET @isAvailable = 1
END
</code></pre>

initially isAvailable boolean value is set to 0

How to return a boolean value on SQL Select Statement?

I tried this code:

<pre><code>SELECT CAST(1 AS BIT) AS Expr1
FROM [User]
WHERE (UserID = 20070022)
</code></pre>

And it only returns <code>TRUE</code> if the <code>UserID</code> exists on the table. I want it to return <code>FALSE</code> if the <code>UserID</code> doesn't exist on the table.

Return Boolean Value on SQL Select Statement

如何在SQL Select语句中返回布尔值？我试过这段代码：SELECT CAST(1 AS BIT) AS Expr1FROM [User]WHERE (UserID = 20070022)并且仅当表上存在该UserID时，它才返回TRUE。如果表中不存在UserID，我希望它返回FALSE。

问在SQL Select语句上返回布尔值
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Select语句上返回布尔值EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Select语句上返回布尔值
EN