如果没有找到SQL，则返回一个值

Here's my simple query. If I query a record that doesn't exist then I will get nothing returned. I'd prefer that false (0) is returned in that scenario. Looking for the simplist method to account for no records.

这是我的简单查询。如果我查询一个不存在的记录，那么我将不会得到任何返回。我希望在那个场景中返回false(0)。寻找simplist方法来解释无记录。

SELECT  CASE
            WHEN S.Id IS NOT NULL AND S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1) THEN 1
            ELSE 0
        END AS [Value]

        FROM Sites S

        WHERE S.Id = @SiteId

9 个解决方案

#1

SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END AS [Value]

FROM Sites S

WHERE S.Id = @SiteId and S.Status = 1 AND 
      (S.WebUserId = @WebUserId OR S.AllowUploads = 1)

#2

This is similar to Adam Robinson's, but uses ISNULL instead of COUNT.

这与Adam Robinson的类似，但是使用ISNULL代替COUNT。

SELECT ISNULL(
(SELECT 1 FROM Sites S
WHERE S.Id = @SiteId and S.Status = 1 AND 
      (S.WebUserId = @WebUserId OR S.AllowUploads = 1)), 0)

If the inner query has a matching row, then 1 is returned. The outer query (with ISNULL) then returns this value of 1. If the inner query has no matching row, then it doesn't return anything. The outer query treats this like a NULL, and so the ISNULL ends up returning 0.

如果内部查询有匹配的行，则返回1。然后外部查询(使用ISNULL)返回这个值1。如果内部查询没有匹配行，则不会返回任何内容。外部查询将其视为NULL，因此ISNULL最终返回0。

#3

This might be a dead horse, another way to return 1 row when no rows exist is to UNION another query and display results when non exist in the table.

这可能是无用的，当不存在行时返回一行的另一种方法是联合另一个查询并在表中不存在时显示结果。

SELECT S.Status, COUNT(s.id) AS StatusCount
FROM Sites S
WHERE S.Id = @SiteId
GROUP BY s.Status
UNION ALL --UNION BACK ON TABLE WITH NOT EXISTS
SELECT 'N/A' AS Status, 0 AS StatusCount
WHERE NOT EXISTS (SELECT 1
   FROM Sites S
   WHERE S.Id = @SiteId
)

#4

Something like:

喜欢的东西:

if exists (select top 1 * from Sites S where S.Id IS NOT NULL AND S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1))
    select 1
else
    select 0

#5

I read all the answers here, and it took a while to figure out what was going on. The following is based on the answer by Moe Sisko and some related research

我读了所有的答案，花了一段时间才弄明白是怎么回事。以下是基于Moe Sisko的回答和相关研究

If your SQL query does not return any data there is not a field with a null value so neither ISNULL nor COALESCE will work as you want them to. By using a sub query, the top level query gets a field with a null value, and both ISNULL and COALESCE will work as you want/expect them to.

如果SQL查询不返回任何数据，则没有一个字段具有null值，因此ISNULL和COALESCE都不会按照您的要求工作。通过使用子查询，*查询得到一个具有空值的字段，并且ISNULL和合并将按照您希望的方式工作。

My query

我的查询

select isnull(
 (select ASSIGNMENTM1.NAME
 from dbo.ASSIGNMENTM1
 where ASSIGNMENTM1.NAME = ?)
, 'Nothing Found') as 'ASSIGNMENTM1.NAME'

My query with comments

我的查询和评论

select isnull(
--sub query either returns a value or returns nothing (no value)
 (select ASSIGNMENTM1.NAME
 from dbo.ASSIGNMENTM1
 where ASSIGNMENTM1.NAME = ?)
 --If there is a value it is displayed 
 --If no value, it is perceived as a field with a null value, 
 --so the isnull function can give the desired results
, 'Nothing Found') as 'ASSIGNMENTM1.NAME'

#6

No record matched means no record returned. There's no place for the "value" of 0 to go if no records are found. You could create a crazy UNION query to do what you want but much, much, much better simply to check the number of records in the result set.

没有匹配的记录意味着没有返回记录。如果没有找到任何记录，那么0的“值”就没有位置了。您可以创建一个疯狂的UNION查询来做您想做的事情，但是仅仅检查结果集中的记录数量就更好了。

#7

You only have to replace the WHERE with a LEFT JOIN:

你只需要用左边的连接来代替:

SELECT  CASE
        WHEN S.Id IS NOT NULL AND S.Status = 1 AND ...) THEN 1
        ELSE 0
    END AS [Value]

    FROM (SELECT @SiteId AS Id) R
    LEFT JOIN Sites S ON S.Id = R.Id

This solution allows you to return default values for each column also, for example:

这个解决方案允许您为每个列返回默认值，例如:

SELECT
    CASE WHEN S.Id IS NULL THEN 0 ELSE S.Col1 END AS Col1,
    S.Col2,
    ISNULL(S.Col3, 0) AS Col3
FROM
    (SELECT @Id AS Id) R
    LEFT JOIN Sites S ON S.Id = R.Id AND S.Status = 1 AND ...

#8

What about WITH TIES?

有联系呢?

SELECT TOP 1 WITH TIES tbl1.* FROM 
        (SELECT CASE WHEN S.Id IS NOT NULL AND S.Status = 1 
                      AND (S.WebUserId = @WebUserId OR 
                           S.AllowUploads = 1)
                     THEN 1 
                     ELSE 0 AS [Value]
         FROM Sites S
         WHERE S.Id = @SiteId) as tbl1
ORDER BY tbl1.[Value]

#9

This Might be one way.

这可能是一种方法。

SELECT TOP 1 [Column Name] FROM (SELECT [Column Name] FROM [table]
    WHERE [conditions]
    UNION ALL
    SELECT 0 )A ORDER BY [Column Name] DESC

#1

SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END AS [Value]

FROM Sites S

WHERE S.Id = @SiteId and S.Status = 1 AND 
      (S.WebUserId = @WebUserId OR S.AllowUploads = 1)

#2