该SQL如何写(100分)

U

unicom

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #1

表一
name value date
aaa
aab
aac
表二
name value date
aad
aae
aaf
aag
要求计算(aaa+aab+aac+aad+aae+aaf+aag)在指定DATE段内value的平均值

 

天

天什

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #2

select Avg(value) from
(select * from 表一
where date <:date1 and date >: date2
union all
select * from 表二
where date <:date1 and date >: date2)

 

C

Celestial dog

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #3

select avg(a.value) from ((select * from 表一) union (select * from 表二)) a
where a.name in (select b.name from ((select * from 表一) union (select * from 表二)) b where b.date between DateStart and DateEnd)

 

天

天真

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #4

select Avg(value) from
(select * from 表一
where date <:date1 and date >: date2
union all
select * from 表二
where date <:date1 and date >: date2)

 

U

unicom

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #5

我用的是ACCESS,是不是不支持UNION
报FROM子句错

 

O

OnlyYou

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-06

• #6

用aceess怕不支持sql
干脆用第三张表啊，把两张表的记录合起来,再求平均值

 

Y

yanyong

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-07

• #7

select avg(3*value+4*value) as newvalue
from 表一,表二
where 表一.name like aa* and 表二.name like aa*
and date>:datestart and date<:dateend
newvalue即为你所求的

 

U

unicom

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-07

• #8

多人接受答案了。