如果在 MicrosoftSQL 中没有找到行，则返回一个值

使用 微软版本的 SQL，下面是我的简单查询。如果我查询一个不存在的记录，那么我将得不到任何返回。在这种情况下，我希望返回 false (0)。寻找最简单的方法来解释没有记录。

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
456398 次浏览
小开

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.

小开
最佳答案 
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)
小开

This is similar to Adam Robinson's, but uses ISNULL instead of 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.

小开

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
)
小开

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
小开

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 ...
小开

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

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.

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'
小开

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
小开

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]
小开

@hai-phan's answer using LEFT JOIN is the key, but it might be a bit hard to follow. I had a complicated query that may also return nothing. I just simplified his answer to my need. It's easy to apply to query with many columns.

;WITH CTE AS (
-- SELECT S.Id, ...
-- FROM Sites S WHERE Id = @SiteId
-- EXCEPT SOME CONDITION.
-- Whatever your query is
)
SELECT CTE.* -- If you want something else instead of NULL, use COALESCE.
FROM (SELECT @SiteId AS ID) R
LEFT JOIN CTE ON CTE.Id = R.ID

Update: This answer on SqlServerCentral is the best. It utilizes this feature of MAX - "MAX returns NULL when there is no row to select."

SELECT ISNULL(MAX(value), 0) FROM table WHERE Id = @SiteId
小开 
DECLARE @col int;
select @col = id  FROM site WHERE status = 1;
select coalesce(@col,0);
小开

You should avoid using expensive methods. You don't need any column for TBL2.

SELECT COUNT(*) FROM(
SELECT TOP 1     1 AS CNT  FROM  TBL1
WHERE ColumnValue ='FooDoo') AS TBL2

Or this code:

IF EXISTS (SELECT TOP 1 1 FROM TABLE1 AS T1
WHERE T1.ColumnValue='VooDoo')
SELECT 1
ELSE
SELECT 0
小开

You can do something just like this.

IF EXISTS ( SELECT * FROM TableName WHERE Column=colval)
BEGIN
select
select name ,Id from TableName WHERE Column=colval
END
ELSE
SELECT 'test' as name,0 as Id
小开

My solition is working

can testing by change where 1=2 to where 1=1

select * from (
select col_x,case when count(1) over (partition by 1) =1 then 1 else HIDE end as HIDE from (
select 'test' col_x,1 as HIDE
where 1=2
union
select 'if no rows write here that you want' as col_x,0 as HIDE
) a
) b where HIDE=1
小开

I liked James Jenkins reply with the ISNULL check, but I think he meant IFNULL. ISNULL does not have a second parameter like his syntax, but IFNULL has the second parameter after the expression being checked to substitute if a NULL is found.

小开

I have a union query with 6 queries. They all match off of a number in 1 table. If there is no value in one of the queries it will not show any result.


提交答案