Microsoft SQL Server查询改进

Question

是否有机会提高以下查询的速度：

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

如果将params月发送为2016和InvoiceAcc (不需要)，那么Server中的视图是什么？

非常感谢。

Answer 1

根据你的例子你想要这样

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通常是一个糟糕的查询的指示--从设计良好的模型中正确设计的查询很少需要不同。

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

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

Answer 2

你可以试着用枢轴

select 
InvoiceAcc,
CustomerName,
[2016-1]  as [01],
[2016-2]  as [02],
[2016-3]  as [03],
[2016-4]  as [04],
[2016-5]  as [05],
[2016-6]  as [06],
[2016-7]  as [07],
[2016-8]  as [08],
[2016-9]  as [09],
[2016-10] as [10],
[2016-11] as [11],
[2016-12] as [12]
from (
select 
InvoiceAcc,
CustomerName,
[Month],
sum(SalesValue) SalesValue
from dbo.tblsales 
group by InvoiceAcc, CustomerName,[Month]) tb
pivot 
(
sum(SalesValue)
for [Month] in ([2016-1],[2016-2] ,[2016-3],[2016-4],[2016-5],[2016-6],[2016-7],[2016-8],[2016-9],[2016-10],[2016-11],[2016-12])
) pvt