求一条sql语句 ( 积分: 100 )

F

fl112900

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-31

• #1

我有个这样的表
name yx sy
w1 0.1 0.2
w1 0.9 0.0
w2 0.5 0.6
w2 0.5 0.6
w3 0.8 0.2
w4 0.9 0.1
w5 0.4 0.3

我想查询出来这样的结果:
name yx sy
w1 (0.1+0.9)/5 (0.2+0.0)/5
w2 (0.5+0.5)/5 (0.6+0.6)/5
w3 0.8/5 0.2/5
... ... ...
w5

把 所有为w1的 yx sy的值都分别求和 然后在除以 （name 字段下的值有几种就除以几，如：还有w6 就除以6 ）；
w2到w5也是这样求值！！

 

K

kk2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-31

• #2

//这样才对
select name, (sum(yx) / (select max(subString(name, 2, len(name))) from Tb)) yx,
(sum(sy) / (select max(subString(name, 2, len(name))) from Tb)) sy from Tb group by name

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-31

• #3

select name ,sum(yx) / (select count( * ) from (select distinct branchid from billdata) a) ,sum(sy) /(select count( * ) from (select distinct branchid from billdata) a ) from table group by name

 

D

dieker

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-31

• #4

select name,sum(yx)/(select count(*) from (select distinct name from tb) a) yx,
sum(sy)/(select count(*) from (select distinct name from tb) a) sy
from (select distinct name,yx,sy from tb) t
group by name

 

刘

刘卫民

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-31

• #5

同意dieker的写法!

 

T

TYZhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-01-31

• #6

如果是求平均，用avg吧：
select name,avg(yx),avg(sy)
from 表 group by name

 

F

fl112900

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-01

• #7

Sql_Str:=Sql_Str+'select name,sum(yx)/(select count(*) from (select distinct name from tb) a) yx,';
Sql_Str:=Sql_Str+'sum(sy)/(select count(*) from (select distinct name from tb) a) sy';
Sql_Str:=Sql_Str+'from (select distinct name,yx,sy from tb) t ';
Sql_Str:=Sql_Str+'group by name';
with adoquery1 do begin
sql.clear;
sql.add(Sql_Str);
open;
end;
我都是这样在 运行 都是这个错误！！
'[Microsoft][ODBC Visual FoxPro Driver]Function name is missing )

 

K

kk2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-01

• #8

你使用的是vfp的 dbf数据库可能SQL语句这样写不妥啊

 

F

fl112900

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-01

• #9

我使用的就是vfp的 dbf
表就是 dbf g格式的

那sql语句怎么写啊！！那样写不可以吗？

 

K

kk2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-01

• #10

distinct 应该是缺少这个关键字!

 

D

dieker

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-01

• #11

select name,sum(yx)/(select count(*) from (select name from tb group by name) a) yx,sum(sy)/(select count(*) from (select name from tb group by name) a) sy
from (select name,yx,sy from tb group by name,yx,sy) t
group by name
改成这样看行不，没用到关键字distinct了

 

F

fl112900

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-01

• #12

错误是一样的啊！！
我晕了，

 

F

fl112900

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-01

• #13

kk2000,您写的那句是这个错误！！！
'[Microsoft][ODBC Visual FoxPro Driver]SQL: Invalid use of subquery'.

 

K

kk2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-01

• #14

VFP 我也不懂写, 提示就是子查询无效

(select max(subString(name, 2, len(name))) from Tb))

而且: 还包含 subString 这个SQL SERVER 的函数,是不行的.

 

K

kk2000

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-02

• #15

select name, (sum(yx) / (select max(subString(name, 2, len(name))) from Tb)) yx,
(sum(sy) / (select max(subString(name, 2, len(name))) from Tb)) sy from Tb group by name

那你就分开写算了！ 先得到各种类的最大数。
1.select count(1) SL from Tb group by name
2. select name, sum(yx) / :sl YX, sum(sy) / :sl sy from Tb group by name
这样应该可以了！ 这都是标准的SQL语句。 ^_^

 

F

fl112900

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-02

• #16

呵呵 我也是这样想的
我想求出来了那个数！！！
呵呵 好了弄出来了 谢谢了！！

 

F

fl112900

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-02

• #17

多人接受答案了。