查询列中的多个值 [英] Query with multiple values in a column

问题描述

我有一张桌子，像这样:

I have a table like:

id     name            children
1      Roberto         Michael,Dia
2      Maria           John,Alex
3      Mary            Alexandre,Diana

我的问题是； 我想找到谁有一个叫亚历克斯的孩子.

My problem is; I want to find who has a child named Alex.

我无法在SQL中使用"where children = 'Alex'"，因为在同一单元格中有多个名称.

I can't use "where children = 'Alex'" in SQL because I have more than one names in same cells.

所以我用"where children LIKE '%Alex%'"-看起来很聪明，但是 在同一时间，我像亚历克斯一样开始一切:(亚历山大 或我想获取dia，但结果是dia和diana:(

So I use "where children LIKE '%Alex%'" - that looks smart but in the same time i get all start like Alex :( Alexandre or i want to get dia but result is dia and diana :(

如何获取该数据类型的单个Alex?

how can I get single Alex in that data type?

我希望我能用我糟糕的英语来解释我的问题:D

I hope I can explain my problem with my terrible english :D

推荐答案

最好的解决方案是标准化您的架构.您应该有一个单独的表，每个子表都有一行，而不是用逗号分隔的列表.然后，您可以与该表连接以查找具有特定子项的父项.有关此示例，请参见@themite的答案.

The best solution would be to normalize your schema. You should have a separate table with one row for each child, instead of a comma-delimited list. Then you can join with this table to find parent with a specific child. See @themite's answer for an example of this.

但是，如果由于某些原因您不能这样做，则可以使用

But if you can't do that for some reason, you can use FIND_IN_SET:

WHERE FIND_IN_SET('Alex', children)

这篇关于查询列中的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！