Beg for an SQL statement and wait. Solution

Beg for an SQL statement and wait. Four tables, box is box, note is message, and box_user is used to chat in which boxes. Box_note refers to the boxes in which messages are stored (messages can be shared in multiple boxes ). The box is the same as a chat box. a message is the message in it. SQLcodemysql & gt; describebox; + ------------------------- + --- submit an SQL statement and wait.
Four tables,

Box is a box,
Note is a message,
Box_user is the user in which boxes to chat.
Box_note refers to the boxes in which messages are stored (messages can be shared in multiple boxes ).

The box is the same as a chat box. a message is the message in it.

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)

The idea is simple. I tried it for half a day and failed to get all the boxes of the user and the time of the last message of each box according to user_id.

I have prepared these two basic SQL statements,

SQL code

  // Obtain the select box of all user_id users. box_id, type, status_type from box, box_user where box. box_id = box_user.box_id and box_user.user_id = 1; // Obtain the boxes of all user_id users with internal messages and the last update time 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;

The execution results are as follows:

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)