一个复杂SQL查询语句，冰天雪地跪求解答

[零起飞]

现在我有张企业用户表

company -企业用户表
userID (用户ID字段)
companyName (企业名称)

review 评论表
userID (用户ID字段)
reTitle （评论标题）
reContent （评论内容）
reIntegral (评论分数)

我现在要要对企业用户表进行查询排序，条件是，跟据评论表中的，评论分数的平均值，来排序。

如：现在有userID=1 这个用户 有两条评论，分数分别是 10 ，8 ，那他的平均值应是，(10+8)/2=9 ,然后得用9这个数来排序


"
select m.* , isnull(avg(n.reIntegral*1.0),0) avg_fs
from company m left join review n
on m.userID = n.userID group by
m.userID , m.companyName
order by avg_fs desc

select m.* , isnull(n.avg_fs,0) avg_fs from company m left join
(select userID , avg(reIntegral*1.0) avg_fs from review group by userID) n
on m.userID = n.userID
order by avg_fs desc

"这样虽然能出来但这是 mssql的写法，在 mysql当中会报错

总是这里有错提示如下

isnull(n.avg_fs,0) avg_fs 提示
“the right syntax to usr near '0) as avg_fs from "

[阿_布]

select * from company c
order by (select avg(reIntegral) from review where userid=c.userid group by userid)

[WWWWA]

COALESCE(n.avg_fs,0)
or
iif(n.avg_fs is null,0,n.avg_fs)

[WWWWA]

if(n.avg_fs is null,0,n.avg_fs)
多了一个I

[vinsonshen]

select m.* , ifnull(n.avg_fs,0) avg_fs from company m left join
(select userID , avg(reIntegral*1.0) avg_fs from review group by userID) n
on m.userID = n.userID
order by avg_fs desc

[vinsonshen]

mssql中函数isnull对应mysql下ifnull即可

[零起飞]

[Quote=引用 1 楼 zhoupuyue 的回复:]
select * from company c
order by (select avg(reIntegral) from review where userid=c.userid group by userid)
[/Quote]
这个任兄可以查出来，但是还有个问题就是现在没有平均值呀

[阿_布]

select c.*,avg(r.reintegral) ar
from company c,review r
where c.userid=r.userid
group by c.userid
order by (select avg(reIntegral) from review where userid=c.userid group
by userid);

[零起飞]

[Quote=引用 7 楼 zhoupuyue 的回复:]
select c.*,avg(r.reintegral) ar
from company c,review r
where c.userid=r.userid
group by c.userid
order by (select avg(reIntegral) from review where userid=c.userid group
by userid);
[/Quote]

这个现在有值了，但是只查询出了有评论值的，没有评论的现在就查询不来了

[vinsonshen]

select m.* , ifnull(n.avg_fs,0) avg_fs from company m 
left join 
(select userID , avg(reIntegral) avg_fs from review group by userID) n 
on m.userID = n.userID 
order by avg_fs desc 

[vinsonshen]

上面语句就是4楼的语句，也是拿你原来语句改了个函数而成的

[vinsonshen]

早就有答案了，是你自己没仔细看，亏你还“冰天雪地跪求”那么久，不冷么？

[阿_布]

select c.*,avg(r.reintegral)
from company c left join review r
on c.userid=r.userid
group by c.userid
order by (select avg(reIntegral) from review
where userid=c.userid group by userid) desc;

[零起飞]

[Quote=引用 9 楼 vinsonshen 的回复:]
SQL codeselect m.* , ifnull(n.avg_fs,0) avg_fsfrom company mleftjoin
(select userID ,avg(reIntegral) avg_fsfrom reviewgroupby userID) non m.userID= n.userIDorderby avg_fsdesc
[/Quote]
谢了，对了的你说对了，是我自己弄错了，在那后面加了一个 'as'到最后就服错了，谢谢的。