现在有一个表,我想通过不同的时间段进行查询统计这个表中的每个产品在不同时间段的金额和,组成一张表:例如组成以下表:
产品名 3月-4月金额 6-7金额 11-12金额....
铅笔 0 20 13
钢笔 50 80 34
尺子 60 97 120
....(注,有些时间段没有这个产品比如铅笔在3月-4月份就没有进货金额 故定为0,上续时间段的划分有不确定时间段可能有N个)望各位指点,谢了.
产品名 3月-4月金额 6-7金额 11-12金额....
铅笔 0 20 13
钢笔 50 80 34
尺子 60 97 120
....(注,有些时间段没有这个产品比如铅笔在3月-4月份就没有进货金额 故定为0,上续时间段的划分有不确定时间段可能有N个)望各位指点,谢了.
2.按产品按月统计各产品的月汇总数据
3.用CASE ...WHEN子句把数据转换成以下形式
product month1 month2 month3 ....select productname,case month when 1 then hj as month1,case month when 2 then hj as month2,...,case month12 when 12 then hj as month12 from table; 4.select (month1+month2) as '1月--2月金额',(month3+month4) as '3月-4月金额',...即可
select 产品名称,
case when month(CreateTime) <=3 then count(*) end as month01 ,
case when month(CreateTime) >3 and month(CreateTime) <=6 then count(*) end as month02 ,
case when month(CreateTime) >6 and month(CreateTime) <=9 then count(*) end as month03 ,
case when month(CreateTime) >9 and month(CreateTime) <=12 then count(*) end as month04
from [UserProfile]group by 产品名称,month(CreateTime)order by 产品名称实在没脸放出来。。
select aaa.roleid,
sum(month01) as 第一季,
sum(month02) as 第二季,
sum(month03) as 第三季,
sum(month04) as 第四季
from
(
select RoleId,
case when month(CreateTime) <=3 then count(*) end as month01 ,
case when month(CreateTime) >3 and month(CreateTime) <=6 then count(*) end as month02 ,
case when month(CreateTime) >6 and month(CreateTime) <=9 then count(*) end as month03 ,
case when month(CreateTime) >9 and month(CreateTime) <=12 then count(*) end as month04
from [UserProfile]group by RoleId,month(CreateTime)) as aaa
group by aaa.roleid