22,206
社区成员
发帖
与我相关
我的任务
分享
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)
go
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
drop 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 行)
*/
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
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.* , 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.* , 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) avg_fs
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) avg_fs
from company m , review n
where m.userID = n.userID group by m.userID , m.companyName
order by avg_fs
select 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