求一个SQL语句---关于统计的
有表A：
ID 单号 .......
1 YD001
2 YD002
3 YD003
表B：
ID AID 订单号 数量
1 1 0001 11
2 1 0002 11
3 2 0001 22
4 2 0003 33
5 3 0001 11
6 3 0002 55
这里的ID都是自动增长的，我现在想要实现 比如在A.单号=YD0002时的这个单号中所包含的订单号，在这个订单（包含）之前的数量和。
以YD002为例：
其它包含 0001 、 0003订单号 想要输出以下结果
0001 33
0003 33
这个要怎么实现 谢谢！
[解决办法]
create table 表A
(ID int, 单号 varchar(10))
insert into 表A
select 1, 'YD001' union all
select 2, 'YD002' union all
select 3, 'YD003'
create table 表B
(ID int, AID int, 订单号 varchar(10), 数量 int)
insert into 表B
select 1, 1, '0001', 11 union all
select 2, 1, '0002', 11 union all
select 3, 2, '0001', 22 union all
select 4, 2, '0003', 33 union all
select 5, 3, '0001', 11 union all
select 6, 3, '0002', 55
select b.订单号,
(select sum(数量)
from 表B c
where c.订单号=b.订单号
and c.ID<=b.ID) '数量'
from 表A a
inner join 表B b on a.ID=b.AID
where a.单号='YD002'
/*
订单号 数量
---------- -----------
0001 33
0003 33
(2 row(s) affected)
*/
--楼上的实现不了
if object_id('A') IS NOT NULL
DROP TABLE A
go
create table A
(
id int primary key identity(1,1),
[单号] varchar(10)
)
go
if object_id('B') IS NOT NULL
DROP TABLE B
go
create table B
(
id int primary key identity(1,1),
aid int,
[订单号] varchar(10),
[数量] int
)
go
insert into a
select 'YD001' UNION ALL
select 'YD002' UNION ALL
select 'YD003'
GO
INSERT INTO B
SELECT 1,'0001',11 union all
SELECT 1,'0002',11 union all
SELECT 2,'0001',22 union all
SELECT 2,'0003',33 union all
SELECT 3,'0001',11 union all
SELECT 3,'0002',55
--select * from a
--select * from b
--select aid,max([数量]) as [数量] from b group by aid
select b.[订单号],c.[数量] from b
inner join a on b.aID=a.ID
inner join
(
select aid,max([数量]) as [数量] from b group by aid
)c on b.aid =c.aid
if OBJECT_ID('taba') is not null
drop table taba
if OBJECT_ID('tabb') is not null
drop table tabb
go
create table taba(id int identity(1,1) primary key,num varchar(5))
create table tabb(id int identity(1,1),aid int references taba(id),
orderid varchar(5),quantity int)
insert into taba values('YD001'),
('YD002'),('YD003')
insert into tabb values(1,'0001',11),
(1,'0002',11),(2,'0001',22),(2,'0003',33),(3,'0001',11),(3,'0002',55)
;with sel as(select b.aid,orderid,quantity from tabb b join taba a
on a.id=b.aid and a.num='YD002')
,sel2 as(
select b.aid,b.orderid,b.quantity from tabb b join sel s
on b.aid<=s.aid and b.orderid=s.orderid
)
select orderid,SUM(quantity) from sel2 group by orderid
