我怎样才能制作“月"?Sql中的列? [英] How can I make "month" columns in Sql?

问题描述

我有一组看起来像这样的数据(非常简化):

I've got a set of data that looks something like this (VERY simplified):

productId    Qty   dateOrdered
---------    ---   -----------
       1       2    10/10/2008
       1       1    11/10/2008
       1       2    10/10/2009
       2       3    10/12/2009
       1       1    10/15/2009
       2       2    11/15/2009

除此之外，我们正在尝试创建一个查询以获得类似的信息:

Out of this, we're trying to create a query to get something like:

productId  Year  Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
---------  ----  --- --- --- --- --- --- --- --- --- --- --- ---
        1  2008    0   0   0   0   0   0   0   0   0   2   1   0
        1  2009    0   0   0   0   0   0   0   0   0   3   0   0
        2  2009    0   0   0   0   0   0   0   0   0   3   2   0

按照我现在的做法，我进行了 12 次选择，每个月一次，并将它们放在临时表中.然后我做了一个巨大的连接.一切正常，但这个家伙很慢.

The way I'm doing this now, I'm doing 12 selects, one for each month, and putting those in temp tables. I then do a giant join. Everything works, but this guy is dog slow.

推荐答案

select productId, Year(dateOrdered) Year
  ,isnull(sum(case when month(dateOrdered) = 1 then Qty end), 0) Jan
  ,isnull(sum(case when month(dateOrdered) = 2 then Qty end), 0) Feb 
  ,isnull(sum(case when month(dateOrdered) = 3 then Qty end), 0) Mar
  ,isnull(sum(case when month(dateOrdered) = 4 then Qty end), 0) Apr
  ,isnull(sum(case when month(dateOrdered) = 5 then Qty end), 0) May
  ,isnull(sum(case when month(dateOrdered) = 6 then Qty end), 0) Jun
  ,isnull(sum(case when month(dateOrdered) = 7 then Qty end), 0) Jul
  ,isnull(sum(case when month(dateOrdered) = 8 then Qty end), 0) Aug
  ,isnull(sum(case when month(dateOrdered) = 9 then Qty end), 0) Sep
  ,isnull(sum(case when month(dateOrdered) = 10 then Qty end), 0) Oct
  ,isnull(sum(case when month(dateOrdered) = 11 then Qty end), 0) Nov
  ,isnull(sum(case when month(dateOrdered) = 12 then Qty end), 0) Dec
from Table1
group by productId, Year(dateOrdered)

SQL 小提琴

这篇关于我怎样才能制作“月"?Sql中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！