如何使用多个“ CASE”改进此SQL Server查询？

mggSoft 发表于 Dev

mggSoft

我对SQL Server 2008 R2有以下SQL查询：

Declare @fechaDesde DateTime
Declare @fechaHasta DateTime

set @fechaDesde = '01/01/2014 00:00:00.000'
set @fechaHasta = '31/12/2014 23:59:59.999'

Select Cuenta, isnull(sum(SaldoDebe), 0) as SumaDebe, 
    isnull(sum(SaldoHaber), 0) as SumaHaber,
    isnull(sum(SaldoDebe01), 0) as SumaDebe01,
    isnull(sum(SaldoDebe02), 0) as SumaDebe02,
    isnull(sum(SaldoDebe03), 0) as SumaDebe03,
    isnull(sum(SaldoDebe04), 0) as SumaDebe04,
    isnull(sum(SaldoDebe05), 0) as SumaDebe05,
    isnull(sum(SaldoDebe06), 0) as SumaDebe06,
    isnull(sum(SaldoDebe07), 0) as SumaDebe07,
    isnull(sum(SaldoDebe08), 0) as SumaDebe08,
    isnull(sum(SaldoDebe09), 0) as SumaDebe09,
    isnull(sum(SaldoDebe10), 0) as SumaDebe10,
    isnull(sum(SaldoDebe11), 0) as SumaDebe11,
    isnull(sum(SaldoDebe12), 0) as SumaDebe12,
    isnull(sum(SaldoHaber01), 0) as SumaHaber01,
    isnull(sum(SaldoHaber02), 0) as SumaHaber02,
    isnull(sum(SaldoHaber03), 0) as SumaHaber03,
    isnull(sum(SaldoHaber04), 0) as SumaHaber04,
    isnull(sum(SaldoHaber05), 0) as SumaHaber05,
    isnull(sum(SaldoHaber06), 0) as SumaHaber06,
    isnull(sum(SaldoHaber07), 0) as SumaHaber07,
    isnull(sum(SaldoHaber08), 0) as SumaHaber08,
    isnull(sum(SaldoHaber09), 0) as SumaHaber09,
    isnull(sum(SaldoHaber10), 0) as SumaHaber10,
    isnull(sum(SaldoHaber11), 0) as SumaHaber11,
    isnull(sum(SaldoHaber12), 0) as SumaHaber12

From(
      Select c.Código as Cuenta,
      case When d.Debe_Haber = 'D' then d.Importe end as SaldoDebe,
      case When d.Debe_Haber = 'H' then d.Importe end as SaldoHaber,

      case When d.Debe_Haber = 'D' and Month(fecha) = 1 
           then d.Importe end as SaldoDebe01,
      case When d.Debe_Haber = 'D' and Month(fecha) = 2 
           then d.Importe end as SaldoDebe02,
      case When d.Debe_Haber = 'D' and Month(fecha) = 3 
           then d.Importe end as SaldoDebe03,
      case When d.Debe_Haber = 'D' and Month(fecha) = 4 
           then d.Importe end as SaldoDebe04,
      case When d.Debe_Haber = 'D' and Month(fecha) = 5 
           then d.Importe end as SaldoDebe05,
      case When d.Debe_Haber = 'D' and Month(fecha) = 6
           then d.Importe end as SaldoDebe06,
      case When d.Debe_Haber = 'D' and Month(fecha) = 7 
           then d.Importe end as SaldoDebe07,
      case When d.Debe_Haber = 'D' and Month(fecha) = 8 
           then d.Importe end as SaldoDebe08,
      case When d.Debe_Haber = 'D' and Month(fecha) = 9 
           then d.Importe end as SaldoDebe09,
      case When d.Debe_Haber = 'D' and Month(fecha) = 10 
           then d.Importe end as SaldoDebe10,
      case When d.Debe_Haber = 'D' and Month(fecha) = 11 
           then d.Importe end as SaldoDebe11,
      case When d.Debe_Haber = 'D' and Month(fecha) = 12 
           then d.Importe end as SaldoDebe12,
      case When d.Debe_Haber = 'H' and Month(fecha) = 1 
           then d.Importe end as SaldoHaber01,
      case When d.Debe_Haber = 'H' and Month(fecha) = 2 
           then d.Importe end as SaldoHaber02,
      case When d.Debe_Haber = 'H' and Month(fecha) = 3 
           then d.Importe end as SaldoHaber03,
      case When d.Debe_Haber = 'H' and Month(fecha) = 4 
           then d.Importe end as SaldoHaber04,
      case When d.Debe_Haber = 'H' and Month(fecha) = 5 
           then d.Importe end as SaldoHaber05,
      case When d.Debe_Haber = 'H' and Month(fecha) = 6 
           then d.Importe end as SaldoHaber06,
      case When d.Debe_Haber = 'H' and Month(fecha) = 7 
           then d.Importe end as SaldoHaber07,
      case When d.Debe_Haber = 'H' and Month(fecha) = 8 
           then d.Importe end as SaldoHaber08,
      case When d.Debe_Haber = 'H' and Month(fecha) = 9 
           then d.Importe end as SaldoHaber09,
      case When d.Debe_Haber = 'H' and Month(fecha) = 10 
           then d.Importe end as SaldoHaber10,
      case When d.Debe_Haber = 'H' and Month(fecha) = 11 
           then d.Importe end as SaldoHaber11,
      case When d.Debe_Haber = 'H' and Month(fecha) = 12 
           then d.Importe end as SaldoHaber12

    From Cuentas as c inner join Diario as d on c.Código = d.Cuenta
    Where d.Fecha >= @fechaDesde and d.Fecha <= @fechaHasta
    ) as table1
group by Cuenta
order by Cuenta

...

有两个表：Cuentas和Diario。在表Diario中，我保存了帐户的移动。这是表格：

帐目

它有两个字段和300000行：Código和Nombre。它包含表Diario中使用的帐户

日记

包含“ Cuentas”表帐户之间的货币变动。他的结构是

    [Apunte] [int] NOT NULL, --Identity
    [Fecha] [datetime] NOT NULL,
    [Concepto] [nvarchar](255) NULL,
    [Cuenta] [nvarchar](9) NULL,
    [Importe] [float] NULL,
    [Debe_Haber] [nvarchar](1) NULL,
CONSTRAINT [PK_Diario] PRIMARY KEY CLUSTERED 
(
    [Apunte] ASC
)



      Cuenta    Concepto    Importe Debe_Haber  Fecha
 ----------------------------------------------------------------------------
    572000006   C/Ef.A2003313E01/01-572000006   123,52  H 01/02/14
    433000077   C/Ef.A2003326E01/01-572000006   21,84   D 01/03/14
    572000006   C/Ef.A2003326E01/01-572000006   21,84   H 01/03/14
    430000754   C/Ef.A2003503E01/01-572000006   54,83   D 11/04/14
    572000006   C/Ef.A2003503E01/01-572000006   54,83   H 12/05/14
    430000807   C/Ef.F2030395E03/03-572000006   50,61   D 22/05/14
    572000006   C/Ef.F2030395E03/03-572000006   50,61   H 23/08/14
    430000497   C/Ef.F2034038E01/01-572000006   581,62  D 05/09/14
    572000006   C/Ef.F2034038E01/01-572000006   581,62  H 06/09/14

Fecha是一个DateTime字段。我已经包含了索引：

CREATE NONCLUSTERED INDEX [<IX_Diario_Fecha>]
ON [dbo].[Diario] ([Fecha])
INCLUDE ([Cuenta],[Importe],[Debe_Haber])

我的查询需要3/4秒，我需要对其进行改进以更快地获得结果。

递归的

试试这个更新的查询，我删除了多个isnull并添加else 0了以防处理空值。

DECLARE @fechaDesde DATETIME
DECLARE @fechaHasta DATETIME

SET @fechaDesde = '01/01/2014 00:00:00.000'
SET @fechaHasta = '31/12/2014 23:59:59.999'

Select Cuenta, sum(SaldoDebe)as SumaDebe, sum(SaldoHaber)as SumaHaber,
sum(SaldoDebe01)as SumaDebe01,
sum(SaldoDebe02)as SumaDebe02,
sum(SaldoDebe03)as SumaDebe03,
sum(SaldoDebe04)as SumaDebe04,
sum(SaldoDebe05)as SumaDebe05,
sum(SaldoDebe06)as SumaDebe06,
sum(SaldoDebe07)as SumaDebe07,
sum(SaldoDebe08)as SumaDebe08,
sum(SaldoDebe09)as SumaDebe09,
sum(SaldoDebe10)as SumaDebe10,
sum(SaldoDebe11)as SumaDebe11,
sum(SaldoDebe12)as SumaDebe12,

sum(SaldoHaber01)as SumaHaber01,
sum(SaldoHaber02)as SumaHaber02,
sum(SaldoHaber03)as SumaHaber03,
sum(SaldoHaber04)as SumaHaber04,
sum(SaldoHaber05)as SumaHaber05,
sum(SaldoHaber06)as SumaHaber06,
sum(SaldoHaber07)as SumaHaber07,
sum(SaldoHaber08)as SumaHaber08,
sum(SaldoHaber09)as SumaHaber09,
sum(SaldoHaber10)as SumaHaber10,
sum(SaldoHaber11)as SumaHaber11,
sum(SaldoHaber12)as SumaHaber12

From(
Select c.Código as Cuenta,
case When d.Debe_Haber = 'D' then d.Importe else 0 end as SaldoDebe,
case When d.Debe_Haber = 'H' then d.Importe else 0 end as SaldoHaber,

case When d.Debe_Haber = 'D' and Month(fecha) = 1 then d.Importe else 0 end as SaldoDebe01,
case When d.Debe_Haber = 'D' and Month(fecha) = 2 then d.Importe else 0 end as SaldoDebe02,
case When d.Debe_Haber = 'D' and Month(fecha) = 3 then d.Importe else 0 end as SaldoDebe03,
case When d.Debe_Haber = 'D' and Month(fecha) = 4 then d.Importe else 0 end as SaldoDebe04,
case When d.Debe_Haber = 'D' and Month(fecha) = 5 then d.Importe else 0 end as SaldoDebe05,
case When d.Debe_Haber = 'D' and Month(fecha) = 6 then d.Importe else 0 end as SaldoDebe06,
case When d.Debe_Haber = 'D' and Month(fecha) = 7 then d.Importe else 0 end as SaldoDebe07,
case When d.Debe_Haber = 'D' and Month(fecha) = 8 then d.Importe else 0 end as SaldoDebe08,
case When d.Debe_Haber = 'D' and Month(fecha) = 9 then d.Importe else 0 end as SaldoDebe09,
case When d.Debe_Haber = 'D' and Month(fecha) = 10 then d.Importe else 0 end as SaldoDebe10,
case When d.Debe_Haber = 'D' and Month(fecha) = 11 then d.Importe else 0 end as SaldoDebe11,
case When d.Debe_Haber = 'D' and Month(fecha) = 12 then d.Importe else 0 end as SaldoDebe12,

case When d.Debe_Haber = 'H' and Month(fecha) = 1 then d.Importe else 0 end as SaldoHaber01,
case When d.Debe_Haber = 'H' and Month(fecha) = 2 then d.Importe else 0 end as SaldoHaber02,
case When d.Debe_Haber = 'H' and Month(fecha) = 3 then d.Importe else 0 end as SaldoHaber03,
case When d.Debe_Haber = 'H' and Month(fecha) = 4 then d.Importe else 0 end as SaldoHaber04,
case When d.Debe_Haber = 'H' and Month(fecha) = 5 then d.Importe else 0 end as SaldoHaber05,
case When d.Debe_Haber = 'H' and Month(fecha) = 6 then d.Importe else 0 end as SaldoHaber06,
case When d.Debe_Haber = 'H' and Month(fecha) = 7 then d.Importe else 0 end as SaldoHaber07,
case When d.Debe_Haber = 'H' and Month(fecha) = 8 then d.Importe else 0 end as SaldoHaber08,
case When d.Debe_Haber = 'H' and Month(fecha) = 9 then d.Importe else 0 end as SaldoHaber09,
case When d.Debe_Haber = 'H' and Month(fecha) = 10 then d.Importe else 0 end as SaldoHaber10,
case When d.Debe_Haber = 'H' and Month(fecha) = 11 then d.Importe else 0 end as SaldoHaber11,
case When d.Debe_Haber = 'H' and Month(fecha) = 12 then d.Importe else 0 end as SaldoHaber12

From Cuentas as c 
inner join 
(select distinct [Fecha],
              [Cuenta],
              isnull([Importe],0) as [Importe],
              [Debe_Haber] from Diario) as d 
on c.Código = d.Cuenta
Where d.Fecha >= @fechaDesde and d.Fecha <= @fechaHasta
) as table1
group by Cuenta
order by Cuenta



CREATE NONCLUSTERED INDEX [<IX_Diario_Fecha>]
ON [dbo].[Diario] ([Fecha])
INCLUDE ([Cuenta],[Importe],[Debe_Haber])