现在我有张企业用户表company -企业用户表
userID (用户ID字段)
companyName (企业名称)review 评论表
userID (用户ID字段)
reTitle (评论标题)
reContent (评论内容)
reIntegral (评论分数)我现在要要对企业用户表进行查询排序,条件是,跟据评论表中的,评论分数的平均值,来排序。如:现在有userID=1 这个用户 有两条评论,分数分别是 10 ,8 ,那他的平均值应是,(10+8)/2=9 ,然后得用9这个数来排序
userID (用户ID字段)
companyName (企业名称)review 评论表
userID (用户ID字段)
reTitle (评论标题)
reContent (评论内容)
reIntegral (评论分数)我现在要要对企业用户表进行查询排序,条件是,跟据评论表中的,评论分数的平均值,来排序。如:现在有userID=1 这个用户 有两条评论,分数分别是 10 ,8 ,那他的平均值应是,(10+8)/2=9 ,然后得用9这个数来排序
from company m , review n
where m.userID = n.userID group by m.userID , m.companyName
order by avg_fsselect m.* , avg(n.reIntegral*1.0) avg_fs
from company m , review n
where m.userID = n.userID group by m.userID , m.companyName
order by avg_fs desc
from company m
left join review n
where m.userID = n.userID
group by m.userID , m.companyName
order by avg_fs
select m.* , avg(n.reIntegral*1.0) 平均值
from company m inner join review n on m.userID = n.userID
group by m.userID , m.companyName
order by 平均值 desc
select m.* , avg(n.reIntegral*1.0) 平均值
from company m left join review n on m.userID = n.userID
group by m.userID , m.companyName
order by 平均值 desc
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(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
这个查询还是不行,avg_fsd 这列出来全是变成1了
以下为测试结果.create table company(userID int,companyName varchar(10))
insert into company values(1 , '一')
insert into company values(2 , '二')
insert into company values(3 , '三')
create table review(userID int, reIntegral int)
insert into review values(1 , 1)
insert into review values(1 , 2)
insert into review values(1 , 3)
insert into review values(1 , 4)
insert into review values(1 , 5)
insert into review values(2 , 2)
insert into review values(2 , 3)
goselect 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 descdrop table company , review/*
userID companyName avg_fs
----------- ----------- ----------------------------------------
1 一 3.000000
2 二 2.500000
3 三 .000000(所影响的行数为 3 行)警告: 聚合或其它 SET 操作消除了空值。
userID companyName avg_fs
----------- ----------- ----------------------------------------
1 一 3.000000
2 二 2.500000
3 三 .000000(所影响的行数为 3 行)*/
总是这里有错
isnull(n.avg_fs,0) avg_fs 提示
“the right syntax to usr near '0) as avg_fs from "
代替 isnull(n.avg_fs,0)