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

问题描述

我有一张像:

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.

但是如果由于某种原因你不能这样做，你可以使用 FIND_IN_SET:

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

WHERE FIND_IN_SET('Alex', children)

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