给大家出个SQL题,轻松一下。

此人已死,有事烧纸 发布于 2012/04/11 06:19
阅读 409
收藏 1

有一个表u_link,用来表明‘主用户’关注了‘副用户’。

结构如下:

u_id;主用户字段

u_2_id;副用户字段

问题:找出与用户16互相关注的用户。

 

 

以下是问题补充:

@此人已死,有事烧纸:在thinkphp中怎么写? (2012/04/11 06:25)
加载中
0
此人已死,有事烧纸
此人已死,有事烧纸
select u_2_id from u_link where u_id=16 u_2_id in(select u_id where u_2_id=16 )
这样写对不对啊?上学的时候写过,现在怎么也想不起来了

0
此人已死,有事烧纸
此人已死,有事烧纸

table("u_link u,u_link u2")->where("u.u_id=16 and u2.u_2_id=16 and u.u_2_id=u2.u_id")->select();

thinkphp中,不知对否

0
无名人士
无名人士
select u1.u_id from u_link as u1 
inner join u_link as u2
on u1.u_2_id = u2.u_id 
and u1.u_id = u2.u_2_id
where u1.u_2_id = 16

这样呢
0
无名人士
无名人士

引用来自“流云诸葛”的答案

select u1.u_id from u_link as u1 
inner join u_link as u2
on u1.u_2_id = u2.u_id 
and u1.u_id = u2.u_2_id
where u1.u_2_id = 16

这样呢
mysql> select * from u_link;
+------+--------+
| u_id | u_2_id |
+------+--------+
|    1 |      2 |
|    1 |      3 |
|    1 |     16 |
|    2 |      3 |
|    2 |     16 |
|    3 |      2 |
|    3 |     16 |
|   16 |      2 |
|   16 |      3 |
+------+--------+
9 rows in set (0.00 sec)

mysql> select u1.u_id from u_link
    -> as u1 inner join u_link as u2
    -> on u1.u_2_id = u2.u_id
    -> and u1.u_id = u2.u_2_id
    -> where u1.u_2_id = 16;
+------+
| u_id |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)


0
白黑白
白黑白

select uid,u2id   from ( select u_id as uid,0 as u_2_id from u_link

where u_2_id=16 union
select 0 as uid, u_2_id as u2id from u_link where u_id=16)

0
泡不烂的凉粉
泡不烂的凉粉

select u_2_id from u_link where u_id=16 and u_2_id in(select u_id where u_2_id=16 )

0
酒逍遥
酒逍遥

select u_id from u_link where u_2_id=16 and u_id in (SELECT u_2_id FROM u_link WHERE u_id=16)

Thinkphp里M('U_link')->field('u_id')->where('u_2_id=16 and u_id in (SELECT u_2_id FROM u_link WHERE u_id=16)')->findall();

0
酒逍遥
酒逍遥

来个复杂版的

select u_id,count(u_id) as c from (select u_2_id as u_id from u_link where u_id =16 union all select u_id from u_link where u_2_id =16) t group by t.u_id having c>1 

返回顶部
顶部