求关于表连接的一条SQL语句～～～～～～～～～～～～～～
有三个表：
NO.1
TableName: Batch
-----------------------------------
BID
1
2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO.2
TableName: Task
-----------------------------------
TID BID
1 1
3 1
8 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO.3
TableName: Entry
----------------------------------
EID TID LID EOrdNum
5 1 2302 382
6 1 2303 465
7 1 2315 384
8 1 2316 225
9 8 2302 412
10 8 2303 402
11 8 2315 354
12 8 2316 347
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
求 BID为１的所有项的统计和的SQL 语句．
结果如下：
－－－－－－－－－－－－－－－－－－
BID LID EOrdNum
1 2302 794
1 2303 867
1 2315 738
1 2316 572
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
问题点数：50、回复次数：4Top
1 楼lsqkeke（可可）回复于 2006-03-03 10:58:25 得分 15
select BID,LID,EOrdNum=(select sum(EOrdNum) from [NO.3] where LID=a.LID)
from [NO.1] b,[NO.3] a
where b.BID=a.TID AND b.BID=1Top
2 楼wgsasd311（自强不息）回复于 2006-03-03 10:58:43 得分 15
select a.bid,b.lid,eordnum=sum(eordnum)
from tb2 a,tb3 b
where a.bid=1 and a.tid=b.tid
group by a.bid,b.lidTop
3 楼mm2love2zz（never stop.）回复于 2006-03-03 11:07:52 得分 10
select 1 as bid,lid,sum(eordnum) from entry where tid in (select tid from task where bid = 1) group by lidTop
4 楼zjdyzwx（十一月猪）回复于 2006-03-03 14:57:55 得分 10
declare @t_1 table(tid int,bid int)
declare @t_2 table(eid int,tid int,lid int ,eordnum int)
insert into @t_1
select 1 , 1 union all
select 3, 1 union all
select 8, 1
insert into @t_2
select 5 , 1, 2302 , 382 union all
select 6, 1, 2303, 465 union all
select 7, 1, 2315 , 384 union all
select 8, 1, 2316, 225 union all
select 9, 8, 2302, 412 union all
select 10, 8, 2303, 402 union all
select 11, 8, 2315, 354 union all
select 12, 8, 2316, 347
select c.bid,c.lid,EOrdNum =sum(EOrdNum) from (select bid = '1' ,lid = a.lid,EOrdNum =b. EOrdNum from (select * from @t_2 where tid in (select tid from @t_1 where bid = 1)) a ,(select * from @t_2 where tid in (select tid from @t_1 where bid = 1) ) b where a.lid = b.lid and a.eordnum = b.eordnum) c group by c.bid,c.lid
Top
