SQL 查询中的多个别名

如果您在不同的子句和/或子查询中多次使用该表，则可以

SELECT SeniorManager = sm.displayName, Manager = m.displayName, Worker = w.displayName
FROM   people AS w
LEFT OUTER JOIN people AS  m ON  m.id = w.bossID
LEFT OUTER JOIN people AS sm ON sm.id = m.bossID

但是你不能给同一个表使用多个别名——例如以下是无效的语法：

SELECT a1.name, a2.name FROM people AS a1 AND a2

此外，您不能（在大多数 DBMS 中）同时使用真实名称和别名的表，因此以下内容也是无效的：

SELECT people.name, a1.name FROM people AS a1 

如果一个对象的使用被赋予了一个别名，那么即使真实名称在查询中是明确的，您也必须通过该别名引用该实例。

是的你可以。

例如：对于这样的表：

create table staff
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(100),
    supervisorId INT,
    FOREIGN KEY (SupervisorId) REFERENCES staff(id)
)

您可以像这样查询它：

select a.name as EmployeeName, b.name as SupervisorName
from staff a,
 staff b
where 
 a.supervisorId = b.id

或者

select a.name as EmployeeName, b.name as SupervisorName
from 
 staff a
 inner join staff b on a.supervisorId = b.id

您可以使用INNER JOIN执行此操作，例如

SELECT count(*)
FROM user AS u1
INNER JOIN user u2 ON u1.user_id = u2.user_id

但请注意，这将比仅“一个别名”表现更差

mysql   > DESC select count(*)
          FROM user AS u1
+------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------+--------+------------+-------------+
|   id | select_type   | table   |   partitions | type   |   possible_keys | key           |   key_len |    ref |   rows |   filtered | Extra       |
|------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------+--------+------------+-------------|
|    1 | SIMPLE        | u1      |       <null> | index  |          <null> | client_id_idx |         4 | <null> |   5625 |        100 | Using index |
+------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------+--------+------------+-------------+

mysql   > DESC select count(*)
          FROM user AS u1
          INNER JOIN user u2 ON u1.user_id = u2.user_id
+------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------------------+--------+------------+-------------+
|   id | select_type   | table   |   partitions | type   | possible_keys   | key           |   key_len | ref                |   rows |   filtered | Extra       |
|------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------------------+--------+------------+-------------|
|    1 | SIMPLE        | u1      |       <null> | index  | PRIMARY         | client_id_idx |         4 | <null>             |   5625 |        100 | Using index |
|    1 | SIMPLE        | u2      |       <null> | eq_ref | PRIMARY         | PRIMARY       |         4 | gr_cg35.u1.user_id |      1 |        100 | Using index |
+------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------------------+--------+------------+-------------+