国内某知名互联网企业的一道压轴笔试题,该怎么解决

国内某知名互联网企业的一道压轴笔试题
以下为一用户兴趣记录表的示例数据，其中User_id为用户编号,Interest_Id为兴趣编号，求出与某用户兴趣完全一致的所有用户(如此示例数据中，与用户编号为1的用户的兴趣完全一致的用户为用户编号为3和8的两位用户)，请写出相关的SQL语句(遵循SQL92标准，不得使用T-SQL,PL/SQL等扩展语言)。

User_id Interest_Id
1 1
1 22 1
2 3
3 1
3 24 1
4 2
4 3
5 7
5 8
6 1
7 2
8 1
8 29 1
9 3
9 7
9 8
10 9
10 11

------解决方案--------------------
SELECT * FROM TB T WHERE User_id
IN(
SELECT User_id FROM TB
WHERE Interest_Id IN(SELECT Interest_Id FROM TB WHERE User_id=1)
GROUP BY USER_ID HAVING COUNT(1)=(SELECT COUNT(1) FROM TB WHERE User_id=1)
)
------解决方案--------------------

SQL code

select id from tb where Interest_Id=(select top 1 Interest_Id from tb where User_id=1) and User_id<>1

------解决方案--------------------
我插，被忽悠了

SQL code

select id from tb where Interest_Id=(select top 1 Interest_Id from tb where [User_id]=1) and [User_id]<>1

------解决方案--------------------
又被忽悠了

SQL code


select [User_id] from tb
where Interest_Id=(select top 1 Interest_Id from tb where [User_id]=1)
and [User_id]<>1

------解决方案--------------------
1 1
1 22 1
2 3
3 1
3 24 1
4 2
4 3
5 7
5 8
6 1
7 2
8 1
8 29 1
9 3
9 7
9 8
10 9
10 11

麻烦楼主把红色部分解释一下

------解决方案--------------------

探讨

引用:
引用:
1 1
1 22 1
2 3
3 1
3 24 1
4 2
4 3
5 7
5 8
6 1
7 2
8 1
8 29 1
9 3
9 7
9 8
10 9
10 11

麻烦楼主把红色部分解释一下

User_id Interest_Id
1 1
1 2
2 ……

------解决方案--------------------
1 22 1
第二行中的最后这个1是你标注上去显示明显，还是里面有这个1值。
同理，3号，和8号都有。
如果有这个1值，我再写过

题目有点不清晰，不知道这样理解的题目，写得对不对！

SQL code


create table abc
(
    userid int,
    Interestid int
);
insert into abc values (1,1)
insert into abc values (1,22)
insert into abc values (2,3)
insert into abc values (3,1)
insert into abc values (3,24)
insert into abc values (4,2)
insert into abc values (4,3)
insert into abc values (4,4)
insert into abc values (4,5)
insert into abc values (5,8)
insert into abc values (6,1)
insert into abc values (7,4)
insert into abc values (8,1)
insert into abc values (8,29)
insert into abc values (9,11)

select userid from abc where Interestid in 
(select count(Interestid)*10+userid+(select top 1 Interestid from abc) as row from abc group by userid)

------解决方案--------------------
/*
M
as
(
select distinct a.*,
case when a.zd1=b.zd1 then null else b.zd1 end as col1,
case when a.zd2=b.zd2 then null else b.zd2 end as col2,
case when a.zd1=b.zd1 and a.zd2=b.zd2 and a.zd3=b.zd3 then null else b.zd3 end as col3,
case when a.zd1=b.zd1 and a.zd2=b.zd2 and a.zd4=b.zd4 then null else b.zd4 end as col4  
from T a inner join T b on a.zd1=b.zd1
)
select *from M
*/
select
 case when px=1 then zd1 else null end as zd1,
 case when px=1 then zd2 else null end as zd2,
 case when a.zd3=zd3 then zd3 else null end as zd3,