sql - sql server 2008 中的 12 个月销售和最大销售

Question

我有一个查询要从salesdata 表 中找出Material wise 和Month wise 数据

示例

我可以找到以下数据

MaterialNo  Jan   Feb   Mar   Apr   May   Jun   Jul  Aug  Sep  Oct  Nov  Dec  
1           10    15    20    30    40    45    56   85   100  95   42   32   
2           12    20    86    32    45    42    62   74   86   42   84   62
3           15    20    46    54    46    42    19   0    62   75   94   98

我想要 SQL SERVER 2008 R2 中的最后两列(MaxSale、MaxSaleMonth)

MaterialNo  Jan   Feb   Mar   Apr   May   Jun   Jul  Aug  Sep  Oct  Nov  Dec  MaxSale   MaxSaleMonth
1           10    15    20    30    40    45    56   85   100  95   42   32   100       Sep
2           12    20    86    32    45    42    62   74   86   42   84   62   86        Mar
3           15    20    46    54    46    42    19   0    62   75   94   98   98        Dec

请建议我最简单的解决方案。

我的当前查询如下

Select 
MaterialNo,
SUM(CASE WHEN CAST(SalesData.dSalesDate AS DATE) BETWEEN '01-Jan-2015' AND  '31-Jan-2015' THEN SalesData.iQty  ELSE 0 END) AS [Jan],
SUM(CASE WHEN CAST(SalesData.dSalesDate AS DATE) BETWEEN '01-Feb-2015' AND  '28-Feb-2015' THEN SalesData.iQty  ELSE 0 END) AS [Feb],
SUM(CASE WHEN CAST(SalesData.dSalesDate AS DATE) BETWEEN '01-Mar-2015' AND  '31-Mar-2015' THEN SalesData.iQty  ELSE 0 END) AS [Mar],
SUM(CASE WHEN CAST(SalesData.dSalesDate AS DATE) BETWEEN '01-Apr-2015' AND  '30-Apr-2015' THEN SalesData.iQty  ELSE 0 END) AS [Apr]
From SalesData
Group By
MaterialNo

Answer 1

使用 Pivot .. 您可以找到最简单的方法。

select max(month), row_max = max(val)
from yourtable
pivot
(
  salesmonth 
  for col in (C1, C2, C3, C4..c12)
) piv
group by id