题如下:
有两张表,表Users有user列和point列
表Groups有user列和group列
我可以认为user列是unique的,point是user的分数,group是user所在的组织
可能有不同的user属于同一个group问题是:
写一个sql,检索出哪一个组织的平均point最高?
thx
有两张表,表Users有user列和point列
表Groups有user列和group列
我可以认为user列是unique的,point是user的分数,group是user所在的组织
可能有不同的user属于同一个group问题是:
写一个sql,检索出哪一个组织的平均point最高?
thx
(select group,point from users,groups where users.user=groups.user) table1
group by group
select [group] from
(select [group],avg(point) as point from yun_users,yun_groups where yun_users.[user]=yun_groups.[user] group by [group]) t1
where point =
(select max(point) from
(select [group],avg(point) as point from yun_users,yun_groups where yun_users.[user]=yun_groups.[user] group by [group]) t2)
其实我原来想这么写的:
select [group] from
(select [group],avg(point) as point from yun_users,yun_groups where yun_users.[user]=yun_groups.[user] group by [group]) t1
where exists
(select max(point) from
t1)
不知道对不对
select group,avg(point)
from users,groups
where users.user= groups.user
group by group
order by avg(point) desc ) b
where rownum = 1 ;
但是在mssql中必须修改,我把它改成这样了:select [group] from (
select top 1 avg(point) as point ,[group]
from yun_users,yun_groups
where users.[user]= groups.[user]
group by [group]
order by avg(point) desc ) t1
通过。
select top 1 avg(point) as point ,[group]
from users,groups
where users.[user]= groups.[user]
group by [group]
order by avg(point) desc ) t1