如何检索由子查询

Question

下面的子查询返回一组值的最小值（S）：

SELECT R.mID AS MID, AVG(R.stars) AS AVGSTARS FROM Rating R 
GROUP BY R.mID 
ORDER BY AVGRSTARS 

返回下列值：

MID AVGSTARS 

104 2.5000 
103 2.5000 
101 3.0000 
108 3.3333 
107 4.0000 
106 4.5000 

现在我要检索最小值（或多个）从其中发生该中间结果是

MID AVGSTARS 

104 2.5000 
103 2.5000 

为了实现这一点，我已经写后续ing查询不工作：

SELECT INTER1.RMID FROM 
(SELECT R.mID AS MID, AVG(R.stars) AS AVGSTARS FROM Rating R 
GROUP BY R.mID 
ORDER BY AVGRSTARS) INTER1 
WHERE MIN(AVGRSTARS) = INTER1.AVRSTARS 

我知道它不起作用，但我想写一个查询这些行。有人能帮帮我吗。

谢谢。

Answer 1

你可以使用：

WITH cte AS ( 
    SELECT R.mID AS MID, AVG(R.stars) AS AVGSTARS 
    FROM Rating R GROUP BY R.mID) 
SELECT MID, AVGSTARS 
FROM cte 
WHERE AVGSTARS = (SELECT MIN(AVGSTARS) FROM cte); 

DBFiddle Demo

编辑：

的MySQL前8.0：

SELECT MID, AVGSTARS 
FROM (SELECT R.mID AS MID, AVG(R.stars) AS AVGSTARS 
     FROM Rating R GROUP BY R.mID) sub 
WHERE AVGSTARS = (SELECT MIN(AVGSTARS) 
        FROM (SELECT R.mID AS MID, AVG(R.stars) AS AVGSTARS 
         FROM Rating R GROUP BY R.mID) AS s)