MySQL 每个派生表必须有自己的别名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15785925/
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
MySQL Every derived table must have its own alias
提问by NealCaffrey
Hi I need to generate an sql query by joining two queries
嗨，我需要通过加入两个查询来生成一个 sql 查询
1st Query : Getting all students data
第一个查询：获取所有学生数据
SELECT * FROM students where class = 1 and section = 'A'
2nd Query : Getting the count of each attendance
第二个查询：获取每次出席人数
SELECT roll_no,full_name,
SUM(hasAttended= 'P') AS DaysPresent,
SUM(hasAttended= 'A') AS DaysAbsent,
COUNT(*) AS totalClasses
FROM attendance
GROUP BY roll_no
NOW I need to join the two tables and produce a resultant table
现在我需要加入两个表并生成一个结果表
I am trying the following query but getting an error :
我正在尝试以下查询，但出现错误：
1248 - Every derived table must have its own alias
1248 - 每个派生表都必须有自己的别名
My Query is as follows :
我的查询如下：
SELECT * FROM students as st
INNER JOIN
(SELECT att.roll_no,att.full_name,
SUM(att.hasAttended= 'P') AS DaysPresent,
SUM(att.hasAttended= 'A') AS DaysAbsent,
COUNT(*) AS totalClasses
FROM attendance as att
GROUP BY att.roll_no)
ON st.roll_no = att.roll_no
ORDER BY st.roll_no
Can anybody please solve the above error
任何人都可以解决上述错误
回答by Taryn
MySQL requires that all derived tables and subqueries have an alias. You are missing an alias at the end of the closing parentheses for the subqquery:
MySQL 要求所有派生表和子查询都有一个别名。您在 subqquery 的右括号末尾缺少别名：
SELECT *
FROM students as st
INNER JOIN
(
SELECT att.roll_no,att.full_name,
SUM(att.hasAttended= 'P') AS DaysPresent,
SUM(att.hasAttended= 'A') AS DaysAbsent,
COUNT(*) AS totalClasses
FROM attendance as att
GROUP BY att.roll_no
) att ---------------------------< this is missing
ON st.roll_no = att.roll_no
WHERE st.class = 1
ORDER BY st.roll_no
回答by Arun
Generally subqquery needs an alias name as above. Also I had a problem which was solved. You can see my solved query. i.e.
通常 subqquery 需要如上所述的别名。我也有一个问题已经解决了。你可以看到我解决的查询。IE
SELECT SUM(total_items) AS sum_total
FROM
(SELECT COUNT(imt.item_id) AS total_items
FROM item_master_tbl imt
INNER JOIN map_category_item_tbl mci
ON imt.item_id=mci.item_id
INNER JOIN category_master_tbl cmt
ON mci.category_id=cmt.category_id
GROUP BY cmt.category_id) alias_name;
