mysql - 在 mysql 条件下选择 IF

问题描述

假设我有 2 张桌子

这是我的小提琴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 |
+----+-----------+---------------------+--------+

with shipping.origin = reprint.origin_id

基本上table reprint是origin_id要打印的条件，那么reprint table会记录记录

所以如果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 reprint table 上的两次 reprint，并且每个 origin_id 包含 3 个 product_id，那么它必须是 3 origin_id 没有 reprint_status 和 6 origin_id with reprint_status = reprint 因为对于 1 origin_id 包含 3 product_id

标签： mysql

mysql - 在 mysql 条件下选择 IF

问题描述

解决方案

推荐阅读