mysql求平均值的问题，急！

[lonan]

采用PHP+MYSQL
表 company
userid name status
1 天才 1
2 英雄 1
3 鬼才 0

表 dp
gid userid sort1 sort2
1 1 4 3
2 2 2 3
3 2 2 1
4 2 3 2
5 2 3 2

列出 status=1 的情况下表company中的数据并计算表dp中userid值相同的sort1和sort2的平均值
name avgsort1 avgsort2
天才 4 3
英雄 2.5 2

请教高手用SQL语句怎样实现啊？？？

[dzxccsu]

--测试数据
if Object_id('company') is not null
drop table company
create table company(
  id bigint not null,
  [name] varchar(50) not null,
  state int not null
)
insert into company 
select '1','天才','1'
union all
select '2','英雄','1'
union all
select '3','鬼才','0'

if Object_id('dp') is not null 
drop table dp
create table dp(
  gid bigint not null,
  id bigint not null,
  sort1 float not null,
  sort2 float not null
)
insert into dp
select '1','1','4','3'
union all
select '2','2','2','3'
union all
select '3','2','2','1'
union all
select '4','2','3','2'
union all
select '5','2','3','2'
--查询
select a.[name],b.sort1,b.sort2 from ((select * from company where state='1')a left join (select id,avg(sort1*1.0) as sort1,avg(sort2*1.0) as sort2 from dp group by id)b on a.id=b.id)
--结果
天才	4.000000	3.000000
英雄	2.500000	2.000000

[阿_布]

SELECT c.*,AVG(d.sort1) avgsort1,AVG(d.sort2) avgsort2
FROM company c,dp d
WHERE c.userid=d.userid AND c.status=1
GROUP BY c.userid;

[codeyell-com]

SELECT company.name AS name, AVG( sort1 ) AS avgsort1, AVG( sort2 ) AS avgsort2
FROM company, dp
WHERE STATUS =1
GROUP BY dp.userid

[codeyell-com]

1楼强悍

[zmjsg]

学习

[zmjsg]

[Quote=引用 3 楼 baiyuxiong 的回复:]
SQL codeSELECT company.nameAS name,AVG( sort1 )AS avgsort1,AVG( sort2 )AS avgsort2FROM company, dpWHERE STATUS=1GROUPBY dp.userid
[/Quote]

顶。。。。

[乐游 之 神话]

学习

[defaultError]

学习

[t240034137]

怎么像回到MSSQL版了！

[hbbliyong]

非常感谢，解决了问题

[lqc851011]

[Quote=引用 3 楼 baiyuxiong 的回复:]
SQL code
SELECT company.name AS name, AVG( sort1 ) AS avgsort1, AVG( sort2 ) AS avgsort2
FROM company, dp
WHERE STATUS =1
GROUP BY dp.userid
[/Quote]
完全正确