sql - 计算sql server中的平均评分

Question

这是我的 table :

我想获取那些包含 ServiceDescription "Plaster"或 Skills "Plaster"或属于“石膏”类别，还想计算这些供应商的平均评分。

注意:如果没有任何供应商的审查，那么也应该有记录。

这是我的查询:

select * from UserDetails u
  ,VendorInCategory v
  ,CategoryMaster c
  ,Review rv
where v.CategoryId=c.Id 
and u.Id=r.UserId 
and u.Id=rv.VendorId  
and v.VendorId=u.Id 
and ((u.ServiceDescription like '%Plaster%' ) 
or (u.Skills like '%Plaster%') 
or (c.Name like '%Plaster%'))

上面查询中的问题是我找不到那个评论不存在的供应商。

但我也想要那个不包含评论但符合我的标准的供应商。

用户详细信息:

id     Servicedescription         Skills
1        Plaster                  plaster

2        construction             construvtion

3        plaster                  plaster

4        null                     null(not vendor)

5        null                     null(not vendor)

评论

id     CustomerId     Vendorid    rating

1       4                1          3

2       5                1          3

预期输出:

VendorId     ServiceDescription     Skills       averagerating

1              plaster              plaster           3

3              plaster              plaster           0

注意:最终输出应按平均评分降序排列

Answer 1

在这里，试试这个:

示例数据

create table UserDetails(
    Id int,
    ServiceDescription varchar(20),
    Skills varchar(20)
)
create table Review(
    Id int,
    CustomerId int,
    VendorId int,
    Rating int
)

insert into UserDetails values(1, 'Plaster', 'plaster'),(2, 'construction', 'construction'),(3, 'plaster', 'plaster');
insert into Review values(1, 4, 1, 3),(2, 5, 1, 3);

解决方案

select
    u.Id as VendorId,
    u.ServiceDescription,
    u.Skills,
    isnull(sum(r.rating)/count(r.rating), 0) as AverageRating
from UserDetails u
left join Review r
    on r.VendorId = u.id
where
    u.ServiceDescription like '%plaster%'
    or u.Skills like '%plaster%'
group by 
    u.Id,
    u.ServiceDescription,
    u.Skills
order by AverageRating desc