主页 / dba / 问题 / 343234
Accepted
adriano
Asked: 2024-10-26 04:20:34 +0800 CST

从 T-SQL 查询输出中删除 NULL

  • 772

在网上找到了这个漂亮的查询（不记得要感谢谁了），并尝试修改它，以便没有值的 backup_finish_date 不会输出为 NULL，但一直没能弄明白。这个查询完全符合我的要求（我自己也学会了），唯一的例外是输出 NULL。我还想把它格式化为 dd-mm-yyyy hh:mm

SELECT  name ,
        recovery_model_desc as 'Recovery Model' ,
        state_desc as 'State',
        d AS 'Last Full Backup' ,
        i AS 'Last Differential Backup' ,
        l AS 'Last log Backup'
FROM    ( SELECT    db.name ,
                    db.state_desc ,
                    db.recovery_model_desc ,
                    type ,
                    backup_finish_date
          FROM      master.sys.databases db
                    LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
        ) AS Sourcetable
    PIVOT
        ( MAX(backup_finish_date)  FOR type IN ( D, I, L ) ) AS MostRecentBackup
   WHERE (name NOT IN ('master','msdb','model','tempdb'))
t-sql

1 个回答

  1. Best Answer

    大概

    鉴于有限的信息和描述，我猜这是您正在寻找的内容：

    SELECT
    M.name,
    [Recovery Model] = 
        M.recovery_model_desc,
    [State] = 
        M.state_desc,
    [Last Full Backup] = 
        FORMAT(ISNULL(M.D, '19000101'), 'dd-MM-yyyy hh:mm'),
    [Last Differential Backup] = 
        FORMAT(ISNULL(M.I, '19000101'), 'dd-MM-yyyy hh:mm'),
    [Last log Backup] = 
        FORMAT(ISNULL(M.L, '19000101'), 'dd-MM-yyyy hh:mm')
FROM
(
    SELECT
        db.name,
        db.state_desc,
        db.recovery_model_desc,
        a.type,
        a.backup_finish_date
    FROM master.sys.databases AS db
    LEFT OUTER JOIN msdb.dbo.backupset AS a
      ON a.database_name = db.name
) AS Sourcetable
PIVOT
(
    MAX(backup_finish_date)
    FOR type IN
    (
        D,
        I,
        L
    )
) AS M --ostRecentBackup
WHERE name NOT IN
(
    N'master',
    N'msdb',
    N'model',
    N'tempdb'
);
    • 3

相关问题