php中mysql内连接写法,php – 两列上的MySQL内连接

SELECT books.*, author1.*, author2.*

FROM books

LEFT JOIN author AS author1

ON author1.author_id = books.author_id

LEFT JOIN author AS author2

ON author2.author_id = books.secondary_author_id

在SQL中,您可以通过在表名后添加表来对表进行别名.请注意,现在您将拥有重复的列,因此您可能希望为author1和author2的结果设置别名而不是author1.*.

编辑

其他细节 – 假设您有基本的表格(我将包含详细信息,以便人们想要自己测试)：

CREATE DATABASE test;

USE test;

CREATE TABLE books

(

book_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(50),

author_id INT NOT NULL,

secondary_author_id INT

);

CREATE TABLE authors

(

author_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50)

);

INSERT INTO authors (author_id,name) VALUES (1,'Sue Z. Que'),(2,'John Doe'),(3,'Bob Smith');

INSERT INTO books (book_id,title,author_id,secondary_author_id) VALUES (1,'JOIN-ing Two Tables',1,2);

如果您选择我上面提到的选择,您的结果将如下：

|----------------------- books TABLE -----------------------------|---- authors table -----|---- authors table ---|

+---------+---------------------+-----------+---------------------+-----------+------------+-----------+----------+

| book_id | title | author_id | secondary_author_id | author_id | name | author_id | name |

+---------+---------------------+-----------+---------------------+-----------+------------+-----------+----------+

| 1 | JOIN-ing Two Tables | 1 | 2 | 1 | Sue Z. Que | 2 | John Doe |

+---------+---------------------+-----------+---------------------+-----------+------------+-----------+----------+

(我为了Calrity的缘故添加了顶部标题)你看到你有两个author_id和两个名字(因为它们是同一个表和相同列名的连接).但是,如果您对连接中的列进行别名,如下所示：

SELECT books.*, author1.name AS primary_author, author2.name AS secondary_author

FROM books

LEFT JOIN authors AS author1

ON author1.author_id = books.author_id

LEFT JOIN authors AS author2

ON author2.author_id = books.secondary_author_id;

你得到一个更清洁的结果：

|----------------------- books TABLE -----------------------------| authors table -|- authors table --|

+---------+---------------------+-----------+---------------------+----------------+------------------+

| book_id | title | author_id | secondary_author_id | primary_author | secondary_author |

+---------+---------------------+-----------+---------------------+----------------+------------------+

| 1 | JOIN-ing Two Tables | 1 | 2 | Sue Z. Que | John Doe |

+---------+---------------------+-----------+---------------------+----------------+------------------+