如何使用MySQL比较两个逗号分隔的字符串列表 [英] How to compare two comma-separated string lists using MySQL
问题描述
我使用了一个名为"containsAll()"的Java方法来检查ArrayList是否具有共同的内容.
I used a Java method called 'containsAll()' to check if ArrayLists have common content.
比方说,我在MySQL表中有一个列表A(一行)和其他几个列表(在名称"列中,逐行). 所有列表均由逗号分隔的字符串(列表中至少一个字符串)组成-名称或其他名称.
Let's say I have a list A (one row), and several other lists in a MySQL table (in column 'name', row by row). All lists consist of comma-separated Strings (at least one String in a list) - names or whatever.
现在,我想检查列表A中的所有字符串是否都可以在名称"列中的任何行中找到. 结果集应显示名称"中匹配的所有行,包括行/列表的行必须具有列表A中的所有字符串,并且可以具有其他字符串.
Now, I want to check if all Strings in list A can be found in any of the rows in column 'name'. The result set should show all the rows in 'name' that match, that includes rows/lists must have all Strings in list A, and can have additional Strings.
示例I
A:"Mr.T"
____name_________________________________________
'Hannibal'
'Hannibal','Face','Murdock','Mr.T','Donald Duck'
'Face','Donald Duck'
'Superman','Chuck Norris','Mr.T'
_________________________________________________
结果集:汉尼拔",脸部",默多克","T先生",唐老鸭"-并且- '超人',查克·诺里斯','T先生'
Result set: 'Hannibal','Face','Murdock','Mr.T','Donald Duck' -AND- 'Superman',Chuck Norris','Mr.T'
示例II
A:"Rocky","Mr.T","Apollo"
A: 'Rocky', 'Mr.T', 'Apollo'
______name__________________________________________________
'Hannibal','Face','Murdock','Donald Duck','Superman','Mr.T'
'Rocky','Apollo','Ivan'
'Apollo', 'Superman','Hannibal','Rocky','Mr.T','Chuck Norris'
'Rocky','Mr.T','Apollo','Chuck Norris'
_____________________________________________________________
结果集:阿波罗",超人",汉尼拔",洛克","T先生",查克·诺里斯"-并且- 'Rocky','T先生','Apollo','Cuck Norris'
Result set: 'Apollo', 'Superman','Hannibal','Rocky','Mr.T','Chuck Norris' -AND- 'Rocky','Mr.T','Apollo','Cuck Norris'
我想知道是否可以使用MySQL查询来执行那些结果. 预先谢谢你.
I wonder if one can carry out those results using a MySQL query. Thank you in advance.
推荐答案
您似乎想做一个数组交集,除了您的数组是单列.可以做到,但是会很慢,难以调试,并且不会利用关系数据库的功能.更好的方法是将表架构更改为以下形式:
It appears you want to do an array intersection, except your array is a single column. It can be done, but it will be slow, difficult to debug and will not leverage the power of relational databases. A better way would be to change your table schema to something like this:
表组
group_id int unsigned not null auto_increment primary key,
character_list text
表members_in_group
Table members_in_group
group_id int unsigned not null,
group_member varchar(45) not null
然后您可以像这样查询:
Then you can query like this:
SELECT group_id, character_list
FROM groups g
JOIN members_in_groups m USING (group_id)
WHERE m.group_member IN ('Mr. T', ...);
groups
表可能非常类似于您当前的表. members_in_groups
表是相同的数据,分为易于搜索的部分.
The groups
table is probably very like your current table. The members_in_groups
table is the same data chopped up into easily searchable parts.
ETA给出您的评论,如果您可以保证每个character_list
仅包含每个字符的一个实例,这应该有效.
ETA given your comment, this should work if you can guarantee that each character_list
contains only one instance of each character:
SELECT group_id,
SUM(CASE m.group_member IN ('Mr. T', 'Apollo', 'Rocky') THEN 1 ELSE 0 END) AS tally,
character_list
FROM groups g
JOIN members_in_groups m ON (g.group_id=m.group_id)
GROUP BY group_id
HAVING SUM(CASE m.group_member IN ('Mr. T', 'Apollo', 'Rocky') THEN 1 ELSE 0 END) = 3;
在这种情况下,HAVING
子句必须等于3,因为IN ('Mr. T', 'Apollo', 'Rocky')
中有3个成员.
In this case the HAVING
clause must equal 3 because there are 3 members in IN ('Mr. T', 'Apollo', 'Rocky')
.
这篇关于如何使用MySQL比较两个逗号分隔的字符串列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!