sql好友关系查询,好友表设计
我现在有一张表,具体设计是这样的
id, Uid, Fid, Userimp, Addtime 对应解释如下 (自增长编号,用户编号,好友编号,好友印象,添加时间)
插入时候数据如下 :
1 100 101 '' 2012-01-12
2 100 102 '' 2012-01-12
3 100 103 '' 2012-01-12
4 100 104 '' 2012-01-12
5 101 100 '' 2012-01-13
6 101 102 '' 2012-01-13
7 105 101 '' 2012-01-14
具体说明如下 :用户编号为 100 的人添加了4个好友 用户编号为 101的人添加了2个好友 而用户编号为 105 的添加了1个好 友
我现在要查询到三种情况:第一就是我添加了别的用户为好友别的用户也添加了我为好友 (如:100和101)
第二就是我添加了别的用户为好友但是别的用户没有加我好友 (如:100和103)
第三就是别人添加了我为好友我却没有加别人为好友 (如:105和101)
小弟愚钝 ,第一种情况比较好些 ,但是第二种和第三种就不会写了 ,望各位不吝赐教 !
[解决办法]
- SQL code
--> 测试数据: #TBif object_id('tempdb.dbo.#TB') is not null drop table #TBcreate table #TB (id int,Uid int,Fid int,Userimp datetime,Addtime sql_variant)insert into #TBselect 1,100,101,'2012-01-12',null union allselect 2,100,102,'2012-01-12',null union allselect 3,100,103,'2012-01-12',null union allselect 4,100,104,'2012-01-12',null union allselect 5,101,100,'2012-01-13',null union allselect 6,101,102,'2012-01-13',null union allselect 7,105,101,'2012-01-14',null--1select * from #TB T WHERE EXISTS(SELECT 1 FROM #TB WHERE UID=T.FID AND FID=T.UID)---2select * from #TB T WHERE EXISTS(SELECT 1 FROM #TB WHERE T.UID=FID AND T.FID<>UID)AND NOT EXISTS(SELECT 1 FROM #TB WHERE UID=T.FID AND FID=T.UID)--3select * from #TB T WHERE EXISTS(SELECT 1 FROM #TB WHERE T.FID=UID AND T.UID<>FID)AND NOT EXISTS(SELECT 1 FROM #TB WHERE UID=T.FID AND FID=T.UID)/*（所影响的行数为 7 行）id Uid Fid Userimp Addtime ----------- ----------- ----------- ------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------- 1 100 101 2012-01-12 00:00:00.000 NULL5 101 100 2012-01-13 00:00:00.000 NULL（所影响的行数为 2 行）id Uid Fid Userimp Addtime ----------- ----------- ----------- ------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------- 2 100 102 2012-01-12 00:00:00.000 NULL3 100 103 2012-01-12 00:00:00.000 NULL4 100 104 2012-01-12 00:00:00.000 NULL6 101 102 2012-01-13 00:00:00.000 NULL（所影响的行数为 4 行）id Uid Fid Userimp Addtime ----------- ----------- ----------- ------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------- 7 105 101 2012-01-14 00:00:00.000 NULL（所影响的行数为 1 行）