SQL 如果没有返回任何行，请执行此操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3702576/
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
SQL if no rows are returned do this
提问by user380432
I have a select statement and I want to say if this select statement does not return any rows then put a '' in every cell. How do I do this?
我有一个 select 语句，我想说如果这个 select 语句不返回任何行，那么在每个单元格中放一个 '' 。我该怎么做呢？
采纳答案by bobs
It sounds like you're still not getting all the rows you want. True? I think @Joe Sefanelli provides an important part to your solution, and then mentions that you need to change INNER to LEFT joins.
听起来你仍然没有得到你想要的所有行。真的？我认为@Joe Sefanelli 为您的解决方案提供了一个重要的部分，然后提到您需要将 INNER 更改为 LEFT 连接。
So, you say you want to display all units in your units list. And, if there's no data for a unit, then display the unit and blanks for the data that doesn't exist.
因此，您说要在单位列表中显示所有单位。并且，如果一个单位没有数据，则显示该单位并为不存在的数据显示空白。
Here's a possible solution. Change your FROM clause to the following:
这是一个可能的解决方案。将您的 FROM 子句更改为以下内容：
FROM [dbo].[Unit] u
LEFT OUTER JOIN
(
SELECT *
FROM [dbo].[IUA] i
JOIN [dbo].[Reports] r ON r.[Report_ID] = i.[Report_ID]
JOIN [dbo].[State] s ON i.[St_ID] = s.[St_Id]
WHERE r.[Account] = [dbo].[fn_Get_PortalUser_AccountNumber](11-11)
AND r.[Rpt_Period] = '2126'
AND r.[RptName] = 'tfd'
AND r.[Type] = 'h'
) ir ON ir.[Unit_ID] = u.[Unit_ID]
LEFT JOIN [dbo].[UnitType] ut ON u.[UnitType] = ut.[UnitType]
WHERE u.[Unit] IN (SELECT [VALUE]
FROM dbo.udf_GenerateVarcharTableFromStringList(@Units, ','))
;
With this change you will get a list of units that are in the @Units list. The left outer joins will include data associated with each unit, but will not exclude units if there is no associated data.
通过此更改，您将获得@Units 列表中的单位列表。左外连接将包括与每个单元关联的数据，但如果没有关联数据，则不会排除单元。
回答by tenfour
select a, b, c from t
if @@rowcount = 0
select '' as a, '' as b, '' as c
But make sure you understand that ''may have a different datatype than columns a, b, and c.
但要确保你明白，''可能有不同的数据类型比列a，b和c。
回答by Sachin Shanbhag
Try this -
尝试这个 -
IF NOT EXISTS ( SELECT 'x' FROM <TABLE> .... )
BEGIN
-- Your logic goes here
END
回答by Rawheiser
Put your blank row select at the bottom of a union
将您的空白行选择放在联合的底部
select x.JobName , x.Description
from MasterJobList x
where x.IsCycleJob = 1
union all
select "" , ""
from MasterJobList x
where not exists
(
select 1
from MasterJobList x
where x.IsCycleJob = 1
)
回答by Joe Stefanelli
Based on the posted code, I think you're looking to blank out the columns from the UnitType table as that's the only one you're left-joining to. In that case use
根据发布的代码，我认为您希望将 UnitType 表中的列清空，因为这是您唯一要加入的列。在这种情况下使用
ISNULL(ut.[Description], '') AS UnitType
回答by lols
select top 1 isnull(max(col2),' ') as noNullCol from table1 where col1='x'
max returns a nullwhere not have rows then isnullfunction returns ' 'instead a nullvalue
max 返回一个null没有行的地方然后isnull函数返回' '一个null值
回答by some_yahoo
Here's an example I use for a single column - it's easy and only produces a blank row if there are no matches in the dataset.
这是我用于单列的示例 - 如果数据集中没有匹配项，它很容易并且只生成一个空白行。
select Company from customer where customer=@Company
union
select '' where not exists (select 1 from customer where customer=@Company)
This creates a row of blank(s) when there are no matches.
当没有匹配项时，这会创建一行空白。
