sql - 替换无结果

Question

我有一个这样的查询:

SELECT TV.Descrizione as TipoVers, 
       sum(ImportoVersamento) as ImpTot, 
       count(*) as N,
       month(DataAllibramento) as Mese
FROM PROC_Versamento V
left outer join dbo.PROC_TipoVersamento TV
    on V.IDTipoVersamento = TV.IDTipoVersamento
inner join dbo.PROC_PraticaRiscossione PR 
    on V.IDPraticaRiscossioneAssociata = PR.IDPratica
inner join dbo.DA_Avviso A
    on PR.IDDatiAvviso = A.IDAvviso
where DataAllibramento between '2012-09-08' and '2012-09-17' and  A.IDFornitura = 4
group by V.IDTipoVersamento,month(DataAllibramento),TV.Descrizione
order by V.IDTipoVersamento,month(DataAllibramento)

这个查询必须总是返回一些东西。如果没有产生结果

0 0 0 0

必须返回行。我怎样才能做到这一点。对每个选定字段使用 isnull 没有用。

Answer 1

使用带有一行的派生表，并对其他表/查询执行外部应用。

这是一个带有表变量 @T 的示例代替你真正的 table 。

declare @T table
(
  ID int,
  Grp int
)

select isnull(Q.MaxID, 0) as MaxID,
       isnull(Q.C, 0) as C
from (select 1) as T(X)
  outer apply (
              -- Your query goes here
              select max(ID) as MaxID,
                     count(*) as C
              from @T
              group by Grp
              ) as Q
order by Q.C -- order by goes to the outer query

这将确保您在输出中始终至少有一行。

像这样使用您的查询。

select isnull(Q.TipoVers, '0') as TipoVers, 
       isnull(Q.ImpTot, 0) as ImpTot, 
       isnull(Q.N, 0) as N,
       isnull(Q.Mese, 0) as Mese
from (select 1) as T(X)
  outer apply (
              SELECT TV.Descrizione as TipoVers, 
                     sum(ImportoVersamento) as ImpTot, 
                     count(*) as N,
                     month(DataAllibramento) as Mese,
                     V.IDTipoVersamento
              FROM PROC_Versamento V
              left outer join dbo.PROC_TipoVersamento TV
                  on V.IDTipoVersamento = TV.IDTipoVersamento
              inner join dbo.PROC_PraticaRiscossione PR 
                  on V.IDPraticaRiscossioneAssociata = PR.IDPratica
              inner join dbo.DA_Avviso A
                  on PR.IDDatiAvviso = A.IDAvviso
              where DataAllibramento between '2012-09-08' and '2012-09-17' and  A.IDFornitura = 4
              group by V.IDTipoVersamento,month(DataAllibramento),TV.Descrizione
              ) as Q
order by Q.IDTipoVersamento, Q.Mese