带有内部连接的 sql、sqlite SELECT

Question

我想知道如何使用内部联接器以其他方式选择一列两次。我的数据库是 sqlite，我使用 PDO 数据库驱动程序。

我的例子:

    SELECT
            orders.id,
            orders.order_number,
            clients.first_name,
            clients.last_name,
            users.name AS user_name
    FROM orders

    INNER JOIN clients ON
            orders.client_id = clients.id

    INNER JOIN users ON
            orders.created_by = users.id

我还想得到，编辑这条记录的用户名

            orders.edited_by = users.id

如何参加本次评选？

Answer 1

您需要使用表别名。

SELECT
        orders.id,
        orders.order_number,
        clients.first_name,
        clients.last_name,
        creator.name AS creator_user_name
        editor.name AS editor_user_name
FROM orders

INNER JOIN clients ON
        orders.client_id = clients.id

INNER JOIN users creator ON
        orders.created_by = creator.id

INNER JOIN users editor ON
        orders.edited_by = editor.id