sql问题急.....(30分)

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #1

有两个表 a1,a2
a1是记录物品的发货数量和日期结构如下

物品代号 发货数量 发货日期
part shu date1
aaa1 70 2006-1-3
aaa1 90 2006-2-3
aaa2 100 2006-1-7
aaa2 90 2006-2-1
aaa1 40 2006-3-1


a2是记录发货单价和发货时间段(也就是说不同的发伙时间段发货单价是不一样的)

物品代号 发货单价 发货时间段1 发货时间段2
part dan date1 date2
aaa1 58 2006-1-1 2006-2-6
aaa1 60 2006-2-7 2006-3-7
aaa1 61 2006-3-8 2006-4-8
aaa2 30 2006-1-1 2006-2-6
aaa2 32 2006-2-7 2006-3-6

现在我想用一条sql 把这两个表连起来并计算出金额(只要是a1的出货日期是在a2的时间段里的就用a1的数量 * a2相对应的单价)请高手指点如何用一条sql实现

 

L

LFCZ

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #2

select a.part,(a.shu*b.dan) as je from a1 a,a2,b where a.part=b.part and
(a.date1>=b.date1 and a.date1<=b.date2)

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #3

呵楼上的不行呀!

 

H

hetian_1982

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #4

暂时还没有想到,帮你顶!!

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #5

select B.part,B.dan*(sum(A.shu)),B.date1,B.date2 from a2 B inner join a1 A on B.part=A.part where A.date1 beteen B.date1 and B.date2 group by B.part,B.date1,B.date2
你试一下.

 

L

LFCZ

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #6

select a.part,(a.shu*b.dan) as je from a1 a,a2 b where a.part=b.part and
(a.date1>=b.date1 and a.date1<=b.date2)
中间多了一个“，”，现在试试看

 

L

LFCZ

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #7

to hityou
a2表中同一种产品的不同日期发货单价是不相同的

 

Z

zhlu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #8

SELECT Part,SUM((CASE WHEN date1>=d1 and date1<=d2 then dan else 0.0) *shu) as Total
FROM
(
SELECT a1.part,a1.shu,a1.date1,a2.dan,a2.date1 as d1,a2.date2 as d2
FROM a1,a2
WHERE a1.part=a2.part
) as A

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #9

呵,上面两位我试了数据都不对呀!!!

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #10

楼主是要统计整个时间的金额,还是每个时间段,每个part都要统计?

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #11

是每个part都要统计,但a1的单价是通过a2相对应的时间段里抓出来的

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #12

select A.part,A.shu*B.dan,A.date1 from a1 A inner join a2 B on A.part=B.part where A.date1 between B.date1 and B.date2
这个试试

 

W

williamlea

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #13

select part,sum(je) from (select a.part,(a.shu*b.dan) as je from a1 a,a2,b where a.part=b.part and (a.date1>=b.date1 and a.date1<=b.date2) )aa group by part

 

Z

zhlu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #14

SELECT Part,SUM((CASE WHEN date1>=d1 and date1<=d2 then dan else 0.0) *shu) as Total
FROM
(
SELECT a1.part,a1.shu,a1.date1,a2.dan,a2.date1 as d1,a2.date2 as d2
FROM a1,a2
WHERE a1.part=a2.part
) as A
GROUP BY Part
不對? 有問題? 只要a2表中的時間沒有重疊的,應該可以吧!

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #15

to zhlu 不行呀
a2里的日期没重复,但与a1连起来后就有很多重复的数据

 

H

hityou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #16

我后来改的呢,可以么/

 

Z

zhlu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #17

有重復的但(CASE WHEN date1>=d1 and date1<=d2 then dan else 0.0 END) 單價為0,不影響統計吧!

(CASE WHEN date1>=d1 AND date1<=d2 THEN dan ELSE 0.0 END)少寫了一個END

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #18

to hityou 也不行数据不对!!看来我只有用存储过程了

 

雨

雨林^-^

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-05-22

• #19

接受答案了.