跪求（急用！！ SQL语句）
A表ID OldDate newDate， name ， 。。。
1 2004-1-1 2004-12-1
2 2004-1-1 2004-10-12
B表ID date amount
1 2004-2-3 100
1 2004-3-6 150
1 2004-2-3 100
2 2004-3-6 150
2 2004-2-3 100
2 2004-3-6 150
C表ID date amount
1 2004-2-3 100
1 2004-3-6 150
1 2004-2-3 100
2 2004-3-6 150
2 2004-2-3 100
2 2004-3-6 150
求在A表的时间段B表和C表的1和2的数量
出来的字段是ID，B_Amount,C_Amount
1 350 350
2 350 350
问题点数：0、回复次数：15Top
1 楼hdhai9451（☆新人类☆）回复于 2004-12-02 17:51:51 得分 0
select a.id,B_Amount=sum(b.amount),C_Amount =sum(c.amount)
from A表 a
left join B表 b on a.id=b.id
left join C表 c on a.id=c.id
group by a.idTop
2 楼hdhai9451（☆新人类☆）回复于 2004-12-02 17:53:58 得分 0
加條件
select a.id,B_Amount=sum(b.amount),C_Amount =sum(c.amount)
from A表 a
left join B表 b on a.id=b.id
left join C表 c on a.id=c.id
where b.date between a.OldDate and a.newDate and c.date between a.OldDate and a.newDate
group by a.id
Top
3 楼good2speed（Goodspeed）回复于 2004-12-02 17:54:39 得分 0
SELECT a.ID,SUM(b.amount) AS B_Amount, SUM(c.amount) AS C_Amount FROM a
INNER JOIN b ON a.ID = b.ID AND b.date BETWEEN a.OldDate AND a.newDate
INNER JOIN c ON c.ID = b.ID AND c.date BETWEEN a.OldDate AND a.newDate
GROUP BY a.ID
Top
4 楼double22822（大无忧-老实和尚（有事发消息））回复于 2004-12-02 17:58:56 得分 0
select a.id ,sum(b.amount),sum(c.amount) from a,b,c where b.id = a.id and b.date between a.olddate and a.newdate and a.id = c.id and c.date between a.olddate and a.newdate.
先写上,回去测一下.Top
5 楼double22822（大无忧-老实和尚（有事发消息））回复于 2004-12-02 18:02:34 得分 0
忘了group by a.id 了。Top
6 楼lijiye19810413（继业）回复于 2004-12-02 18:07:57 得分 0
select a.ID,sum(b.amount) as B_AMOUNT,sum(c.amount) as C_AMOUNT from a,b,c
where a.ID=b.ID and a.ID=c.ID and (b.data betweent a.olddata and a.newdata)
and (c.data between a.olddata and a.newdata)
group by a.IDTop
7 楼double22822（大无忧-老实和尚（有事发消息））回复于 2004-12-02 18:09:17 得分 0
SELECT a.id, SUM(b.amount) , SUM(c.amount)
FROM a INNER JOIN
b ON a.id = b.id INNER JOIN
c ON a.id = c.id
WHERE (b.[date] BETWEEN a.olddate AND a.newdate) AND (c.[date] BETWEEN
a.olddate AND a.newdate)
GROUP BY a.id
测试后。Top
8 楼guanshiyu123（就欺负你..宝）回复于 2004-12-02 18:44:44 得分 0
select a.id ,sum(b.amount) b_amount,sum(c.amount) c_amount
from a inner join b
on a.id=b.id
inner join c
on a.id=c.id
where
(b.date between a.olddate and a.newdate) and (c.date between a.olddate and a.newdate)
group by a.idTop
9 楼bluemaple268（我是一片飘落的枫叶）回复于 2004-12-02 20:19:23 得分 0
select a.id ,sum(b.amount) b_amount,sum(c.amount) c_amount
from a inner join b
on a.id=b.id
inner join c
on a.id=c.id
where
(b.date between a.olddate and a.newdate) and (c.date between a.olddate and a.newdate)
group by a.id
这个还是较好的,顶!学习中!
Top
10 楼fycooer（回帖是一种美德！----水影）回复于 2004-12-02 21:59:57 得分 0
？？？？？？？？？？？？？？？？？？
create table a (id int,oledate datetime, newdate datetime)
insert a select 1,'2004-1-1','2004-12-1'
union all select 2,'2004-1-1','2004-10-12'
create table b (id int, date datetime, amount int)
insert b select 1,'2004-2-3',100
union all select 1,'2004-3-6',150
union all select 1,'2004-2-3',100
union all select 2,'2004-3-6',150
union all select 2,'2004-2-3',100
union all select 2,'2004-2-3',150
create table c (id int, date datetime, amount int)
insert c select 1,'2004-2-3',100
union all select 1,'2004-3-6',150
union all select 1,'2004-2-3',100
union all select 2,'2004-3-6',150
union all select 2,'2004-2-3',100
union all select 2,'2004-2-3',150
select a.id,B_Amount=sum(b.amount),C_Amount =sum(c.amount)
from a
inner join b on a.id=b.id
inner join c on a.id=c.id
where b.date between a.OleDate and a.newDate and c.date between a.OleDate and a.newDate
group by a.id
结果
ID B_Amount C_Amount
1 1050 1050
2 1200 1200
select a.id,B_Amount=sum(b.amount)
from a
inner join b on a.id=b.id
where b.date between a.OleDate and a.newDate
group by a.id
结果
ID B_Amount
1 350
2 400
？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？Top
11 楼jimmmmm（我不是菜鸟）回复于 2004-12-03 10:04:09 得分 0
还是不行啊！就像fycooer(水影) 得出的结果一样！！
Top
12 楼scorpiowell（年年岁岁花相似，岁岁年年人不同）回复于 2004-12-03 10:50:34 得分 0
declare @t table(ID int,B_Amount int,C_Amount int)
insert @t(ID,B_Amount)
select B.id,B_Amount=sum(convert(int,B.amount)),C_Amount=0
from A inner join
B on
A.id=B.id
where convert(datetime,B.date) between convert(datetime,A.olddate) and convert(datetime,A.newdate)
group by B.id
insert @t(ID,C_Amount)
select B.id,B_Amount=0,C_Amount=sum(convert(int,C.amount))
from A inner join
C on
A.id=C.id
where convert(datetime,C.date) between convert(datetime,A.olddate) and convert(datetime,A.newdate)
group by C.id
SELECT ID,SUM(B_Amount),SUM(C_Amount) FROM @T GROUP BY ID
此情况B和C表不能相连的，会增加记录行，得出的结果当然不对。
Top
13 楼scorpiowell（年年岁岁花相似，岁岁年年人不同）回复于 2004-12-03 10:55:23 得分 0
出来的字段是ID，B_Amount,C_Amount
1 350 350
2 350 350
????????????????????????????????????
你的结果有误吧，按照你的逻辑过程，怎么可能？应该是：
ID，B_Amount,C_Amount
1 350 350
2 400 400 呀。
Top
14 楼jimmmmm（我不是菜鸟）回复于 2004-12-03 11:09:52 得分 0
对的，应该是400
那么请问scorpiowell(小狸) 怎么得到这里的结果呢？？
此情况B和C表不能相连的，会增加记录行，得出的结果当然不对。
？？？
什么意思Top
15 楼fycooer（回帖是一种美德！----水影）回复于 2004-12-03 12:52:49 得分 0
create table a (id int,oledate datetime, newdate datetime)
insert a select 1,'2004-1-1','2004-12-1'
union all select 2,'2004-1-1','2004-10-12'
create table b (id int, date datetime, amount int)
insert b select 1,'2004-2-3',100
union all select 1,'2004-3-6',150
union all select 1,'2004-2-3',100
union all select 2,'2004-3-6',150
union all select 2,'2004-2-3',100
union all select 2,'2004-2-3',150
create table c (id int, date datetime, amount int)
insert c select 1,'2004-2-3',100
union all select 1,'2004-3-6',150
union all select 1,'2004-2-3',100
union all select 2,'2004-3-6',150
union all select 2,'2004-2-3',100
union all select 2,'2004-2-3',150
declare @t table (Id int , B_Amount int, C_Amount int)
insert @t (id,B_amount)
select a.id,B_Amount=sum(B.amount)
from a inner join b on a.id = b.id
where b.date between a.oledate and a.newdate group by a.id
insert @t(id,C_Amount)
select a.id,C_Amount = sum(c.amount)
from a inner join c on a.id = c.id
where c.date between a.oledate and a.newdate group by a.id
select id,sum(b_amount) as B_Amount,sum(c_Amount) as C_Amount from @t group by id
结果
----------------------
id B_Amount C_Amount
1 350 350
2 400 400
学习：）
Top
