oracle用户登录验证语句,Oracle SQL如何编写一个sql语句来验证我的网络中的用户(即朋友或朋友的朋友)...

SELECT *

FROM (

SELECT username

FROM friends

START WITH

username = 'myname'

CONNECT BY

friendname = PRIOR username

AND level <= 3

)

WHERE username = 'friendname'

AND rownum = 1

根据需要更新级别：您可以搜索第三层朋友等.

如果友谊关系是对称的,则应进行以下查询：

WITH q AS

(

SELECT username, friendname

FROM friends

UNION ALL

SELECT friendname, username

FROM friends

),

f AS

(

SELECT friendname, level

FROM q

START WITH

username = 'Thomas'

CONNECT BY NOCYCLE

username = PRIOR friendname

)

SELECT *

FROM f

WHERE friendname = 'Jo'

AND rownum = 1

如果对表进行非规范化,则可以更快地进行此查询：每个友谊存储两条记录,如下所示：

CREATE TABLE dual_friends (orestes NOT NULL, pylades NOT NULL, CONSTRAINT pk_dualfriends_op PRIMARY KEY (orestes, pylades)) ORGANIZATION INDEX

AS

SELECT username, friendname

FROM friends

UNION ALL

SELECT friendname, username

FROM friends

然后你可以用dual_friends替换上面的CTE：

WITH f AS

(

SELECT pylades, level

FROM dual_friends

START WITH

orestes = 'Thomas'

CONNECT BY NOCYCLE

orestes = PRIOR pylades

AND level <= 3

)

SELECT *

FROM f

WHERE pylades = 'Jo'

AND rownum = 1

,它将使用索引并且效率更高,特别是如果您将级别限制为某个合理的值.