>类别表
+----+-----------------------+
| id | category_name |
+----+-----------------------+
| 1 | Buy Book |
| 2 | Buy other thinks |
+----+-----------------------+
购买表
+----+-----------------------+----------+-------------+----------+--------+-------+
| id | identity | name | description | per_rate | bought | costs |
+----+-----------------------+----------+-------------+----------+--------+-------+
| 1 | PROJECT[1]CATEGORY[1] | BOOK | PHP BOOK | 10 | 50 | 5000 |
| 2 | PROJECT[1]CATEGORY[1] | BOOK | PHP BOOK | 10 | 40 | 4000 |
| 3 | PROJECT[2]CATEGORY[1] | BOOK | JS BOOK | 2 | 50 | 100 |
+----+-----------------------+----------+-------------+----------+--------+-------+
当我选择此表时,我想从其他表中选择类别名称。
身份: 项目[project_id]类别[category_id]
所以有什么方法可以选择类别ID并从其他表中选择类别名称
我想要像这张桌子
+----+---------------+-----------------------+----------+-------------+----------+--------+-------+
| id | category_name | identity | name | description | per_rate | bought | costs |
+----+---------------+-----------------------+----------+-------------+----------+--------+-------+
| 1 | Buy Book | PROJECT[1]CATEGORY[1] | BOOK | PHP BOOK | 10 | 50 | 5000 |
| 2 | Buy Book | PROJECT[1]CATEGORY[1] | BOOK | PHP BOOK | 10 | 40 | 4000 |
| 3 | Buy Book | PROJECT[2]CATEGORY[1] | BOOK | JS BOOK | 2 | 50 | 100 |
+----+---------------+-----------------------+----------+-------------+----------+--------+-------+
你的数据结构非常糟糕。 project
和category
应位于各自的列中,数字应正确存储为数字,并具有正确的外键关系。 在MySQL中,这样做可能需要触发器,但这是值得的。
有时,我们会被别人的错误决定所困。 您可以使用like
做您想做的事:
select b.*, c.category_name
from buy b join
category c
on b.identity like concat('%CATEGORY[', c.id, ']');
但是,您可能应该努力修复损坏的数据结构。