逗号分隔的字段以在mysql中进行比较 [英] comma separated field to compare in mysql

问题描述

我进行了很多搜索以找到答案，但我找不到.这是我的问题
我有一个名为main_table的表，如下所示:

I searched so much to find an answer but i can't.Here is my question
I have a table named main_table like this:

╔════╦════════════════╦
║ id ║    seat_id     ║
╠════╬════════════════╬
║  1 ║   274115,      ║
║  2 ║  274116,274117,║ 
║  3 ║ 274113,274114, ║
╚════╩════════════════╩

这些seat_id是另一个名为sub_table的表的主键

These seat_id's are primary key of another table named sub_table

╔═════════╦════════════════╦
║ seat_id ║    seat        ║
╠═════════╬════════════════╬
║  274115 ║     186        ║
║  274116 ║     187        ║ 
║  274117 ║     188        ║
║  274118 ║     159        ║
╚═════════╩════════════════╩

我想要所有与main_table的seat_id相关的座位

I want all the seat related to main_table's seat_id

╔════════════╗
║    seat    ║
╠════════════╣
║    186     ║
║    187     ║
║    188     ║
╚════════════╝

到目前为止，我一直在尝试子查询

What i have tried so far is with sub query

select seat from sub_table where seat_id in(select seat_id from main_table)  

那对我没有帮助

推荐答案

您可以使用但是，我建议对表main_table进行规范化，因为将逗号分隔的值像您一样存储在单个字段中始终是个坏主意.

However, I would suggest normalizing table main_table, as it is always a bad idea to store comma separated values in a single field like you do.

此处演示

这篇关于逗号分隔的字段以在mysql中进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！