超难...一个多对多查询的问题

[benwang6]

一个表有这样的数据

user_id role
-----------|---------
1 | student
1 | writer
2 | writer

我想查询角色(role)既是student同时又是writer的user_id，该怎么查

[gc_ding]

select user_id from 表名
where role ='student' or role ='writer'

[冷箫轻笛]

create table users
(
[user_id] int,
role varchar(20)
)

insert into users select 1,'student'
insert into users select 1,'writer'
insert into users select 2,'writer'

select [user_id]
from users
where role = 'student' or role = 'writer'
group by [user_id]
having count(1) = 2

--结果
1

[benwang6]

那这样查询结果不就是这1和2么，我只要1这个结果怎么办

[gc_ding]

不好意思，写漏了

select user_id from abc
where role ='student' or role ='writer'
group by [user_id]
having count(*) = 2

[砸死牛顿的苹果]

select * from abc where role='student' and user_id in (select user_id where role='writer')

[iamx]

select distinct a.user_id
from abc a, abc b
where a.user_id = b.user_id and a.role = 'student' and b.role = 'writer'

[砸死牛顿的苹果]

或者只取 不重复的 id
select distinct user_id from abc where role='student' and user_id in (select user_id where role='writer')

[iamx]

select a.user_id
from abc a
where a.role = 'student' and exist (select 1 from abc b where a.user_id = b.user_id and b.role = 'writer')

[iamx]

更正：

select distinct a.user_id
from abc a
where a.role = 'student' and exist (select 1 from abc b where a.user_id = b.user_id and b.role = 'writer')

[iamx]

gc_ding(E.T) 的方法是不行的，如果有两条 1 的role 都是 writer or student时就有问题了．

[hhhdyj]

DECLARE @tb TABLE([user_id] int, [role] varchar(10))
INSERT INTO @tb
SELECT 1, 'student'
UNION ALL SELECT 1, 'writer'
UNION ALL SELECT 2, 'writer'

SELECT [user_id] FROM @tb WHERE role IN('student', 'writer')
GROUP BY [user_id] HAVING COUNT(DISTINCT role) > 1

[benwang6]

小刀惋心，谢谢你的行内视图查询，不过这样查的化不太好扩展，比如我查 writer and student and teache and reader ...

E.T兄弟的查询我试了，果然可以查，而且增加条件也方便，我对E.T兄弟只有一个字要说，那就是“佩服佩服”

另外请教E.T兄弟，你对这个查询的效率怎么看？我如果是百万级的数据量如何？谢谢啊

[benwang6]

阿麦兄弟，两条 1 的role 都是 writer or student不可能出现了，因为我加了两个字段的unique，谢谢啊

[砸死牛顿的苹果]

假如user_id 和 role 有做过 联合唯一键 ET的做法是完全正确的
而且效率也算比较快 算法复杂度 属于N的一次方

[砸死牛顿的苹果]

up hhhdyj(萤火虫) 的也完全正确

[gc_ding]

百万级的数据量，上面查询的效率还可以，不过如果你的OR条件越来越多的话，可考虑用IN :)

另外提供以下小建议给楼主参考：

1。select * from t1 where f1 in (select f1 from t2 where t2.fx='x')，
其中子查询的where里的条件不受外层查询的影响，这类查询一般情况下，自动优化会转成exist语句，也就是效率和exist一样

2。select * from t1 where f1 in (select f1 from t2 where t2.fx=t1.fx)，
其中子查询的where里的条件受外层查询的影响，这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少，一般认为效率不如exists

3。当只显示一个表的数据如A，关系条件只一个如ID时，使用IN会更快：
select * from A where id in (select id from B)

4。当只显示一个表的数据如A，关系条件不只一个如ID，col1时，使用IN就不方便了，
可以使用EXISTS：
select * from A where exists (select 1 from B where id = A.id and col1 = A.col1)

5。当只显示两个表的数据时，使用IN，EXISTS都不合适，要使用连接：
select * from A left join B on id = A.id

[wf105]

hhhdyj(萤火虫) 是对的

select user_id from table where role='student'or role='writer'group by user_id
having count(distinct role)> 1 这个没问题的