MySQL/Language/Alias
< MySQL
表达式与列可以使用AS定义别名。别名可用于ORDER BY, GROUP BY 或 HAVING子句,但不能用于WHERE 子句。例如:
SELECT
CONCAT(last_name,' ', first_name) AS full_name,
nickname AS nick
FROM
mytable
ORDER BY
full_name
表名也可以有别名。可以使用或忽略AS关键字。
SELECT
COUNT(B.Booking_ID), U.User_Location
FROM
Users U
LEFT OUTER JOIN
Bookings AS B
ON
U.User_ID = B.Rep_ID AND
B.Project_ID = '10'
GROUP BY
(U.User_Location)
自连接(self join)时表名的别名非常重要。
SELECT
p.name AS parent,
c.name AS child,
MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) AS minage
FROM
people AS p
LEFT JOIN
people AS c
ON
p.name=c.parent WHERE c.name IS NOT NULL
GROUP BY
parent HAVING minage > 50 ORDER BY p.dob;