mysql - 使用 SELECT 有条件地填充列

Question

我想从数据库中获取一些表并将它们合并到一个结果查询中。

我有一个产品表:

+-----------------+-----------------------------+------+-----+---------+----------------+
| Field           | Type                        | Null | Key | Default | Extra          |
+-----------------+-----------------------------+------+-----+---------+----------------+
| id              | bigint(20)                  | NO   | PRI | NULL    | auto_increment |
| menu_id         | bigint(20)                  | YES  |     | NULL    |                |
| menu_is_section | int(1)                      | NO   |     | 1       |                |
| company_id      | bigint(20)                  | NO   | MUL | NULL    |                |
| name            | varchar(255)                | NO   |     | NULL    |                |
| description     | mediumtext                  | YES  |     | NULL    |                |
| image           | varchar(255)                | NO   |     | NULL    |                |
| initial_time    | datetime                    | YES  |     | NULL    |                |
| final_time      | datetime                    | YES  |     | NULL    |                |
| archived        | int(1)                      | NO   |     | 0       |                |
| full_image      | varchar(255)                | YES  |     | NULL    |                |
| kind            | enum('PRODUCT','PROMOTION') | NO   |     | PRODUCT |                |
+-----------------+-----------------------------+------+-----+---------+----------------+

product_likes 表:

+------------+------------+------+-----+-------------------+-----------------------------+
| Field      | Type       | Null | Key | Default           | Extra                       |
+------------+------------+------+-----+-------------------+-----------------------------+
| user_id    | bigint(20) | NO   | MUL | NULL              |                             |
| product_id | bigint(20) | NO   | MUL | NULL              |                             |
| liked_on   | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------+------+-----+-------------------+-----------------------------+

还有一个 users 表，其唯一相关字段是 id

我想获取菜单下的所有产品并确定用户是否“喜欢”该给定产品。为此，我正在考虑进行联接来创建一个新列，该新列将创建包含 1 或 0 的“喜欢”列，以查看用户是否喜欢某个产品。

但我有点卡住了，因为我找不到用 MySQL 有条件填充列的方法。

到目前为止我的查询如下所示:

"SELECT id, name, description, concat('$image_base_url/products/', image) AS icon, concat('$image_base_url/products_full/', full_image) AS full_image FROM (SELECT * FROM products WHERE menu_id = ? AND menu_is_section = ?) AS 选择了 WHERE UTC_TIMESTAMP() BETWEENinitial_time AND Final_time ORDER BYinitial_time DESC"

完成它所需的一切就是查找 user_id 是否喜欢 Product_it，并相应地创建列。

最好的方法是什么？

编辑:

我刚刚做了一个 LEFT JOIN，我想我已经接近我想要的了。 “liked”返回 1，但我 99% 确定这是因为 user_id 是 1。当用户不喜欢某个产品时，它返回 NULL。我可以使用这个，但如果我的网络服务器返回 1 和 0 会更好。

"SELECT selected.id, u_likes.user_id AS like, selected.name, selected.description, concat('$image_base_url/products/', selected.image) AS icon, concat('$image_base_url/products_full/', selected.full_image) AS full_image FROM (SELECT * FROM products WHERE menu_id = ? AND menu_is_section = ?) AS selected LEFT JOIN (SELECT * FROM Product_likes WHERE user_id = ?) AS u_likes ON selected.id = u_likes.product_id WHERE UTC_TIMESTAMP () BETWEEN selected.initial_time AND selected.final_time ORDER BY selected.initial_time DESC"

Answer 1

我相信我已经明白了。如果这里有专家请查看我的解决方案:

SELECT selected.id, CASE WHEN u_likes.user_id IS NULL THEN '0' ELSE '1' END AS like, selected.name, selected.description, concat('$image_base_url/products/', selected. image) AS icon, concat('$image_base_url/products_full/', selected.full_image) AS full_image FROM (SELECT * FROM products WHERE menu_id = ? AND menu_is_section = ?) AS selected LEFT JOIN (SELECT * FROM Product_likes WHERE user_id = ?) AS u_likes ON selected.id = u_likes.product_id WHERE UTC_TIMESTAMP() BETWEEN selected.initial_time AND selected.final_time ORDER BY selected.initial_time DESC

似乎正确地将 1 或 0 放入名为“喜欢”的字段中。遗憾的是，我现在无法做太多测试用例，但看起来工作正常。