vb+sql 中select问题

[jxdzjhz]

本人建一数据库
b1 b2
ID product id month salenumber
1 笔 1 1 2
2 纸 2 1 3
1 1 2
1 2 3
2 2 4
2 2 1
1 3 2
2 3 4


当选择时间为3月份,实现下表查询
product month sale tatolsalenumber
笔 3 2 9
纸 3 4 12
用select 如何实现上表值

[paoluo]

--建立测试环境
Create Table b1
(ID Int,
product Nvarchar(10))

Create Table b2
(id Int,
[month] Int,
salenumber Int)
--插入数据
Insert b1 Values(1, N'笔')
Insert b1 Values(2, N'纸')

Insert b2 Values(1, 1, 2)
Insert b2 Values(2, 1, 3)
Insert b2 Values(1, 1, 2)
Insert b2 Values(1, 2, 3)
Insert b2 Values(2, 2, 4)
Insert b2 Values(2, 2, 1)
Insert b2 Values(1, 3, 2)
Insert b2 Values(2, 3, 4)
--测试
Declare @month Int
Set @month=3
Select
A.product,
B.[month],
SUM(B.salenumber) As sale,
(Select SUM(salenumber) from b2 Where ID=B.ID And [month]<=B.[month]) As tatolsalenumber
from b1 A
Inner Join b2 B
On A.id=B.id
Where B.[month]=@month
Group By A.product,B.id,B.[month]
Order By A.product,B.id,B.[month]
--删除测试环境
Drop Table b1,b2
--结果
/*
product month sale tatolsalenumber
笔 3 2 9
纸 3 4 12
*/

[jxdzjhz]

paoluo(一天到晚游泳的鱼)

太感激你了，这个问题我想了一天也没能解决