选择与所有数组值匹配的结果 [英] Select result that match ALL array values

问题描述

我想解决这个问题已经有一段时间了，但我似乎无法自己解决.

I'm trying to solve this for quite a moment now and I don't seem to be able to do it by myself.

我想存储链接到ID的OPTIONS，并在需要时获得与所有所需OPTIONS匹配的结果.我考虑过这样做:

I'd like to store OPTIONS linked to IDs, and when needed, get the results that match all wanted OPTIONS. I thought about doing it this way:

ID    |    OPTION

aaa   |  1
aaa   |  2
aaa   |  3

bbb   |  1
bbb   |  2

ccc   |  1
ccc   |  2
ccc   |  5
ccc   |  7

ID和OPTION是外键.

Where ID and OPTION are FOREIGN KEYS.

最终请求看起来像

options_wanted(1,2,5,7)
SELECT * FROM main_table 
WHERE crit1=... 
AND crit2=... 
AND (ALL OPTIONS ARE FOUND IN options TABLE)

我可以使其正常工作还是应该更改实施方式?

Can I make it work or should I change the implementation?

您对我有什么建议?

感谢 https://stackoverflow.com/a/7505147/2512108 ，我几乎找到了我想要的东西.

Thanks to https://stackoverflow.com/a/7505147/2512108, I almost found what I want.

他的查询有效，但最后一列仅给出第一个选项.有没有办法让它返回所有可用的选项(不仅是想要的选项)?

His query works but the last column only gives the 1st option alone. Is there a way to make it return ALL the options AVAILABLE (not only the wanted ones) ?

推荐答案

答案:

select item_id, group_concat(option_id order by option_id asc) options
  from options
  where option_id in (1, 2, 3)
  group by item_id
  having count(option_id) = 3

提琴: http://sqlfiddle.com/#!9/04f69/3

由于没有真正明确提及表模式，因此我将把与其他表的联接以及其他条件留给您.

I'll leave the joining to your other table up to you, as well as the other criteria since the table schema isn't really explicitly mentioned.

编辑 不，我不会，我讨厌一半的答案.

EDIT No I won't, I hate half an answer.

select item_id, group_concat(option_id order by option_id asc) options
  from main_table m
  inner join options o
    on m.id = o.item_id
  where option_id in (1, 2, 3)
    AND crit1 = 2
    AND crit2 = 3
  group by item_id
  having count(option_id) = 3

更新的提琴: http://sqlfiddle.com/#!9/45bee/1

如果您希望它返回至少具有所有REQUIRED选项的项的所有可用选项，则您的查询如下:

And if you want it to return ALL options available to an item that has at minimum all of the REQUIRED options, your query is this:

 select o.item_id, group_concat(o.option_id) options
   from options o
   inner join (
     select item_id
       from main_table m
       inner join options o
         on m.id = o.item_id
       where option_id in (1, 2, 3)
       AND crit1 = 2
       AND crit2 = 3
     group by item_id
     having count(option_id) = 3

在此处进行最后的拨弄: http://sqlfiddle.com/#!9/d60b3/1

With a final fiddle here: http://sqlfiddle.com/#!9/d60b3/1

这篇关于选择与所有数组值匹配的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！