欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

跪求一句SQL语句,坐等。解决办法

程序员文章站 2022-05-23 15:37:07
...
跪求一句SQL语句,坐等。
4个表,

box是盒子,
note是消息,
box_user是哪些用户在哪些盒子里聊天。
box_note是哪些消息在哪些盒子里(消息可在多盒共享)。

盒子就和微信的一个聊天框一样,消息就是里面的消息。

SQL code

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语句,
SQL code

//获取所有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;


它们分别执行结果:
SQL code
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)


 
跪求一句SQL语句,坐等。解决办法

声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。

相关文章

相关视频