1.一张表中有用户信息表user(user_id,nickname),另外一张表联系人表contact(user_id,friend_id).现在要找出联系人的信息
|
1
|
SELECT
U1.nickname
as
myselft,U2.nickname
as
friend
FROM
contact C
inner
join
user
U1
on
C.user_id=U1.user_id
inner
join
user
U2
on
C.friend_id=U2.id
|
以上如果friend_id可能为空的话，则后面那个inner join 改为left join.
2.查找出好友及自己发的帖子,帖子表invitation(id,owner_id,title,content)
|
1
2
3
4
|
SELECT
*
FROM
( invitation I
inner
join
user
U
on
U.user_id=I.owner_id )
inner
join
(
select
friend_id
from
contacts
where
user_id=2
union
all
select
2)
as
B
on
B.friend_id=I.user_id
|
在mysql中就不要使用子查询了，mysql 5.5以前的版本对子查询效率极差。
本文转自 rong341233 51CTO博客，原文链接:http://blog.51cto.com/fengwan/1672746
