56,681
社区成员
发帖
与我相关
我的任务
分享
id user_id user_pwd user_email
1 alexis 1111 123@126.com
2 ab 1234 1234@msn.com
edu_id user_id edu_name edu_date
1 1 大学 2010-10-01 00:00:00
2 1 高中 2006-09-01 00:00:00
id user_id user_pwd user_email edu_name edu_date
1 alexis 1111 123@126.com 大学 2010-10-01 00:00:00
2 ab 1234 1234@msn.com \N \N
SELECT users.*,
(SELECT edu_name FROM edu WHERE edu.user_id=users.id ORDER BY edu_date DESC LIMIT 1) AS name1
FROM users
mysql> select *
-> from (
-> select a.id,a.user_id,a.user_pwd,a.user_email,b.edu_name,b.edu_date
-> from users a left join edu b on a.id=b.user_id
-> order by a.id,b.edu_date desc
-> ) t
-> group by id;
+------+---------+----------+--------------+----------+---------------------+
| id | user_id | user_pwd | user_email | edu_name | edu_date |
+------+---------+----------+--------------+----------+---------------------+
| 1 | alexis | 1111 | 123@126.com | 大学 | 2010-10-01 00:00:00 |
| 2 | ab | 1234 | 1234@msn.com | NULL | NULL |
+------+---------+----------+--------------+----------+---------------------+
2 rows in set (0.03 sec)
mysql>
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.1.45-community
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `users` (
`id` double ,
`user_id` varchar (60),
`user_pwd` varchar (60),
`user_email` varchar (60)
);
insert into `users` (`id`, `user_id`, `user_pwd`, `user_email`) values('1','alexis','1111','123@126.com');
insert into `users` (`id`, `user_id`, `user_pwd`, `user_email`) values('2','ab','1234','1234@msn.com');
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.1.45-community
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `edu` (
`edu_id` double ,
`user_id` double ,
`edu_name` varchar (150),
`edu_date` datetime
);
insert into `edu` (`edu_id`, `user_id`, `edu_name`, `edu_date`) values('1','1','大学','2010-10-01 00:00:00');
insert into `edu` (`edu_id`, `user_id`, `edu_name`, `edu_date`) values('2','1','高中','2006-09-01 00:00:00');
select * from(
select a.*,b.*
from 主表 a inner join 子表 b on a.id=b.user_id
order by a.id,b.edu_id
) t
group by a.id