查询一列中有多个值 [英] 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屋！