主页 / dba / 问题 / 104898
Accepted
Mike Fal
Asked: 2015-06-24 08:18:53 +0800 CST

NULL 时在表中使用“默认值”

  • 772

我正在构建一个配置表并希望其中一行为默认值。例如，我有一个list包含所有数据库的表和一个msg包含所有返回值的表。在msg表中，我有一行是“alldbs”，如果加入的数据库没有另一行，我希望返回该值。

所以输出应该是：

--LIST TABLE
dbname                                             createdate
-------------------------------------------------- -----------------------
master                                             2015-06-23 10:05:14.363
model                                              2015-06-23 10:05:14.363
msdb                                               2015-06-23 10:05:14.363
tempdb                                             2015-06-23 10:05:14.363
dummy                                              2015-06-23 10:05:14.363

--MSG Table
dbname                                             msgval
-------------------------------------------------- --------------------
alldbs                                             Message 1
dummy                                              Message 2


--DESIRED OUTPUT
dbname                                             msgval
-------------------------------------------------- --------------------
dummy                                              Message 2
master                                             Message 1
model                                              Message 1
msdb                                               Message 1
tempdb                                             Message 1

现在我可以得到结果，但查询很笨拙，似乎应该有更好的方法：

select
    a.dbname
    ,b.msgval
from
    list a
    join msg b on a.dbname = b.dbname
union all
select
    a.dbname
    ,b.msgval
from
    list a
    ,msg b
where
    a.dbname not in (select dbname from msg)
    and b.dbname = 'alldbs'

有没有更有效的方法来编写此查询？

SQLFiddle 链接使用 SQL 2014。

sql-server sql-server-2014

3 个回答

  1. Best Answer

    使用另一个left join：

    select
    a.dbname
    , coalesce(b.msgval, d.msgval) as msgval
from
    list a
    left join msg b on a.dbname = b.dbname
    left join msg d on  b.dbname is null
                    and d.dbname = 'alldbs' ;

    或cross连接（这需要存在'alldbs'一行，否则将返回 0 个结果。）：

    select
    a.dbname
    , coalesce(b.msgval, d.msgval) as msgval
from
    msg d 
    cross join list a 
    left join msg b on a.dbname = b.dbname
where d.dbname = 'alldbs' ;

    sqlfiddle

    两个查询都假设只有一行带有dbname = 'alldbs'. 如果没有，请使用applyAndriyM 提供的版本。

    • 8

  2. 另一种方式（在看到@ypercube 的建议后对我来说不是那么漂亮，但仍然......）：

    SELECT
  l.dbname,
  x.msgval
FROM
  dbo.list AS l
  CROSS APPLY
    (
      SELECT TOP (1)
        m.msgval
      FROM
        dbo.msg AS m
      WHERE
        m.dbname IN (l.dbname, 'alldbs')
      ORDER BY
        CASE m.dbname WHEN 'alldbs' THEN 2 ELSE 1 END ASC
    ) AS x
;
    • 7

  3. 使用子选择获取丢失的消息：

    select a.dbname
    ,  coalesce(b.msgval, (select msgval 
                           from msg c 
                           where dbname = 'alldbs')) as msgval 
from list a
left join msg b 
    on a.dbname = b.dbname
    • 5

相关问题