4个表,box是盒子,
note是消息,
box_user是哪些用户在哪些盒子里聊天。
box_note是哪些消息在哪些盒子里(消息可在多盒共享)。盒子就和微信的一个聊天框一样,消息就是里面的消息。
mysql> describe box;
+---------------------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+------------+------+-----+---------+----------------+
| box_id | bigint(20) | NO | PRI | NULL | auto_increment |
| type | tinyint(4) | NO | | NULL | |
| status_type | char(1) | NO | | NULL | |
| create_time | datetime | NO | | NULL | |
| delete_time_from_one_part | datetime | NO | | NULL | |
+---------------------------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)mysql> describe box_user;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| user_id | bigint(20) | NO | PRI | 0 | |
| box_id | bigint(20) | NO | PRI | 0 | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)mysql> describe note;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| note_id | bigint(20) | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) | NO | MUL | NULL | |
| type | tinyint(4) | NO | | NULL | |
| content | text | NO | | NULL | |
| mood | tinyint(4) | NO | | NULL | |
| locate | varchar(30) | NO | | none | |
| privacy | char(1) | NO | | 1 | |
| create_time | datetime | NO | MUL | NULL | |
| delay | int(11) | NO | | 0 | |
| festival | char(30) | NO | | NULL | |
| delete_time | datetime | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)mysql> describe box_note;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| note_id | bigint(20) | NO | PRI | 0 | |
| box_id | bigint(20) | NO | PRI | 0 | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
想法很简单,试了半天都失败:想根据user_id获取该user的所有盒子以及每个盒子最后一条消息的时间。我准备了这两个基本SQL语句,//获取所有user_id用户的盒子
select box.box_id,type,status_type from box,box_user where box.box_id=box_user.box_id and box_user.user_id=1;
//获取所有内部具有消息的user_id用户的盒子以及最后更新时间
select box_id,max(create_time) as time from note,box_note where note.note_id=box_note.note_id and note.user_id=1 group by box_id;它们分别执行结果:
mysql> select box.box_id,type,status_type from box,box_user where box.box_id=box_user.box_id and box_user.user_id=1;
+--------+------+-------------+
| box_id | type | status_type |
+--------+------+-------------+
| 1 | 0 | 0 |
| 6 | 1 | 0 |
| 7 | 3 | 0 |
| 8 | 3 | 0 |
+--------+------+-------------+
4 rows in set (0.00 sec)mysql> select box_id,max(create_time) as time from note,box_note where note.note_id=box_note.note_id and note.user_id=1 group by box_id;
+--------+---------------------+
| box_id | time |
+--------+---------------------+
| 1 | 2012-05-21 00:00:00 |
| 6 | 2012-05-30 00:00:00 |
+--------+---------------------+
2 rows in set (0.00 sec)给这个例子的目的就是说,我要用left join把两个结果集串起来,以第一个结果集为基准,没有对应的最后时间就显示个null或者0000-00-00 00:00:00之类的都可以了。我彻头彻尾的失败了,left join子查询一直报各种错,坐等解答。
note是消息,
box_user是哪些用户在哪些盒子里聊天。
box_note是哪些消息在哪些盒子里(消息可在多盒共享)。盒子就和微信的一个聊天框一样,消息就是里面的消息。
mysql> describe box;
+---------------------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+------------+------+-----+---------+----------------+
| box_id | bigint(20) | NO | PRI | NULL | auto_increment |
| type | tinyint(4) | NO | | NULL | |
| status_type | char(1) | NO | | NULL | |
| create_time | datetime | NO | | NULL | |
| delete_time_from_one_part | datetime | NO | | NULL | |
+---------------------------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)mysql> describe box_user;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| user_id | bigint(20) | NO | PRI | 0 | |
| box_id | bigint(20) | NO | PRI | 0 | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)mysql> describe note;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| note_id | bigint(20) | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) | NO | MUL | NULL | |
| type | tinyint(4) | NO | | NULL | |
| content | text | NO | | NULL | |
| mood | tinyint(4) | NO | | NULL | |
| locate | varchar(30) | NO | | none | |
| privacy | char(1) | NO | | 1 | |
| create_time | datetime | NO | MUL | NULL | |
| delay | int(11) | NO | | 0 | |
| festival | char(30) | NO | | NULL | |
| delete_time | datetime | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)mysql> describe box_note;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| note_id | bigint(20) | NO | PRI | 0 | |
| box_id | bigint(20) | NO | PRI | 0 | |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
想法很简单,试了半天都失败:想根据user_id获取该user的所有盒子以及每个盒子最后一条消息的时间。我准备了这两个基本SQL语句,//获取所有user_id用户的盒子
select box.box_id,type,status_type from box,box_user where box.box_id=box_user.box_id and box_user.user_id=1;
//获取所有内部具有消息的user_id用户的盒子以及最后更新时间
select box_id,max(create_time) as time from note,box_note where note.note_id=box_note.note_id and note.user_id=1 group by box_id;它们分别执行结果:
mysql> select box.box_id,type,status_type from box,box_user where box.box_id=box_user.box_id and box_user.user_id=1;
+--------+------+-------------+
| box_id | type | status_type |
+--------+------+-------------+
| 1 | 0 | 0 |
| 6 | 1 | 0 |
| 7 | 3 | 0 |
| 8 | 3 | 0 |
+--------+------+-------------+
4 rows in set (0.00 sec)mysql> select box_id,max(create_time) as time from note,box_note where note.note_id=box_note.note_id and note.user_id=1 group by box_id;
+--------+---------------------+
| box_id | time |
+--------+---------------------+
| 1 | 2012-05-21 00:00:00 |
| 6 | 2012-05-30 00:00:00 |
+--------+---------------------+
2 rows in set (0.00 sec)给这个例子的目的就是说,我要用left join把两个结果集串起来,以第一个结果集为基准,没有对应的最后时间就显示个null或者0000-00-00 00:00:00之类的都可以了。我彻头彻尾的失败了,left join子查询一直报各种错,坐等解答。
SELECT a.* , b.time
FROM (
SELECT 1 box_id, 0
TYPE , 0 status_type
UNION ALL SELECT 6 , 1, 0
UNION ALL SELECT 7 , 3, 0
UNION ALL SELECT 8 , 3, 0
)a
LEFT JOIN (
SELECT 1 box_id, '2012-05-21 00:00:00' time
UNION ALL SELECT 6 , '2012-05-30 00:00:00'
)b ON a.box_id = b.box_id
结果box_id type status_type time
1 0 0 2012-05-21 00:00:00
6 1 0 2012-05-30 00:00:00
7 3 0 NULL
8 3 0 NULL
擦,一点也不简单,这是虐待sql白痴.那些数字0,6什么的完全不了解 -,-
额,那些select是你构造的数据
TYPE , 0 status_type
UNION ALL SELECT 6 , 1, 0
UNION ALL SELECT 7 , 3, 0
UNION ALL SELECT 8 , 3, 0这是构造你的第一个SQL语句执行结果我测试,懒得建表,插入数据【虽然你所贴很详细,但没有直接的SQL语句】后面那个()里的union all类似的,构造出第二个语句结果
from(select box.box_id,type,status_type from box,box_user where box.box_id=box_user.box_id and box_user.user_id=1)a
left join
(select box_id,max(create_time) as time from note,box_note where note.note_id=box_note.note_id and note.user_id=1 group by box_id
)b on a.box_id=b.box_id;额,成功了,我就是有个疑惑,就是这个left join一定要操作两个子查询之后的结果作为一个整体再select一次吗。我之前一直在尝试类似这样的方法,就是第一个查询left join一个子查询,不要细看这个语法只是举个例子,这样没救吗。select box.box_id,type,status_type from box,box_user left join
(select box_id,max(create_time) as time from note,box_note where note.note_id=box_note.note_id and note.user_id=1 group by box_id) as t2
on box.box_id=t2.box_id where box.box_id=box_user.box_id and box_user.user_id=1;
create table.............语句,
inset into table..................语句。还有期望结果及简单描述。这样别人可以搭建与你相同的环境进行测试。