问题描述
我有一张像:
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 butin the same time i get all start like Alex :( Alexandreor 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)
这篇关于使用列中的多个值进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！