按日期统计

[好帅的一条鱼]

select A.TA014 as 日期,B.TB004 as 品号,B.TB005 as 品名,B.TB006 as 规格,sum(B.TB007) as 数量,sum(B.TB011) as 金额
from INVTA A,INVTB B where A.TA001=B.TB001 and A.TA002=B.TB002  
and A.TA001 in ('1101','1103','1105','1106','1198') and A.TA014>='20120101' 
and A.TA014<='20120831' group by B.TB004,B.TB005,B.TB006

上面这条语句 我怎么按日期分别统计出一月 到八月的数量和金额呢

[好帅的一条鱼]

忘记了，今天礼拜六，大侠们，快点起床啊 ，，，，，

[人生无悔]

select convert(varchar(6),A.TA014,112) as 日期,B.TB004 as 品号,B.TB005 as 品名,B.TB006 as 规格,sum(B.TB007) as 数量,sum(B.TB011) as 金额
from INVTA A,INVTB B where A.TA001=B.TB001 and A.TA002=B.TB002  
and A.TA001 in ('1101','1103','1105','1106','1198') 
and A.TA014>='20120101' 
and A.TA014<='20120831' 
group by B.TB004,B.TB005,B.TB006,convert(varchar(6),A.TA014,112)

[以学习为目的]

2楼、4楼正解！你用你的数据环境测试下。

ps：今天去爬山啦

[zjl8008]

[Quote=引用 2 楼 的回复:]
SQL code


select convert(varchar(6),A.TA014,112) as 日期,B.TB004 as 品号,B.TB005 as 品名,B.TB006 as 规格,sum(B.TB007) as 数量,sum(B.TB011) as 金额
from INVTA A,INVTB B where A.TA001=B.TB001 and A.TA002=B.TB0……
[/Quote]

+1

[叶子]

;with m as
(
select A.TA014 as 日期,B.TB004 as 品号,B.TB005 as 品名,B.TB006 as 规格,sum(B.TB007) as 数量,sum(B.TB011) as 金额
from INVTA A,INVTB B where A.TA001=B.TB001 and A.TA002=B.TB002  
and A.TA001 in ('1101','1103','1105','1106','1198') and A.TA014>='20120101' 
and A.TA014<='20120831' group by B.TB004,B.TB005,B.TB006
)

select 
   month(日期) as 月份,sum(数量) as 数量,sum(金额) as 金额 
from m group by month(日期)