在 mysql 子查询中使用外部别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18987668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using outer alias in mysql subquery
提问by ackerchez
I am writing a mysql query and I have a question. Can I / How do I do something like this:
我正在编写一个 mysql 查询，我有一个问题。我可以/如何做这样的事情：
select rating, user_id, (
-- in here I want to write a subquery to get the number of times the user_id in the outter query has rated this teacher
) as user_rated_frequency from teachers_rating where teacher_id = id
Essentially I am trying to get data and the frequency in which that user rated that teacher. Is it possible to use an alias from one of the items I want to select in a subquery that is still in the select and not in the where clause?
本质上，我正在尝试获取数据以及该用户对该教师进行评分的频率。是否可以使用我想在仍然在选择中而不是在 where 子句中的子查询中选择的项目之一的别名？
回答by Trinimon
Check out 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;
or that one:
或者那个：
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;
Links above show a SQL Fiddle example assuming that idis 3.
上面的链接显示了一个 SQL Fiddle 示例，假设id是3.
Alternatively you could have a sub query in the FROMclause:
或者，您可以在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
Hat a look at this Fiddle.
看看这个Fiddle。
回答by Andrew
You need to move your subquery (technically called a derived table) into your from clause. Something like so:
您需要将您的子查询（技术上称为派生表）移动到您的 from 子句中。像这样：
select
rating,
user_id,
from teachers_rating,
(in here I want to write a subquery to get the number of times the user_id in the outter query has rated this teacher) as user_rated_frequency f
where teacher_id = f.id
