mysql查询中使用别名_在mysql子查询中使用外部别名

查看

this …

SELECT rating,

user_id,

(SELECT COUNT(*)

FROM teachers_rating t1

WHERE teacher_id = 3

AND t1.user_id = t2.user_id) AS user_rated_frequency

FROM teachers_rating t2

WHERE teacher_id = 3;

SELECT AVG (rating) AS average_rating,

user_id,

(SELECT Count(*)

FROM teachers_rating t1

WHERE teacher_id = 3

AND t1.user_id = t2.user_id) AS user_rated_frequency

FROM teachers_rating t2

WHERE teacher_id = 3

GROUP BY user_rated_frequency;

上面的链接显示了一个SQL小提琴示例,假设id为3.

或者,您可以在FROM子句中使用子查询：

SELECT AVG (t1.rating),

t1.user_id,

t2.user_rated_frequency

FROM teachers_rating t1,

(SELECT tr.teacher_id,

tr.user_id,

COUNT(*) AS user_rated_frequency

FROM teachers_rating tr

GROUP BY tr.teacher_id) t2

WHERE t1.teacher_id = t2.teacher_id

AND t1.user_id = t2.user_id

GROUP BY user_id, user_rated_frequency

看看这个Fiddle吧.