选择IF on mysql条件

Question

假设我有两张表

这是我的小提琴https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=d8526c5e4eb6e58eb8071b395223d690

发货表：

+----+--------+----------+------------+------------+--------+
| id | origin | order_id | createdAt  | product_id | amount |
+----+--------+----------+------------+------------+--------+
|  1 |      1 |       11 | 2020-12-02 |        234 |   2000 |
|  2 |      1 |       11 | 2020-12-02 |        235 |   3000 |
|  3 |      1 |       11 | 2020-12-02 |        236 |   4000 |
|  4 |      2 |       12 | 2020-12-02 |        236 |   3000 |
|  5 |      2 |       12 | 2020-12-02 |        235 |   2100 |
|  6 |      3 |       13 | 2020-12-02 |        236 |   2200 |
|  7 |      3 |       13 | 2020-12-02 |        239 |   3400 |
|  8 |      4 |       14 | 2020-12-02 |        237 |   2300 |
|  9 |      4 |       14 | 2020-12-02 |        233 |   4000 |
+----+--------+----------+------------+------------+--------+

重新打印表：

+----+-----------+---------------------+--------+
| id | origin_id |      reprintAt      | status |
+----+-----------+---------------------+--------+
|  1 |         1 | 2020-12-03 06:53:53 |      1 |
|  2 |         1 | 2020-12-04 06:53:53 |      1 |
+----+-----------+---------------------+--------+

shipping.origin = reprint.origin_id

基本上，表格重新打印是指当origin_id要打印时，该记录将被记录在重新打印表中

所以我想做一个带有条件的表格，如果origin_id在表格重新打印中，然后使它的状态重新打印，否则为NULL

所以我试着使用这个查询

SELECT shipping.origin,  (SELECT IF(
origin_id = shipping.origin, 'REPRINT', '')
FROM reprint
JOIN shipping ON reprint.origin_id = shipping.origin 
WHERE 
origin_id = shipping.origin) status_reprint
FROM shipping
JOIN reprint ON shipping.origin = reprint.origin_id;

但这似乎与我预期的结果不符

根据表格，我的预期结果如下所示

+-----------------+----------------+
| Shipping Origin | Status_Reprint |
+-----------------+----------------+
|               1 |                |
|               1 |                |
|               1 |                |
|               1 | REPRINT        |
|               1 | REPRINT        |
|               1 | REPRINT        |
|               1 | REPRINT        |
|               1 | REPRINT        |
|               1 | REPRINT        |
|               2 |                |
|               2 |                |
|               3 |                |
|               3 |                |
|               4 |                |
|               4 |                |
+-----------------+----------------+

说明:由于origin_id =1在重印表上重复打印两次，且每个origin_id包含3个product_id，因此它必须是3个不带reprint_status的origin_id，以及6个带reprint_status = reprint的origin_id，因为对于1个origin_id包含3个product_id

Answer 1

SELECT origin `Shipping Origin`, '' Status_Reprint
FROM shipping
UNION ALL
SELECT shipping.origin, 'REPRINT'
FROM shipping
JOIN reprint ON shipping.origin = reprint.origin_id
ORDER BY 1,2

fiddle