我的SQL -引用另一个表行的数据。

Question

我不知道这是否可能。我有两张桌子：

医院表中的列

• hospitalID
• 名字
• AverageRating

分级表中的列

• ID
• rating1
• rating2
• rating3
• rating4
• rating5
• rating6
• hospitalID

rating1 to rating6是评级的类型。现在，通过calculation，我可以通过以下SELECT查询在ratings表中获得particular hospital的average rating

SELECT IFNULL((SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation) 
                   + SUM(hygine) + SUM(treatment))/(count(hospitalID) * 6), 0
                  ) AverageRating,COUNT(ID) RatingCount 
   FROM ratings
   WHERE hospitalID = '111111'

上面的查询非常适合我，但是这个averageRating我也想在我的医院表中计算，因为我想给医院排序。

在mySQL中是否有任何函数可以通过引用收视率表来计算医院的平均评分。

评级表

​

查询输出表

​

Answer 1

根据给定的表结构，您可以尝试这样做。

编辑

SELECT avgratings.*, @curRow := @curRow + 1 AS hospitalRank
FROM (
    SELECT (SUM(r.`rating1`)+SUM(r.`rating2`)+SUM(r.`rating3`)+SUM(r.`rating4`)+SUM(r.`rating5`)+SUM(r.`rating6`))/(COUNT(r.`hospitalID`)*6) AS AverageRating, h.hospitalID
    FROM hospitals h INNER JOIN ratings r
    ON h.`hospitalID`=r.`hospitalID`
    WHERE 1 GROUP BY r.`hospitalID` 
) avgratings JOIN (SELECT @curRow := 0) rank
ORDER BY avgratings.AverageRating DESC

第二次查询以获得特定医院的级别。

SELECT tablea.*
FROM (
    SELECT avgratings.*, @curRow := @curRow + 1 AS hospitalRank
    FROM (
        SELECT (SUM(r.`rating1`)+SUM(r.`rating2`)+SUM(r.`rating3`)+SUM(r.`rating4`)+SUM(r.`rating5`)+SUM(r.`rating6`))/(COUNT(r.`hospitalID`)*6) AS AverageRating, h.hospitalID
        FROM hospitals h INNER JOIN ratings r
        ON h.`hospitalID`=r.`hospitalID`
        WHERE 1 GROUP BY r.`hospitalID` 
    ) avgratings JOIN (SELECT @curRow := 0) rank

) tablea
WHERE tablea.hospitalID=1 ORDER BY tablea.AverageRating DESC

将WHERE块中的WHERE替换为hospitalID。

Answer 2

我不太明白你的问题.

您可以从以下几个方面获得一张唱片的平均价值：

charges + behaviour + admission + properInformation + hygine + treatment / 6

你可以得到总收益，因此：

avg(charges + behaviour + admission + properInformation + hygine + treatment / 6)

我要在每家医院拿到它，你可以按hospital_id分组：

select
  hospitalid,
  avg(charges + behaviour + admission + properinformation + hygine + treatment / 6) as avr,
  count(*) as rating_count
from ratings
group by hospitalid
order by 2 desc;

您可以通过加入医院表来选择医院数据(例如医院名称)。例如。

select
  h.hospitalid,
  h.name,
  avg(r.charges + r.behaviour + r.admission +
      r.properinformation + r.hygine + r.treatment / 6) as average_rating,
  count(*) as rating_count
from hospitals h
left join ratings r on r.hospitalid = h.hospitalid
group by h.hospitalid
order by average_rating desc;