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 | [email protected] | 大学 | 2010-10-01 00:00:00 | | 2 | ab | 1234 | [email protected] | NULL | NULL | +------+---------+----------+--------------+----------+---------------------+ 2 rows in set (0.03 sec)mysql>
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
我是这样写的 SELECT users.*, (SELECT edu_name FROM edu WHERE edu.user_id=users.id ORDER BY edu_date DESC LIMIT 1) AS name1 FROM users
[征集]分组取最大N条记录方法征集,及散分....
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
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 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','[email protected]');
insert into `users` (`id`, `user_id`, `user_pwd`, `user_email`) values('2','ab','1234','[email protected]');/*
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');
"id" "user_id" "user_pwd" "user_email" "edu_name" "edu_date"
"1" "alexis" "1111" "[email protected]" "大学" "2010-10-01 00:00:00"
"2" "ab" "1234" "[email protected]" \N \N
-> 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 | [email protected] | 大学 | 2010-10-01 00:00:00 |
| 2 | ab | 1234 | [email protected] | NULL | NULL |
+------+---------+----------+--------------+----------+---------------------+
2 rows in set (0.03 sec)mysql>
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
SELECT users.*,
(SELECT edu_name FROM edu WHERE edu.user_id=users.id ORDER BY edu_date DESC LIMIT 1) AS name1
FROM users