计算SQL Server中的平均评分

问题描述

这是我的桌子

我想获取那些包含ServiceDescription"Plaster"或Skills"Plaster"或在石膏"类别中，并且还希望计算这些供应商的平均价格.

I want to fetch records of Those Vendor which contain ServiceDescription "Plaster" or Skills "Plaster" oris in Category "Plaster" and also want to calculate averagerating of those Vendor.

注意:如果没有任何供应商的评论，那么记录也应该到来.

note:If there is no review Of any vendor then also that records should come.

这是我的查询

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%'))

上面查询中的问题是我没有得到那个没有评论的供应商.

here problem in above query is i am not getting that vendor whose review is not there.

但我还希望该供应商不包含任何评论，但符合我的标准.

but i also want that vendor which does not contain review but matches my criteria.

用户详细信息:

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

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

Note:final output should in descending order of average rating

推荐答案

在这里，尝试以下操作:

Here, try this:

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

这篇关于计算SQL Server中的平均评分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！