Microsoft SQL Server查询改进

Maludasek 发表于 Dev

马卢达塞克

有没有机会提高下面的查询速度：

select distinct
    a.InvoiceAcc,
    (select top 1 b.CustomerName 
     from dbo.tblsales as b 
     where b.InvoiceAcc = a.InvoiceAcc),
    (select sum(b.SalesValue) 
     from dbo.tblsales as b 
     where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-1') as [01],
    (select sum(b.SalesValue) 
     from dbo.tblsales as b 
     where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-2') as [02],
    (select sum(b.SalesValue) 
     from dbo.tblsales as b 
     where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-3') as [03],
    (select sum(b.SalesValue) 
     from dbo.tblsales as b 
     where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-4') as [04],
    (select sum(b.SalesValue) 
     from dbo.tblsales as b 
     where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-5') as [05],
    (select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-6') as [06],
    (select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-7') as [07],
    (select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-8') as [08],
    (select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-9') as [09],
    (select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-10') as [10],
    (select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-11') as [11],
    (select sum(b.SalesValue) from dbo.tblsales as b where b.InvoiceAcc = a.InvoiceAcc and b.Month = '2016-12') as [12]
from
    dbo.tblsales as a 
group by 
    a.InvoiceAcc, CustomerName

在SQL Server中将参数月发送为2016年InvoiceAcc（非必需）的视图是什么？

非常感谢。

霍根

根据您的示例，您需要

select 
  InvoiceAcc,
  CustomerName,
  sum(CASE WHEN [Month] = '2016-1' then salesvalue else 0 end) as [01],
  sum(CASE WHEN [Month] = '2016-2' then salesvalue else 0 end) as [02],
  sum(CASE WHEN [Month] = '2016-3' then salesvalue else 0 end) as [03],
  sum(CASE WHEN [Month] = '2016-4' then salesvalue else 0 end) as [04],
  sum(CASE WHEN [Month] = '2016-5' then salesvalue else 0 end) as [05],
  sum(CASE WHEN [Month] = '2016-6' then salesvalue else 0 end) as [06],
  sum(CASE WHEN [Month] = '2016-7' then salesvalue else 0 end) as [07],
  sum(CASE WHEN [Month] = '2016-8' then salesvalue else 0 end) as [08],
  sum(CASE WHEN [Month] = '2016-9' then salesvalue else 0 end) as [09],
  sum(CASE WHEN [Month] = '2016-10' then salesvalue else 0 end) as [10],
  sum(CASE WHEN [Month] = '2016-11' then salesvalue else 0 end) as [11],
  sum(CASE WHEN [Month] = '2016-12' then salesvalue else 0 end) as [12]
from dbo.tblsales 
group by InvoiceAcc, CustomerName

我确实发现测试（例如Month = '2016-1'）很奇怪-我期望像这样的日期字段和测试MONTH(invoiceDate) = 1

我还认为，在sql中使用“ distinct”通常表示查询效果不佳-从设计良好的模型中正确设计的查询很少需要使用“ distinct”。

如果要删除其他名称，最好的方法是：

select 
  InvoiceAcc,
  CustomerName,
  sum(CASE WHEN [Month] = '2016-1' then salesvalue else 0 end) as [01],
  sum(CASE WHEN [Month] = '2016-2' then salesvalue else 0 end) as [02],
  sum(CASE WHEN [Month] = '2016-3' then salesvalue else 0 end) as [03],
  sum(CASE WHEN [Month] = '2016-4' then salesvalue else 0 end) as [04],
  sum(CASE WHEN [Month] = '2016-5' then salesvalue else 0 end) as [05],
  sum(CASE WHEN [Month] = '2016-6' then salesvalue else 0 end) as [06],
  sum(CASE WHEN [Month] = '2016-7' then salesvalue else 0 end) as [07],
  sum(CASE WHEN [Month] = '2016-8' then salesvalue else 0 end) as [08],
  sum(CASE WHEN [Month] = '2016-9' then salesvalue else 0 end) as [09],
  sum(CASE WHEN [Month] = '2016-10' then salesvalue else 0 end) as [10],
  sum(CASE WHEN [Month] = '2016-11' then salesvalue else 0 end) as [11],
  sum(CASE WHEN [Month] = '2016-12' then salesvalue else 0 end) as [12]
from (
  SELECT InvoiceAcc, CustomerName, Month, salesvalue,
         ROW_NUMBER() OVER (PARTITION BY InvoiceAcc ORDER BY CustomerName) AS RN
         FROM tblsales
) x
WHERE RN=1
group by InvoiceAcc, CustomerName