现有如下两张表，主表和子表
主表：
- HTML code
-
id user_id user_pwd user_email 1 alexis 1111 123@126.com 2 ab 1234 1234@msn.com
子表：
- HTML code
-
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关联，
要求查询每个用户最高的学历信息，即查出来的结果是
+ -- ----+---------+----------+--------------+----------+---------------------+ | 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 | + -- ----+---------+----------+--------------+----------+---------------------+
有如下几种实现方法：
1. 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;
2.
SELECT * FROM `users` a1 LEFT JOIN (
SELECT a.user_id,a.edu_name,a.edu_date FROM edu a
LEFT JOIN edu b ON a.user_id=b.user_id AND a.edu_date>=b.edu_date
GROUP BY a.user_id,a.edu_name,a.edu_date HAVING COUNT(*)=2) b1 ON a1.id=b1.user_id 3.
SELECT users. * , ( SELECT edu_name FROM edu WHERE edu. user_id = users.id ORDER BY edu_date DESC LIMIT 1 ) AS name1 FROM users