试试这个: select t.*,tt.handle_content,tt.handle_time from 问题表 t left join ( select problem_id,handle_content,time as handle_time from (select * from 问题处理表 order by time desc) tt group by problem_id )tt on t.id = tt.problem_id;
create table 问题表(id int,content varchar(20),time date);insert into 问题表 values(1,'手机发热','2015-08-01'),(2,'屏幕坏点','2015-09-01'),(3,'放小视频卡','2015-10-01');create table 问题处理表(id int,problem_id int,handle_content varchar(30),time date);insert into 问题处理表 values(1,1,'电池问题导致','2015-08-02'), (2,1,'优化电池充电技术','2015-08-03'), (3,2,'屏幕出厂未检测出来','2015-09-02'), (4,2,'关系供应商','2015-09-03'), (5,3,'网速问题','2015-10-02'), (6,3,'播放器兼容性不好','2015-10-03');mysql> select t.*,tt.handle_content,tt.handle_time from 问题表 t left join (select problem_id,handle_content,time as handle_time from (select * from 问题处理表 order by time desc) tt group by problem_id)tt on t.id = tt.problem_id; +------+------------+------------+------------------+-------------+ | id | content | time | handle_content | handle_time | +------+------------+------------+------------------+-------------+ | 1 | 手机发热 | 2015-08-01 | 优化电池充电技术 | 2015-08-03 | | 2 | 屏幕坏点 | 2015-09-01 | 关系供应商 | 2015-09-03 | | 3 | 放小视频卡 | 2015-10-01 | 播放器兼容性不好 | 2015-10-03 | +------+------------+------------+------------------+-------------+ 3 rows in set (0.01 sec)mysql>
试试这个:
select t.*,tt.handle_content,tt.handle_time
from 问题表 t
left join
(
select problem_id,handle_content,time as handle_time
from (select * from 问题处理表 order by time desc) tt
group by problem_id
)tt
on t.id = tt.problem_id;
create table 问题表(id int,content varchar(20),time date);insert into 问题表 values(1,'手机发热','2015-08-01'),(2,'屏幕坏点','2015-09-01'),(3,'放小视频卡','2015-10-01');create table 问题处理表(id int,problem_id int,handle_content varchar(30),time date);insert into 问题处理表
values(1,1,'电池问题导致','2015-08-02'),
(2,1,'优化电池充电技术','2015-08-03'),
(3,2,'屏幕出厂未检测出来','2015-09-02'),
(4,2,'关系供应商','2015-09-03'),
(5,3,'网速问题','2015-10-02'),
(6,3,'播放器兼容性不好','2015-10-03');mysql> select t.*,tt.handle_content,tt.handle_time from 问题表 t left join (select problem_id,handle_content,time as handle_time from (select * from 问题处理表 order by time desc) tt group by problem_id)tt on t.id = tt.problem_id;
+------+------------+------------+------------------+-------------+
| id | content | time | handle_content | handle_time |
+------+------------+------------+------------------+-------------+
| 1 | 手机发热 | 2015-08-01 | 优化电池充电技术 | 2015-08-03 |
| 2 | 屏幕坏点 | 2015-09-01 | 关系供应商 | 2015-09-03 |
| 3 | 放小视频卡 | 2015-10-01 | 播放器兼容性不好 | 2015-10-03 |
+------+------------+------------+------------------+-------------+
3 rows in set (0.01 sec)mysql>