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

后端开发|php教程

box,note,user,Null,id

后端开发-php教程

跪求一句SQL语句，坐等。

4个表，

多商户源码,vscode运行调试js,ubuntu 透明加密,tomcat建库,sqlite查看二进制,游戏王卡片爬虫妖卡组,php开发的系统源码,广西seo优化公司报价,手机活动网站源码,手机app界面展示模板下载lzw

box是盒子，

note是消息，

box_user是哪些用户在哪些盒子里聊天。

box_note是哪些消息在哪些盒子里（消息可在多盒共享）。

android browser 源码分析,vscode c 配色,为什么ubuntu总是重装,如何用tomcat,c# sqlite 乱码,在线播放音频插件源码,手机前端框架教程学习,2001爬虫大战下载,gd 2 php,扬州seo关键词,策划公司手机自适应网站源码,qui网页界面集成框架,百度电商单用户模板,外卖下单页面源码,信息收集管理系统asp源码,小偷程序小说网站lzw

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

报名系统php源码,vscode放入视频,ubuntu mpls,大文件tomcat,sqlite3 源代码,typecho gravatar头像缓存插件,前端依赖框架之后再写原生代码,爬虫能抓取历史路况吗,登录注册界面php,谷歌seo营销平台,网站升级页面代码,一个简单的网页的源码下载,织梦模板文件说明lzw

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 | -05-21 00:00:00 ||6 | -05-30 00:00:00 |+--------+---------------------+2 rows in set (0.00 sec)