关于一对多查询的问题

Question

有三张表,message,list和message_list_assoc
一条message可以关联多个list保存在message_list_assoc中
如
message_id list_id
1            1
1            2
1            3
1            4
2            1
2            2
2            3
我要如何得到只跟list_id为1,2,3关联的message_id呢,也就是说查询结果是2.
大家帮帮忙啊!

Answer 1

select message_id from message a
where list_id in(1,2,3)
group by message_id
having count(1)=3

Answer 2

declare @message table(message_id int,list_id int)
insert @message select 1,            1
union all select 1,            2
union all select 1,            3
union all select 1,            4
union all select 2,            1
union all select 2,            2
union all select 2,            3select message_id from  @message
where message_id in
(select message_id from @message
where list_id in(1,2,3)group by message_id having count(1)=3)
group by message_id having count(1)=3
（所影响的行数为 7 行）message_id  
----------- 
2（所影响的行数为 1 行）

Answer 3

select 
    distinct l.message_id 
from 
    list l 
where 
    not exists(select 1 from list where message_id=l.message_id and list_id not in(1,2,3))

Answer 4

declare @message table(message_id int,list_id int)
insert @message select 1,            1
union all select 1,            2
union all select 1,            3
union all select 1,            4
union all select 2,            1
union all select 2,            2
union all select 2,            3select distinct message_id from  @message a
where message_id in(1,2,3)and
exists(select message_id from @message
where message_id=a.message_id
group by message_id having count(1)=3)（所影响的行数为 7 行）message_id  
----------- 
2（所影响的行数为 1 行）

Answer 5

结果不对啊,注意"只"这个条件,虽然message_id为1的也和list1,2,3关联,但是它同时又与list4关联,所以这条记录不满足条件

Answer 6

select message_id from message
where exists(select * from message_list_assoc where list_id = 1 and message_list_assoc.messageid = message.messageid)
and exists(select * from message_list_assoc where list_id = 2 and message_list_assoc.messageid = message.messageid)
exists(select * from message_list_assoc where list_id = 3 and message_list_assoc.messageid = message.messageid)

Answer 7

1            1
1            2
1            3
1            4
2            1
2            2
2            3
SELECT message_id FROM TB A WHERE 
(SELECT COUNT(1) FROM TB B WHERE B.list_id=1) >0 AND (SELECT COUNT(1) FROM TB B WHERE B.list_id=2) >0 
AND (SELECT COUNT(1) FROM TB B WHERE B.list_id=3) >0
GROUP BY message_id HAVING COUNT(1)=3

Answer 8

楼上的如果有重复项就不对了，改改，嘿嘿
select message_id from  @message
where message_id in
(select message_id from @message
where list_id in(1,2,3)group by message_id having count(distinct list_id)=3)
group by message_id having count(distinct list_id)=3

Answer 9

select message_id from message
where exists(select * from message_list_assoc where list_id = 1 and message_list_assoc.messageid = message.messageid)
and exists(select * from message_list_assoc where list_id = 2 and message_list_assoc.messageid = message.messageid)
and exists(select * from message_list_assoc where list_id = 3 and message_list_assoc.messageid = message.messageid)
and not exists(select * from message_list_assoc where (list_id > 3 or list_id < 1) and message_list_assoc.messageid = message.messageid)改一下,现在应该可以了

Answer 10

create table #temp
(message_id int,
list_id int
)
insert into #temp
select '1','1' union all select '1','2' union all select '1','3' union all select '1','4' union all select '2','1' union all select '2','2' union all select '2','3'
select * from #tempselect message_id 
from #temp a 
where exists(select * from #temp where list_id in(1,2,3) and message_id=a.message_id group by message_id)
group by message_id
having count(1)=3
-----------
2