现在我有张企业用户表 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 descselect 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 "
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 descselect 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 "
order by (select avg(reIntegral) from review where userid=c.userid group by userid)
or
iif(n.avg_fs is null,0,n.avg_fs)
多了一个I
(select userID , avg(reIntegral*1.0) avg_fs from review group by userID) n
on m.userID = n.userID
order by avg_fs desc
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);
left join
(select userID , avg(reIntegral) avg_fs from review group by userID) n
on m.userID = n.userID
order by avg_fs desc
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;