以下为一用户兴趣记录表的示例数据,其中User_id为用户编号,Interest_Id为兴趣编号,求出与某用户兴趣完全一致的所有用户(如此示例数据中,与用户编号为1的用户的兴趣完全一致的用户为用户编号为3和8的两位用户),请写出相关的SQL语句(遵循SQL92标准,不得使用T-SQL,PL/SQL等扩展语言)。User_id Interest_Id
1 1
1 2
2 1
2 3
3 1
3 2
4 1
4 2
4 3
5 7
5 8
6 1
7 2
8 1
8 2
9 1
9 3
9 7
9 8
10 9
10 11----附数据生成脚本,方便大家调试
create table tbl(
[user_id] int,
Interest_Id int
)insert tbl
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 2,3 union all
select 3,1 union all
select 3,2 union all
select 4,1 union all
select 4,2 union all
select 4,3 union all
select 5,7 union all
select 5,8 union all
select 6,1 union all
select 7,2 union all
select 8,1 union all
select 8,2 union all
select 9,1 union all
select 9,3 union all
select 9,7 union all
select 9,8 union all
select 10,9 union all
select 10,11
1 1
1 2
2 1
2 3
3 1
3 2
4 1
4 2
4 3
5 7
5 8
6 1
7 2
8 1
8 2
9 1
9 3
9 7
9 8
10 9
10 11----附数据生成脚本,方便大家调试
create table tbl(
[user_id] int,
Interest_Id int
)insert tbl
select 1,1 union all
select 1,2 union all
select 2,1 union all
select 2,3 union all
select 3,1 union all
select 3,2 union all
select 4,1 union all
select 4,2 union all
select 4,3 union all
select 5,7 union all
select 5,8 union all
select 6,1 union all
select 7,2 union all
select 8,1 union all
select 8,2 union all
select 9,1 union all
select 9,3 union all
select 9,7 union all
select 9,8 union all
select 10,9 union all
select 10,11
select userid,id into #bb from abc where id=1
select userid,id into #cc from abc where id=2
select b.userid from #bb b,#cc c where b.userid=c.useriddrop table #bb
drop table #cc
这样行么,这个结果是出来的,经过测试了
不知道符合不符合标准。
我又按你的实际情况加了4和6,你试试这个结果是对的
select userid from abc
group by userid having count(userid)=2 and (max(id)=2 and min(id)=1)
group by User_id having count(User_id)=(select count(User_id) from abc where User_id=1)
and max(Interest_Id)=(select max(Interest_Id) from abc where User_id=1)
and min(Interest_Id)=(select min(Interest_Id) from abc where User_id=1)
group by [User_id] having count([User_id])=(select count([User_id]) from abc where [User_id]=1)