# 请哪位帮忙实现根据时间段和选择的维度年月日周统计一个产品各个维度的数量怎么实现，

0
[待解决问题]

id   product    dateTime

1    aa            2011-01-12

1    bb           2009-01-12

1    aa            2009-01-12

1    cc           2010-01-12

1    aa            2009-01-12

1    cc           2012-01-12

1    aa            2007-01-12

1.比如查询2006-01-12至2012-09-12根据年来查询各产品的数量

wuxing123 | 菜鸟二级 | 园豆：205

0
```select product,

sum(case when year(datetime)=2006 then 1 else 0 end) as 2006,

sum(case when year(datetime)=2007 then 1 else 0 end) as 2007,

sum(case when year(datetime)=2008 then 1 else 0 end) as 2008,

sum(case when year(datetime)=2009 then 1 else 0 end) as 2009,

sum(case when year(datetime)=2010 then 1 else 0 end) as 2010,

sum(case when year(datetime)=2011 then 1 else 0 end) as 2011,

sum(case when year(datetime)=2012 then 1 else 0 end) as 2012

from tb group by product```

@wuxing123: 照着我的思路做呗,sql查询方法都有了

@飞来飞去: 你还没我的题目意思吗

@飞来飞去: 你还没明白我的题目意思吗

0

declare @sql varchar(8000)

select product, dateYear,count=sum(id) into #temp from ( select  id,   product,    dateYear=year(datetime)  from tb  where dateTime between @Begindate and @Endate ) A group by product, dateYear

set @sql=''

select @sql=@sql+','+ dateYear from #temp group by dateYear

set @sql=stuff(@sql,1,1,'')--去掉首個','

set @sql='select * from #temp pivot (max(count) for dateYear in ('+@sql+'))A'

exec(@sql)

product   2007  2009  2010 2011 2012

aa           1        2       0       1     0

您需要登录以后才能回答，未注册用户请先注册