有三个表 user_base,user_call; user_friends ,其中的 user_friends表中的user_id friend_id都是user_base表中的user_id.
求出一个用户每个朋友信息和最新发表的一条消息(call_message)。就是message_id或者call_date最大的一条。
[sql]
SELECT * FROM user_base AS ub
RIGHT JOIN (
SELECT IF(user_id=151,friend_id,user_id) AS user_ids,add_date
FROM user_friends
WHERE user_id=151 OR friend_id=151
) AS uf
ON ub.user_id=uf.user_ids
LEFT JOIN user_call AS uc ON uf.user_ids=uc.user_id
GROUP BY uc.user_id
ORDER BY uc.message_id DESC
LIMIT 0, 15[/sql]
这个查出来的都是最晚的消息。mysql> desc user_base;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| user_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_name | char(30) | YES | | NULL | |
| user_nickname | char(50) | YES | | NULL | |
| user_password | char(32) | YES | | NULL | |
| user_date | int(10) unsigned | NO | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)mysql> desc user_call;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| message_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | YES | | NULL | |
| call_message | char(140) | YES | | NULL | |
| call_date | int(10) unsigned | YES | | NULL | |
| view_abled | int(10) unsigned | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)mysql> desc user_friends;
+----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| user_id | int(10) unsigned | YES | | NULL | |
| friend_id | int(10) unsigned | YES | | NULL | |
| add_date | int(10) unsigned | YES | | NULL | |
+----------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
求出一个用户每个朋友信息和最新发表的一条消息(call_message)。就是message_id或者call_date最大的一条。
[sql]
SELECT * FROM user_base AS ub
RIGHT JOIN (
SELECT IF(user_id=151,friend_id,user_id) AS user_ids,add_date
FROM user_friends
WHERE user_id=151 OR friend_id=151
) AS uf
ON ub.user_id=uf.user_ids
LEFT JOIN user_call AS uc ON uf.user_ids=uc.user_id
GROUP BY uc.user_id
ORDER BY uc.message_id DESC
LIMIT 0, 15[/sql]
这个查出来的都是最晚的消息。mysql> desc user_base;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| user_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_name | char(30) | YES | | NULL | |
| user_nickname | char(50) | YES | | NULL | |
| user_password | char(32) | YES | | NULL | |
| user_date | int(10) unsigned | NO | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)mysql> desc user_call;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| message_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | YES | | NULL | |
| call_message | char(140) | YES | | NULL | |
| call_date | int(10) unsigned | YES | | NULL | |
| view_abled | int(10) unsigned | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)mysql> desc user_friends;
+----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| user_id | int(10) unsigned | YES | | NULL | |
| friend_id | int(10) unsigned | YES | | NULL | |
| add_date | int(10) unsigned | YES | | NULL | |
+----------------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
http://blog.csdn.net/ACMAIN_CHM/archive/2009/04/26/4126306.aspx
RIGHT JOIN (
SELECT IF(user_id=151,friend_id,user_id) AS user_ids,add_date
FROM user_friends
WHERE user_id=151 OR friend_id=151
) AS uf
ON ub.user_id=uf.user_ids
LEFT JOIN user_call AS uc ON uf.user_ids=uc.user_id
GROUP BY uc.user_id
ORDER BY uc.message_id DESC
LIMIT 0, 15
这是我写的。实现不了功能。高手请现身帮帮忙吧。
select friend_id from user_friends where user_id=151;每个user_id 最晚的消息id。
select user_id,max(message_id) as max_message_id
from user_call
group by user_id每个user_id 最晚的消息。
select c.*
from user_call c inner join (
select user_id,max(message_id) as max_message_id
from user_call
group by user_id) c1 on c.user_id=c1.user_id and c.message_id=c1.max_message_id这些好友的最晚的消息
select c.*
from user_call c inner join (
select user_id,max(message_id) as max_message_id
from user_call
group by user_id
) c1 on c.user_id=c1.user_id and c.message_id=c1.max_message_id
inner join user_friends f on c.user_id=f.friend_id
where f.user_id=151