给定一个SQL Server实例,假设有一个名为Configuration的表,该表具有三列:IDNameDataName应该没有重复的行。现在想象您要选择为特定配置存储的数据,但是如果该配置在表中没有行,那么您只需要选择一个默认值。

这就是我一直在使用的值:

我试图尝试一种缩短EXISTS()调用的方法,使代码更清晰一些,消除重复的代码,甚至可能加快速度,这是到目前为止我能找到的: >
SELECT CASE
         WHEN EXISTS(SELECT 1
                     FROM   Configuration
                     WHERE  Name = 'NameOfConfiguration')
           THEN (SELECT Data
                 FROM   Configuration
                 WHERE  Name = 'NameOfConfiguration')
         ELSE 'Default Value'
       END 


后面的代码片段一般是处理这类问题的正确方法吗?不幸的是,SQL Server需要将聚合函数调用或GROUP BY子句应用于后者,而SQL Server中显然不存在FIRST()。我没有将对MIN()的调用限制为任何类型的数据类型。有没有更好的方法可以写呢?

#1 楼

我能想到的较短的查询是

SELECT COALESCE(MAX(Data), 'Default Value')
FROM   Configuration
WHERE  Name = 'NameOfConfiguration';


如果存在该配置,它将接受它。一个MAX值(没有的最大值是NULL)将被合并为默认值


还有一种更通用的方法可以处理Martin Smith的评论,但是更为冗长(使用配置名称唯一的假设)。

WITH Param AS (
  SELECT ID, Data
  FROM   Configuration
  WHERE  Name = 'NameOfConfiguration'
  UNION ALL
  SELECT NULL, 'Default Value'
)
SELECT TOP 1
       Data
FROM   Param
ORDER BY ID DESC


默认值的NULL设置为ID,以避免使用幻数。
在BOL中:NULL值被视为可能的最低值。 。同样在一般情况下,默认值对于配置值的数据类型将需要为有效值,否则NULL将返回转换错误。

评论


\ $ \ begingroup \ $
尽管它与问题中的最终查询基本相同,但有一个额外的限制,即它不能区分存储在表中的NULL行和根本没有存储的行。仍然存在MIN / MAX不适用于所有数据类型的问题。
\ $ \ endgroup \ $
–马丁·史密斯
2014年12月20日13:08

#2 楼

由于SQL是为处理数据集而设计的,因此查询感到很痛苦。您正在像普通编程语言一样使用SQL,试图将表转换为标量,并在查询中嵌入特殊值,而不是将其存储为数据。

我建议您创建并填充一个ConfigurationDefaults表与您的Configuration表类似。 />
CREATE TABLE Configuration
( ID INTEGER IDENTITY PRIMARY KEY
, Name VARCHAR(123) UNIQUE
, Data VARCHAR(123)
);

CREATE TABLE ConfigurationDefaults
( Name VARCHAR(123) PRIMARY KEY
, Data VARCHAR(123)
);

INSERT INTO ConfigurationDefaults VALUES ('NameOfConfiguration', 'Default Value');


一旦基础架构就绪,查询就很容易读写!

CREATE VIEW EffectiveConfiguration AS
    SELECT COALESCE(Cfg.Name, Def.Name) AS Name
         , CASE WHEN Cfg.Name IS NULL THEN Def.Data ELSE Cfg.Data END AS Data
        FROM Configuration AS Cfg
            FULL OUTER JOIN ConfigurationDefaults AS Def
                ON Cfg.Name = Def.Name;


#3 楼

如果具有静态默认值,则可以将其分配给变量,并在发现行时有条件地覆盖。第一个选择返回data1,第二个选择返回默认值,因为该名称不在表中。

create table #cfg (name varchar(32) primary key, id int, data varchar(32))
insert #cfg values ('name1', null, 'data1');
insert #cfg values ('name2', null, 'data2');
go

declare @data varchar(32)
set @data='defaultvalue';
select @data=data
from #cfg
where name='name1';
select @data;
go

declare @data varchar(32)
set @data='defaultvalue';
select @data=data
from #cfg
where name='name3';
select @data;
go


#4 楼

为什么不从表中选择Name等于NameOfConfiguration的字段,然后使用isnull呢?比使用EXIST语句更直接

#5 楼

我认为您尝试的第一个查询会更好,因为它不需要聚合任何内容。我相信这就是我会采取的方式。我只是将其重新格式化,以便我们可以更轻松地阅读。

SELECT CASE 
    WHEN EXISTS(
        SELECT 1 
        FROM Configuration 
        WHERE Name = 'NameOfConfiguration'
    )
    THEN (
        SELECT Data 
        FROM Configuration 
        WHERE Name = 'NameOfConfiguration'
    ) 
    ELSE 'Default Value' 
END



尽管还有其他一些小改进。您需要为该列添加别名。否则,您将以一个英里长的名字结尾。 (取决于您的RDBMS。我知道SQL Server将使用表达式字符串来命名它。)

我要做的第二件事是声明'NameOfConfiguration'的变量。您在两个地方使用了相同的参数,因此声明一个变量很有意义。如果您应该决定将此查询包装到存储过程中,这也将使事情变得更容易。

#6 楼

显然,这是您要使用ISNULL的情况。但是为此,如果配置名称不存在,则需要返回一行。因此,这意味着您需要一个左外部联接。然后,使用所需的值创建一个虚拟临时表,并在实际数据上保留外部联接。

select
case when c.Name is null then 'Default Value' else c.Data end [Data]
from
(select 'NameOfConfiguration' [Name]) Target
left outer join Configuration c on c.[Name] = Target.[Name]


评论


\ $ \ begingroup \ $
如果您对答案不满意,请解释一下原因。
\ $ \ endgroup \ $
–Paschover
2014年12月19日23:10

\ $ \ begingroup \ $
不能将Select语句嵌套在isull语句中吗?
\ $ \ endgroup \ $
–马拉奇♦
2014年12月20日在21:13

\ $ \ begingroup \ $
不,您需要为此返回一行。
\ $ \ endgroup \ $
–Paschover
2014年12月21日下午16:21

\ $ \ begingroup \ $
我找到了一种方法,请参阅我的答案
\ $ \ endgroup \ $
–马拉奇♦
2014-12-22 14:31

\ $ \ begingroup \ $
@Malachi:我无法评论您的答案,因此我将在此处发布。您不需要查询中的前1个,因为在表上应该有一个使配置名称唯一的约束。
\ $ \ endgroup \ $
–Paschover
2014年12月22日17:21

#7 楼

如果您使用的是sql开发人员,那么它将给您错误:


ORA-00923:找不到期望的FROM关键字
00923。00000-“找不到FROM关键字在期望的位置“


在这种情况下,您需要最后添加from dual

SELECT CASE 
    WHEN EXISTS(
        SELECT 1 
        FROM your_table 
        WHERE your_item= 'XXX' 
    )
    THEN (
        SELECT some_value
        FROM your_table 
        WHERE your_item= 'XXX' 
    ) 
    ELSE '0' END from dual;