select * from opp where account_id = 1 or account_id in (select account_friend from account where account_id = 1)
select account.account_id,t1.titles from account, ( select account.account_id,account_friend,opp.titles from account inner join opp on account.accountid = opp.accountid ) as t1 where account.account_id = t1.accountid or t1.friend like account.account_id + '|%' or t1.friend like '%|' + account.account_id + '|%'
2 信息A 2 信息B 4 信息A 4 信息B 这些其实不存在的在物理表中,不然就不是信息共享了,LZ意思是通通查询方式逻辑上实现这样的结果, select * from opp where account_id = 1 union select (select account_friend from account where account_id = 1),(select titles from opp where account_id = 1)
以下語句測試OKSelect * From opp Union Select B.account_id, C.titles From account A Inner Join account B On CharIndex('|' + Cast(B.account_id As Varchar) + '|', '|' + A.account_friend) > 0 Inner Join opp C On A.account_id = C.account_id
--創建測試環境 Create Table account( account_id Int, account_friend Varchar(100)) Insert account Select 1, '2|4|' Union All Select 2, '1|' Union All Select 3, '1|' Union All Select 4, '1|2|3|'Create Table opp( account_id Int, titles Nvarchar(20)) Insert opp Select 1, N'信息A' Union All Select 1, N'信息B' GO --測試 Select * From opp Union Select B.account_id, C.titles From account A Inner Join account B On CharIndex('|' + Cast(B.account_id As Varchar) + '|', '|' + A.account_friend) > 0 Inner Join opp C On A.account_id = C.account_id GO --刪除測試環境 Drop Table account, opp --結果 /* account_id titles 1 信息A 1 信息B 2 信息A 2 信息B 4 信息A 4 信息B */
如果这种Friend关系单向且不可传递的话,下面就可以实现,如果可传递的话,那就涉及到图的遍历了.方法是有,不过这里先不考虑这种情形.SELECT c.account_id,b.titles FROM account a,opp b,account c WHERE a.account_id = b.account_id AND CHARINDEX('|' + CAST(a.account_id AS varchar) + '|','|' + CAST(c.account_id AS varchar) + '|' + account_friend)>0
SELECT c.account_id,b.titles FROM account a,opp b,account c WHERE a.account_id = b.account_id AND CHARINDEX('|' + CAST(c.account_id AS varchar) + '|','|' + CAST(a.account_id AS varchar) + '|' + a.account_friend)>0
declare @id int set @id = 1 select b.account_id,opp.titles from opp, (select account_id from account where account_id = @id or CharIndex ('|' + Cast(account_id As Varchar) + '|', '|' + (select account_friend from account where account_id = @id) ) > 0 ) b where opp.account_id = @id
建议用逗号分隔,那样要方便许多。直接可以 select *** from ***_table where *** in(select **** from ****_anothertable)
第二步:查询出account_id=1的好友
第三步:建立临时表temp_opp:循环好友列表 并依次插入temp_opp,其中account_id依次为好友id
第四步:查询临时表 titles like '%+"A"+%' 不知道以上方法可不可行,请高手指点 或有其他更好的办法达到此结果 谢谢
from account,
(
select account.account_id,account_friend,opp.titles
from account inner join opp
on account.accountid = opp.accountid
) as t1
where account.account_id = t1.accountid
or t1.friend like account.account_id + '|%'
or t1.friend like '%|' + account.account_id + '|%'
select * from opp where account_id = 1 or account_id in (select account_friend from account where account_id = 1)
哥们你的算法只能查询出自身的2条而已
1 2
1 4
...这样就比较好实现了,而且纵表的方式要灵活的多!
-------------
或者还有个思路,加一个组表group,一个组_信息表group_opp
把有好友关系的放到一个组里,这个组能访问的信息放到group_opp里,
呵呵,楼主参考一下!
第二步:查询出account_id=1的好友
第三步:建立临时表temp_opp:循环好友列表 并依次插入temp_opp,其中account_id依次为好友id
第四步:查询临时表 titles like '%+"A"+%'
高手帮忙看一下我这个算法可以么
1、有点搞不懂这种储存方式“1|2|3|”的优势在哪里?节约空间?感觉得不尝失。
2、当account_id=1或2或4的用户登录后都看到信息A、信息B就可以了,没有必要将每个人每时每刻所对应的信息全列出……或者请LZ说说这样列出来的作用。
1.这样的方式是因为怕会员多了查询费时,比如说有1W会员 每个会员有100好友,那么整个好友表里就需要100W条记录,太旁大了,而且有弊端,所以用分割的方式 只需要1W条记录就可以呵呵
2.这个是客户的要求,他的意思是实现共享,也就是只要这个会员有的东西,那么搜索的时候也将他的信息作为他好友的信息
呵呵
---------------------------------
wen01071081() ( ) 信誉:100 2007-08-27 17:10:25 得分: 0
select * from opp where account_id = 1 or account_id in (select account_friend from account where account_id = 1)
哥们你的算法只能查询出自身的2条而已
------------------------------------
如果你的存储格式可以改的话
这个办法可以
2 信息B
4 信息A
4 信息B
这些其实不存在的在物理表中,不然就不是信息共享了,LZ意思是通通查询方式逻辑上实现这样的结果,
select * from opp where account_id = 1 union select (select account_friend from account where account_id = 1),(select titles from opp where account_id = 1)
Union
Select B.account_id, C.titles From account A Inner Join account B On CharIndex('|' + Cast(B.account_id As Varchar) + '|', '|' + A.account_friend) > 0 Inner Join opp C On A.account_id = C.account_id
Create Table account(
account_id Int,
account_friend Varchar(100))
Insert account Select 1, '2|4|'
Union All Select 2, '1|'
Union All Select 3, '1|'
Union All Select 4, '1|2|3|'Create Table opp(
account_id Int,
titles Nvarchar(20))
Insert opp Select 1, N'信息A'
Union All Select 1, N'信息B'
GO
--測試
Select * From opp
Union
Select B.account_id, C.titles From account A Inner Join account B On CharIndex('|' + Cast(B.account_id As Varchar) + '|', '|' + A.account_friend) > 0 Inner Join opp C On A.account_id = C.account_id
GO
--刪除測試環境
Drop Table account, opp
--結果
/*
account_id titles
1 信息A
1 信息B
2 信息A
2 信息B
4 信息A
4 信息B
*/
WHERE a.account_id = b.account_id
AND CHARINDEX('|' + CAST(a.account_id AS varchar) + '|','|' + CAST(c.account_id AS varchar) + '|' + account_friend)>0
WHERE a.account_id = b.account_id
AND CHARINDEX('|' + CAST(c.account_id AS varchar) + '|','|' + CAST(a.account_id AS varchar) + '|' + a.account_friend)>0
set @id = 1
select b.account_id,opp.titles from opp,
(select account_id from account
where
account_id = @id
or
CharIndex
('|' + Cast(account_id As Varchar) + '|',
'|' +
(select account_friend from account where account_id = @id)
) > 0
) b where opp.account_id = @id
select *** from ***_table where *** in(select **** from ****_anothertable)