雪地裸体跪求（急用！！ SQL语句）（在线急等。。）

[jimmmmm]

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

[bluemaple268]

樓主厲害！哈哈,以後再求人時要冰天雪地、赤身裸體、左空翻、右旋轉720度，落地跪求，那樣才感人呢！

[txlicenhe]

select a.ID,sum(b.amount) as b_Amount,sum(c.amount) as C_Amount
from A表 a
join B表 b on a.id = b.id and b.date between a.OldDate and a.newDate
join C表 c on a.id = c.id and c.date between a.OldDate and a.newDate

group by a.id

[lh1979]

select a.ID,sum(b.amount) as b_Amount,sum(c.amount) as C_Amount
from A表 a, B表 b , C表 c
where a.id = b.id and b.date between a.OldDate and a.newDate and
a.id = c.id and c.date between a.OldDate and a.newDate
group by a.id

[guanshiyu123]

同意楼上,这个问题好熟悉啊

[jimmmmm]

这是我昨天的问题，还没解决呢！！
昨天有人按照你们的方法得出这样的结果

回复人： fycooer(水影) ( ) 信誉：100 2004-12-02 21:59:00 得分: 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

？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？

[jiang130]

select a.ID,sum(distinct b.amount) as b_Amount,sum(distinct c.amount) as C_Amount
from A表 a, B表 b , C表 c
where a.id = b.id and b.date between a.OldDate and a.newDate and
a.id = c.id and c.date between a.OldDate and a.newDate
group by a.id

[jiang130]

看錯結果了,c_amount應是400,請問樓主怎能得350,除非還有別的條件

[jimmmmm]

对
写错了
c_amount應是400

可是这样结果还是不正确啊！！

[txlicenhe]

create table a (id int,olddate 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,(select sum(amount) from b where id = a.id and date between a.OldDate and a.newDate) b_Amount
,(select sum(amount) from c where id = a.id and date between a.OldDate and a.newDate) c_Amount
from a

/*
id b_Amount c_Amount
----------- ----------- -----------
1 350 350
2 400 400

（所影响的行数为 2 行）

*/

[jimmmmm]

多谢！！
ok了！！

[jimmmmm]

怎么放分啊！？？

[singlesoul]

select a.id,(select sum(amount) from b where id = a.id and date between a.OldDate and a.newDate) b_Amount
,(select sum(amount) from c where id = a.id and date between a.OldDate and a.newDate) c_Amount
from a
ok　了，楼上的大哥厉害