什么是实现其中一个阵列的所有元素都必须匹配的条款最简单和最快的方法-使用时不能只有一个IN ？ 毕竟它应该表现得像MongoDB的$所有 。
想着组通话情况conversation_users是conversation_id与USER_ID之间的连接表我有这样的事情在脑海：
WHERE (conversations_users.user_id ALL IN (1,2))
更新 12年7月16日
加入约模式和案例的详细信息：
联接表是相当简单：
Table "public.conversations_users" Column | Type | Modifiers | Storage | Description -----------------+---------+-----------+---------+------------- conversation_id | integer | | plain | user_id | integer | | plain |
一个对话有许多用户和用户所属的多次交谈。 为了找到会话的所有用户，我使用这个连接表。
最终，我试图找出在轨道上红宝石scope是发现是我根据这一个对话的参与者-例如：
scope :between, ->(*users) { joins(:users).where('conversations_users.user_id all in (?)', users.map(&:id)) }
更新 12年7月23日
我的问题是要找到的人完全匹配。 因此：
之间的对话(1,2,3)如果查询将不匹配(1,2)
Answer 1:
假设连接表如下很好的做法，有一个约束，以防止重复行，然后像下面这样简单的查询应该做定义的唯一复合键，即。
select conversation_id from conversations_users where user_id in (1, 2)
group by conversation_id having count(*) = 2
需要注意的是，在最后的数字2是user_ids的列表的长度是很重要的。 这显然需要改变，如果USER_ID列表改变长度。 如果您无法将连接表不包含重复，变“COUNT（*）”，以“计数（不同的user_id）”在性能上的一些可能的成本假设。
该查询查找所有的对话，包括所有指定的用户即使交谈也包括其他用户。
如果您希望只与完全指定的一组用户的对话，一种方法是如下的where子句中使用嵌套子查询。 注意，第一个和最后一个行是一样的原始查询，只有中间两行是新的。
select conversation_id from conversations_users where user_id in (1, 2)
and conversation_id not in
(select conversation_id from conversation_users where user_id not in (1,2))
group by conversation_id having count(*) = 2
同样地，你可以，如果你的数据库支持它使用一组差分算。 下面是Oracle语法的例子。 （有关的Postgres或DB2，变更关键字“减”到“除外）。
select conversation_id from conversations_users where user_id in (1, 2)
group by conversation_id having count(*) = 2
minus
select conversation_id from conversation_users where user_id not in (1,2)
良好的查询优化器应该同样对待过去两年的变化，但与你的特定数据库检查，以确保万无一失。 例如，在Oracle 11gR2的查询计划将减号来进行排序前两组对话的ID，但跳过最后一个查询的排序步骤。 因此，无论查询计划可能会更快取决于多种因素，如行，内核，缓存，索引等数
Answer 2:
我崩溃的用户到一个数组。 我还使用一个CTE（WITH子句中的东西），使这个更具可读性。
=> select * from conversations_users ;
conversation_id | user_id
-----------------+---------
1 | 1
1 | 2
2 | 1
2 | 3
3 | 1
3 | 2
(6 rows)
=> WITH users_on_conversation AS (
SELECT conversation_id, array_agg(user_id) as users
FROM conversations_users
WHERE user_id in (1, 2) --filter here for performance
GROUP BY conversation_id
)
SELECT * FROM users_on_conversation
WHERE users @> array[1, 2];
conversation_id | users
-----------------+-------
1 | {1,2}
3 | {1,2}
(2 rows)
编辑 （一些资源）
- 阵列功能： http://www.postgresql.org/docs/9.1/static/functions-array.html
- 热膨胀系数： http://www.postgresql.org/docs/9.1/static/queries-with.html
Answer 3:
虽然@Alex”与答案IN和count()可能是最简单的解决方案，我希望这个PL / pgSQL函数是速度快：
CREATE OR REPLACE FUNCTION f_conversations_among_users(_user_arr int[])
RETURNS SETOF conversations AS
$BODY$
DECLARE
_sql text := '
SELECT c.*
FROM conversations c';
i int;
BEGIN
FOREACH i IN ARRAY _user_arr LOOP
_sql := _sql || '
JOIN conversations_users x' || i || ' USING (conversation_id)';
END LOOP;
_sql := _sql || '
WHERE TRUE';
FOREACH i IN ARRAY _user_arr LOOP
_sql := _sql || '
AND x' || i || '.user_id = ' || i;
END LOOP;
/* uncomment for conversations with exact list of users and no more
_sql := _sql || '
AND NOT EXISTS (
SELECT 1
FROM conversations_users u
WHERE u.conversation_id = c.conversation_id
AND u.user_id <> ALL (_user_arr)
)
*/
-- RAISE NOTICE '%', _sql;
RETURN QUERY EXECUTE _sql;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;
呼叫：
SELECT * FROM f_conversations_among_users('{1,2}')
动态地构建函数执行以下形式的查询：
SELECT c.*
FROM conversations c
JOIN conversations_users x1 USING (conversation_id)
JOIN conversations_users x2 USING (conversation_id)
...
WHERE TRUE
AND x1.user_id = 1
AND x2.user_id = 2
...
这种形式在表现最好的关系部门查询广泛的测试 。
你也可以构建查询您的应用程序，但我去的，你想用一个阵列参数的假设。 此外，这可能是最快的呢。
无论是查询需要像要快以下内容的索引 ：
CREATE INDEX conversations_users_user_id_idx ON conversations_users (user_id);
多列主（或唯一）键的(user_id, conversation_id)是一样好，但一对(conversation_id, user_id)就像你很可能有！）会逊色 。 你会发现在链路短理由上方或下的dba.SE此相关的问题综合评估
我还假设你有一个主键conversations.conversation_id 。
你可以运行一个性能测试EXPLAIN ANALYZE上@Alex”查询，这一功能和报告您的发现？
请注意，这两种解决方案找到谈话，其中阵列至少在用户的参与-包括与其他用户交谈。
如果要排除那些，取消注释在我的功能附加条款（或将其添加到任何其他查询）。
如果你需要的功能特性的更多的解释告诉我。
Answer 4:
这保留了ActiveRecord对象。
在下面的例子中，我想知道其与阵列中的所有代码相关联的时间片。
codes = [8,9]
Timesheet.joins(:codes).select('count(*) as count, timesheets.*').
where('codes.id': codes).
group('timesheets.id').
having('count(*) = ?', codes.length)
你应该有充分ActiveRecord对象一起工作。 如果你希望它是一个真正的范围，你可以用你上面的例子中，并在结果通过与.pluck(:id)
Answer 5:
创建一个映射表中所有可能的值，并使用此
select
t1.col from conversations_users as t1
inner join mapping_table as map on t1.user_id=map.user_id
group by
t1.col
having
count(distinct conversations_users.user_id)=
(select count(distinct user_id) from mapping)
Answer 6:
select id from conversations where not exists(
select * from conversations_users cu
where cu.conversation_id=conversations.id
and cu.user_id not in(1,2,3)
)
这可以很容易地制作成一个轨道范围。
Answer 7:
我猜测，你不是真的要开始使用临时表搞乱。
你的问题不清楚，你是否想完全组用户，或与超谈话的谈话。 以下是超集：
with users as (select user_id from users where user_id in (<list>)
),
conv as (select conversation_id, user_id
from conversations_users
where user_id in (<list>)
)
select distinct conversation_id
from users u left outer join
conv c
on u.user_id = c.user_id
where c.conversation_id is not null
对于该查询工作得很好，它假定你有在用户和conversations_users USER_ID指标。
对于确切的集合。 。 。
with users as (select user_id from users where user_id in (<list>)
),
conv as (select conversation_id, user_id
from conversations_users
where user_id in (<list>)
)
select distinct conversation_id
from users u full outer join
conv c
on u.user_id = c.user_id
where c.conversation_id is not null and u.user_id is not null
Answer 8:
基于@Alex布莱克莫尔的回答，相当于Rails的你4范围Conversation类将是：
# Conversations exactly with users array
scope :by_users, -> (users) {
self.by_any_of_users(users)
.group("conversations.id")
.having("COUNT(*) = ?", users.length) -
joins(:conversations_users)
.where("conversations_users.user_id NOT IN (?)", users)
}
# generates an IN clause
scope :by_any_of_users, -> (users) { joins(:conversations_users).where(conversations_users: { user_id: users }).distinct }
请注意，您可以优化它，而不是做一个Rails -减号），你可以做一个.where("NOT IN")但是这将是非常复杂的阅读。
文章来源: PostgreSQL where all in array
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://img.manongdao.com/sparkler-677774__340.jpg', '推荐 傲 的文章《PostgreSQL的，其中所有在阵列(PostgreSQL where all in array)》','https://www.manongdao.com/article-965982.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}