选择不同于加入
我的表看起来像这样选择不同于加入
Table 1 Table 2 Users Options id name id user_id option ------- --- -------- -------- 1 Donald 1 1 access1 2 John 2 1 access2 3 Bruce 3 1 access3 4 Paul 4 2 access1 5 Ronald 5 2 access3 6 Steve 6 3 access1
现在，我要选择加入这些发现其中只有access1用户 如果我这样做
select t1.id,t1.name,t2.id,t2.user_id,t2.option
from table1 t1, table2 t2
where t1.id=t2.user_id
and option='access1';
这不给我独特的结果，因为在这个例子中，我只需要user_id = 3我的数据已经有这些在数百
我也尝试过类似
select user_id from table2 where option='access1'
and user_id not in (select user_id from table2 where option<>'access1')
还有其他不成功的尝试过，但我坚持在这里
您可以使用EXISTS子查询（技术上，左半连接）执行此操作：
SELECT id, name
FROM table1
WHERE EXISTS(
SELECT * FROM table2
WHERE table1.id = table2.user_id
AND table2.option = 'access1'
)
如果您只想要具有access1而不具有任何其他访问权的用户，请添加NOT EXISTS（左反半连接;有一个词来你的同事留下深刻的印象！）：
AND NOT EXISTS (
SELECT * FROM table2
WHERE table1.id = table2.user_id
AND table2.option <> 'access1'
)
如果你想有只access1用户，我会用聚合：
select user_id
from table2
group by user_id
having min(option) = max(option) and min(option) = 'access1';
这不返回的名称，并没有加入到表的主键（我们不知道正在执行参照完整性）。这也是相当昂贵的，因为像这样计算列的最小值和最大值需要排序或建立临时表。 – cliffordheath
我同意@cliffordheath聚合对我来说会很贵，但是我会尽量试试 – srednivashtar
我使用了聚合，它也返回了0行 – srednivashtar
WITH users(id,name) AS (VALUES
(1,'Donald'),
(2,'John'),
(3,'Bruce'),
(4,'Paul'),
(5,'Ronald'),
(6,'Steve')
), options(id,user_id,option) AS (VALUES
(1,1,'access1'),
(2,1,'access2'),
(3,1,'access3'),
(4,2,'access1'),
(5,2,'access3'),
(6,3,'access1')
), user_access_count AS (
SELECT op.user_id,count(op.option) AS access_count
FROM options op
WHERE EXISTS(
SELECT 1 FROM options
WHERE option = 'access1'
)
GROUP BY op.user_id
)
SELECT u.id,u.name
FROM users u
INNER JOIN user_access_count uac ON uac.user_id = u.id
WHERE uac.access_count = 1;
bool_and变得非常简单
with users (id,name) as (values
(1,'donald'),
(2,'john'),
(3,'bruce'),
(4,'paul'),
(5,'ronald'),
(6,'steve')
), options (id,user_id,option) as (values
(1,1,'access1'),
(2,1,'access2'),
(3,1,'access3'),
(4,2,'access1'),
(5,2,'access3'),
(6,3,'access1')
)
select u.id, u.name
from
users u
inner join
options o on o.user_id = u.id
group by 1, 2
having bool_and(o.option = 'access1')
;
id | name
----+-------
3 | bruce
谢谢，我会马上试试。 – srednivashtar
我试过了，不存在选择在这个方案给我0行 – srednivashtar
有了一些变化又试了一次，在NOT EXISTS我用'AND NOT EXISTS（ SELECT * FROM表2 WHERE = table1.id table2.user_id 和Table 。选项='access1''这似乎工作，但我还没有验证数据尚未 – srednivashtar