有个笨方法就是 select (select sum(price) from 表名 where 日期 between (2010-01-01,2010-01-31)) as 一月,.和前面一样...二月,....三月.. from 表名
SELECT Convert(varchar(7),日期,121) as '年月',sum(销售额) from 表 group by Convert(varchar(7),日期,121)
declare @tb table([产品] nvarchar(1),[销售时间] Datetime,[单价] int,[数量] int) Insert @tb select N'a','2010-01-01',10,80 union all select N'a','2010-01-01',10,80 union all select N'a','2010-01-01',12,85 union all select N'a','2010-01-05',12,85 union all select N'a','2010-02-01',10,82 union all select N'a','2010-02-02',10,83 union all select N'a','2010-03-05',12,80 union all select N'b','2010-01-01',20,90 union all select N'b','2010-01-01',22,95 union all select N'b','2010-02-01',20,92 union all select N'b','2010-02-02',20,93 union all select N'b','2010-03-05',21,90 union all select N'c','2010-01-01',30,70 union all select N'c','2010-01-01',32,75 union all select N'c','2010-02-01',30,72 union all select N'c','2010-02-02',30,73 union all select N'c','2010-03-05',31,70declare @dt datetime set @dt ='2010-01-01' select 产品,单价,sum(case when datediff(day,[销售时间],@dt)=0 then 数量 else 0 end) [日统计], sum(case when datediff(month,[销售时间],@dt)=0 then 数量 else 0 end) [月统计] from @tb where [销售时间]=@dt group by 产品,单价
统计一年的如下 select datepart(yy,cdate) as '月份',sum(cmoney) from consumption group by datepart(yy,cdate)
我帮你写好了 你看看 select y.year, sum(一月) as '一月', sum(二月) as '二月', sum(三月) as '三月', sum(四月) as '四月', sum(五月) as '五月', sum(六月) as '六月', sum(七月) as '七月', sum(八月) as '八月', sum(九月) as '九月', sum(十月) as '十月', sum(十一月) as '十一月', sum(十二月) as '十二月' from( select m.year, (case when (m.month = 1) then m.total else 0 end) as '一月', (case when (m.month = 2) then m.total else 0 end) as '二月', (case when (m.month = 3) then m.total else 0 end) as '三月', (case when (m.month = 4) then m.total else 0 end) as '四月', (case when (m.month = 5) then m.total else 0 end) as '五月', (case when (m.month = 6) then m.total else 0 end) as '六月', (case when (m.month = 7) then m.total else 0 end) as '七月', (case when (m.month = 8) then m.total else 0 end) as '八月', (case when (m.month = 9) then m.total else 0 end) as '九月', (case when (m.month = 10) then m.total else 0 end) as '十月', (case when (m.month = 11) then m.total else 0 end) as '十一月', (case when (m.month = 12) then m.total else 0 end) as '十二月' from (select sum(FeedPrice) as total,datepart(year,FeedData) as year ,datepart(month,FeedData) as month from FeedBack group by datepart(year,FeedData),datepart(month,FeedData))m) y group by y.year
沿用10楼的例子 修改了下算法看看是否能满足楼主的需求create table #t ([产品] nvarchar(1),[销售时间] Datetime,[单价] int,[数量] int)Insert #t select N'a','2010-01-01',10,80 union all select N'a','2010-01-01',10,80 union all select N'a','2010-01-01',12,85 union all select N'a','2010-01-05',12,85 union all select N'a','2010-02-01',10,82 union all select N'a','2010-02-02',10,83 union all select N'a','2010-03-05',12,80 union all select N'b','2010-01-01',20,90 union all select N'b','2010-01-01',22,95 union all select N'b','2010-02-01',20,92 union all select N'b','2010-02-02',20,93 union all select N'b','2010-03-05',21,90 union all select N'c','2010-01-01',30,70 union all select N'c','2010-01-01',32,75 union all select N'c','2010-02-01',30,72 union all select N'c','2010-02-02',30,73 union all select N'c','2010-03-02',30,73 union all select N'c','2010-04-02',30,73 union all select N'c','2010-05-02',30,73 union all select N'c','2010-06-02',30,73 union all select N'c','2010-07-02',30,73 union all select N'c','2010-08-02',30,73 union all select N'c','2010-09-02',30,73 union all select N'c','2010-10-02',30,73 union all select N'b','2010-07-02',20,93 union all select N'b','2010-09-05',21,90 union all select N'c','2010-11-01',30,70 union all select N'c','2010-11-02',30,73 union all select N'c','2010-12-05',31,70select sal_year as sal_year, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] from (select [数量]*[单价] as sal,datepart(month,[销售时间]) as sal_month,datepart(year,[销售时间]) as sal_year from #t)T pivot ( sum(sal) for sal_month in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) )P drop table #t
select datepart(yyyy,cdate) as '年份',datepart(mm,cdate) as '月份',sum(cmoney) from consumption group by datepart(yyyy,cdate),datepart(mm,cdate) having datepart(yyyy,cdate)=2010
select 年份, Max(case when [月份]=1 then total end ) '一月', Max(case when [月份]=2 then total end ) '二月', ... from(select datepart(yyyy,cdate) as '年份',datepart(mm,cdate) as '月份',sum(cmoney)as total from consumption group by datepart(yyyy,cdate),datepart(mm,cdate) having datepart(yyyy,cdate)=2010) b group by b.年份
select sum(case DatePart(m,TrafficDate) when 1 then salenum else 0 end) as '一月', sum(case DatePart(m,TrafficDate) when 2 then salenum else 0 end) as '二月', ............................................ 这样可以达到你的要求!
Insert @tb
select N'a','2010-01-01',10,80 union all
select N'a','2010-01-01',10,80 union all
select N'a','2010-01-01',12,85 union all
select N'a','2010-01-05',12,85 union all
select N'a','2010-02-01',10,82 union all
select N'a','2010-02-02',10,83 union all
select N'a','2010-03-05',12,80 union all
select N'b','2010-01-01',20,90 union all
select N'b','2010-01-01',22,95 union all
select N'b','2010-02-01',20,92 union all
select N'b','2010-02-02',20,93 union all
select N'b','2010-03-05',21,90 union all
select N'c','2010-01-01',30,70 union all
select N'c','2010-01-01',32,75 union all
select N'c','2010-02-01',30,72 union all
select N'c','2010-02-02',30,73 union all
select N'c','2010-03-05',31,70declare @dt datetime
set @dt ='2010-01-01'
select 产品,单价,sum(case when datediff(day,[销售时间],@dt)=0 then 数量 else 0 end) [日统计],
sum(case when datediff(month,[销售时间],@dt)=0 then 数量 else 0 end) [月统计]
from @tb
where [销售时间]=@dt
group by 产品,单价
for(int i=1;i<13;i++)/1-12月
不用打那么多字,几行代码就行
datepart 函数 这个函数我不太会,能具体些吗?
是啊,也用过类似的方法,统计某年每月的业绩
挥泪ing
select datepart(yy,cdate) as '月份',sum(cmoney) from consumption group by datepart(yy,cdate)
select y.year,
sum(一月) as '一月',
sum(二月) as '二月',
sum(三月) as '三月',
sum(四月) as '四月',
sum(五月) as '五月',
sum(六月) as '六月',
sum(七月) as '七月',
sum(八月) as '八月',
sum(九月) as '九月',
sum(十月) as '十月',
sum(十一月) as '十一月',
sum(十二月) as '十二月'
from(
select m.year,
(case when (m.month = 1) then m.total else 0 end) as '一月',
(case when (m.month = 2) then m.total else 0 end) as '二月',
(case when (m.month = 3) then m.total else 0 end) as '三月',
(case when (m.month = 4) then m.total else 0 end) as '四月',
(case when (m.month = 5) then m.total else 0 end) as '五月',
(case when (m.month = 6) then m.total else 0 end) as '六月',
(case when (m.month = 7) then m.total else 0 end) as '七月',
(case when (m.month = 8) then m.total else 0 end) as '八月',
(case when (m.month = 9) then m.total else 0 end) as '九月',
(case when (m.month = 10) then m.total else 0 end) as '十月',
(case when (m.month = 11) then m.total else 0 end) as '十一月',
(case when (m.month = 12) then m.total else 0 end) as '十二月' from
(select sum(FeedPrice) as total,datepart(year,FeedData) as year ,datepart(month,FeedData) as month from FeedBack group by datepart(year,FeedData),datepart(month,FeedData))m) y
group by y.year
([产品] nvarchar(1),[销售时间] Datetime,[单价] int,[数量] int)Insert #t
select N'a','2010-01-01',10,80 union all
select N'a','2010-01-01',10,80 union all
select N'a','2010-01-01',12,85 union all
select N'a','2010-01-05',12,85 union all
select N'a','2010-02-01',10,82 union all
select N'a','2010-02-02',10,83 union all
select N'a','2010-03-05',12,80 union all
select N'b','2010-01-01',20,90 union all
select N'b','2010-01-01',22,95 union all
select N'b','2010-02-01',20,92 union all
select N'b','2010-02-02',20,93 union all
select N'b','2010-03-05',21,90 union all
select N'c','2010-01-01',30,70 union all
select N'c','2010-01-01',32,75 union all
select N'c','2010-02-01',30,72 union all
select N'c','2010-02-02',30,73 union all
select N'c','2010-03-02',30,73 union all
select N'c','2010-04-02',30,73 union all
select N'c','2010-05-02',30,73 union all
select N'c','2010-06-02',30,73 union all
select N'c','2010-07-02',30,73 union all
select N'c','2010-08-02',30,73 union all
select N'c','2010-09-02',30,73 union all
select N'c','2010-10-02',30,73 union all
select N'b','2010-07-02',20,93 union all
select N'b','2010-09-05',21,90 union all
select N'c','2010-11-01',30,70 union all
select N'c','2010-11-02',30,73 union all
select N'c','2010-12-05',31,70select sal_year as sal_year,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
from
(select [数量]*[单价] as sal,datepart(month,[销售时间]) as sal_month,datepart(year,[销售时间]) as sal_year from #t)T
pivot
(
sum(sal) for sal_month in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
)P
drop table #t
having datepart(yyyy,cdate)=2010
Max(case when [月份]=1 then total end ) '一月',
Max(case when [月份]=2 then total end ) '二月',
...
from(select datepart(yyyy,cdate) as '年份',datepart(mm,cdate) as '月份',sum(cmoney)as total from consumption group by datepart(yyyy,cdate),datepart(mm,cdate)
having datepart(yyyy,cdate)=2010) b
group by b.年份
sum(case DatePart(m,TrafficDate) when 2 then salenum else 0 end) as '二月',
............................................
这样可以达到你的要求!