时间戳

首页 > 解决方案 > 根据 ONE 列的值有条件地连接两个表

mysql - 根据 ONE 列的值有条件地连接两个表

问题描述

我有下表来管理物品的购买和问题。

项目表

+---------+-----------+
| item_id | item_name |
+---------+-----------+
|     500 | A4        |
|     501 | A5        |
|     502 | B5        |
|     503 | B4        |
|     504 | A3        |
+---------+-----------+

供应商表

+-------------+---------------+
| sup_id      | supplier_name |
+-------------+---------------+
|           1 | ABC Suppliers |
|           2 | DEF Suppliers |
|           3 | GHI Suppliers |
+-------------+---------------+

官员表

+------------+--------------+
| officer_id | officer_name |
+------------+--------------+
|          1 | Jhon         |
|          2 | William      |
|          3 | Ken          |
|          4 | Robert       |
+------------+--------------+

采购/发行表

+-----------+---------+---------+---------+------------+-----+-------------+
| update_id | bill_no | sup_id  | item_id |    date    | qty |    type     |
+-----------+---------+---------+---------+------------+-----+-------------+
|      1000 |      10 |       1 |     500 | 2018-11-01 |  50 | purchase    |
|      1001 |      40 |       1 |     500 | 2018-11-02 |  25 | purchase    |
|      1002 |      32 |       2 |     500 | 2018-11-04 |  10 | issue       |
|      1003 |      25 |       3 |     500 | 2018-11-05 |  12 | issue       |
|      1004 |      14 |       1 |     500 | 2018-11-08 |  22 | purchase    |
|      1005 |      55 |       2 |     501 | 2018-11-09 |  10 | purchase    |
|      1006 |      30 |       2 |     502 | 2018-11-10 |  40 | purchase    |
+-----------+---------+---------+---------+------------+-----+-------------+

02) 购买/发行表通过在表格末尾提及“类型”来保存购买明细和发行明细。

03）我需要得到以下输出。

+-----------+------------------------------+------------+-----+------------+
| item_name | supplier_name / officer_name |    date    | qty |type        |
+-----------+------------------------------+------------+-----+------------+
| A4        | ABC Suppliers                | 2018-11-01 |  50 | purchase   |
| A4        | ABC Suppliers                | 2018-11-02 |  25 | purchase   |
| A4        | William                      | 2018-11-04 |  10 | issue      |
| A4        | Ken                          | 2018-11-05 |  12 | issue      |
| A4        | ABC Suppliers                | 2018-11-08 |  22 | purchase   |
| A5        | DEF Suppliers                | 2018-11-09 |  10 | purchase   |
| B5        | DEF Suppliers                | 2018-11-10 |  40 | purchase   |
+-----------+------------------------------+------------+-----+------------+

03）我使用了以下查询

select item.item_name, 
       supplier.supplier_name, 
       purchase.date, 
       purchase.qty, 
       purchase.type 
from purchase 
join item on item.item_id = purchase.item_id
where supplier.sup_id in (select sup_id from supplier)

04）但我没有得到想要的输出。我不明白我出了什么问题。谁能帮我 ？

标签： mysqlsql

解决方案

在您的SELECT子句中，您指的是supplier表，而不是加入该表。现在，您似乎想显示officer_name购买类型何时为issue，否则supplier_name为purchase。

我们可以LEFT JOIN对这两个表都做一个，并使用它CASE .. WHEN来确定各自的名称。

我已经删除了WHERE .. IN子查询，它在这里似乎没有任何用途。

select item.item_name, 
       CASE purchase.type 
         WHEN 'purchase' THEN supplier.supplier_name 
         WHEN 'issue' THEN officer.officer_name 
       END AS `supplier_name_officer_name`
       purchase.date, 
       purchase.qty, 
       purchase.type 
from purchase 
join item on item.item_id = purchase.item_id 
left join supplier on purchase.sup_id = supplier.sup_id 
left join officer on purchase.sup_id = officer.officer_id

推荐阅读