MySQL -基于其他值的内连接-添加值列

Question

我正在与mysql做斗争:/

我在数据库fe中有多个表。任务、用户等

表任务包含具有各种变量的任务，但最重要的是用户的id被签名到任务(作为任务中的不同角色--作者、图形、校正器)：

+---------+-------------+--------------+
| task_id | task_author | task_graphic |
+---------+-------------+--------------+
| 444     | 1           | 2            |
+---------+-------------+--------------+

表用户

+---------+----------------+------------+-----------+
| user_id | user_nice_name | user_login | user_role |
+---------+----------------+------------+-----------+
| 1       | Nice Name #1   | login1     | 0         |
+---------+----------------+------------+-----------+
| 2       | Bad Name #2    | login2     | 1         |
+---------+----------------+------------+-----------+

使用PDO，在使用来自不同表(和$_GET变量)的数据时，我将获得所需的全部数据。

SELECT tasks.*, types.types_name, warehouse.warehouse_id, warehouse.warehouse_code, warehouse.warehouse_description
FROM tasks
INNER JOIN types ON types.types_id = tasks.task_id
INNER JOIN warehouse ON warehouse.warehouse_id = tasks.task_id
WHERE tasks.task_id = '".$get_id."'
ORDER BY tasks.task_id

以上查询返回：

+---------+--------------+--------------+----------------+------------+-----------+------------------+------------------------+------------+-------------+-----------------+-----------+----------------+--------------------+---------------------+-----------+---------------------+------------------+---------------------+
| task_id | task_creator | task_graphic | task_purchaser | task_title | task_lang | task_description | task_description_files | task_files | task_status | task_prod_index | task_type | task_print_run | task_print_company | task_warehouse_code | task_cost | task_time_added     | task_deadline    | task_date_warehouse |
+---------+--------------+--------------+----------------+------------+-----------+------------------+------------------------+------------+-------------+-----------------+-----------+----------------+--------------------+---------------------+-----------+---------------------+------------------+---------------------+
| 2       | 1            | 2            | 1              | Test       | PL        | Lorem ipsum (?)  |                        |            | w           | 2222            | 3         | 456546         | Firma XYZ          | 2                   | 124       | 29.09.2016 15:48:20 | 01.10.2016 12:00 | 07.10.2016 14:00    |
+---------+--------------+--------------+----------------+------------+-----------+------------------+------------------------+------------+-------------+-----------------+-----------+----------------+--------------------+---------------------+-----------+---------------------+------------------+---------------------+

我希望在task_creator、task_author和task_graphic之后使用添加的task_author进行查询--显然，在上面的3个字段fe中，从表用户中选择的名称很好。

+---------+--------------+------------------------------------+--------------+--------------------------------------+
| task_id | task_creator | task_creator_nn                    | task_graphic | task_graphic                         |
+---------+--------------+------------------------------------+--------------+--------------------------------------+
| 2       | 1            | Nice Name (from task_creator ID=1) | 2            | Nice Name (from task_graphic ID = 2) |
+---------+--------------+------------------------------------+--------------+--------------------------------------+

我怎样才能做到这一点？

Answer 1

你需要三个连接：

SELECT t.*,
       uc.user_nice_name as creator_name,
       ug.user_nice_name as graphic_name,
       up.user_nice_name as purchaser_name,
       ty.types_name, w.warehouse_id, w.warehouse_code, w.warehouse_description
FROM tasks t INNER JOIN
     types ty
     ON ty.types_id = t.task_id INNER JOIN
     warehouse w
     ON w.warehouse_id = t.task_id LEFT JOIN
     users uc
     ON uc.user_id = t.task_creator LEFT JOIN
     users ug
     ON ug.user_id = t.task_graphic LEFT JOIN
     users up
     ON up.user_id = t.task_purchaser
WHERE t.task_id = '".$get_id."'
ORDER BY t.task_id;

备注：

• 表别名使查询更易于编写和读取。它们也是必需的，因为在users子句中有三个对FROM的引用。
• 这将使用LEFT JOIN作为users，以防止某些引用值丢失。
• 你得努力点你的名字。“仓库”id与“任务”id匹配是没有意义的。或者，“任务”id与“type”id匹配。但这就是你在问题中表达问题的方式。
• ORDER BY实际上什么也不做，因为所有行都有相同的task_id。

Answer 2

假设task_graphic_name位于表名task_graphic_table中，关系字段为task_graphic_id

SELECT tasks.*
      , types.types_name
      , warehouse.warehouse_id
      , warehouse.warehouse_code
      , warehouse.warehouse_description
      , users.user_nice_name
FROM tasks
INNER JOIN types ON types.types_id = tasks.task_id
INNER JOIN warehouse ON warehouse.warehouse_id = tasks.task_id
INNER JOIN users ON users.user_nice_name = tasks.task_graphic
WHERE tasks.task_id = '".$get_id."'
ORDER BY tasks.task_id

如果需要按特定顺序显示列，则应按顺序调用列名，例如：

SELECT tasks.col1
      , task.col2
      , types.types_name
      , warehouse.warehouse_id
      , warehouse.warehouse_code
      , task.col2
      , warehouse.warehouse_description
      , task_graphic_table.task_graphic_name

Answer 3

在查询中添加两个子查询。喜欢

 SELECT tasks.*,
....
....,
(select user_nice_name from users where id = tasks.task_author) AS task_creator_name,
(select user_nice_name from users where id = tasks.task_graphic) AS task_graphic_name
FROM tasks
INNER JOIN types ON types.types_id = tasks.task_id
....
....