mysql - 如何在一个查询中进行分组和排序？

Question

我有两个表，一个是'tb_student'，另一个是'tb_fees'

为“tb_student”创建查询

CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `class` varchar(255) NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
)

为“tb_fees”创建查询

CREATE TABLE `tb_fees` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL,
  `amount` varchar(255) NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
)

在第一个表中我存储学生详细信息，在另一个表中存储费用详细信息

我想从 'tb_student' 中选择学生详细信息，最后从 'tb_fees' 中为 6 类的学生添加费用

所以我试过了

SELECT * 
  FROM tb_student s INNER JOIN
       tb_fees f on 
       s.email =f.email
 WHERE s.class = 6 GROUP BY s.email ORDER BY f.created_on DESC

这将只给出第一个创建的结果如何获取最后创建的值

费用表

insert into `tb_fees`(`id`,`email`,`amount`,`created_on`) values (5,'ram@gmail.com','5000','2013-05-01 14:20:15');
insert into `tb_fees`(`id`,`email`,`amount`,`created_on`) values (6,'Sam@gmail.com','5000','2013-05-02 14:20:23');
insert into `tb_fees`(`id`,`email`,`amount`,`created_on`) values (7,'jak@gmail.com','5000','2013-05-03 14:20:30');
insert into `tb_fees`(`id`,`email`,`amount`,`created_on`) values (8,'Sam@gmail.com','5000','2013-05-29 14:20:35');
insert into `tb_fees`(`id`,`email`,`amount`,`created_on`) values (9,'ram@gmail.com','5000','2013-05-30 14:20:39');
insert into `tb_fees`(`id`,`email`,`amount`,`created_on`) values (10,'jak@gmail.com','5000','2013-05-30 14:36:13');
insert into `tb_fees`(`id`,`email`,`amount`,`created_on`) values (11,'rose@gmail.com','5000','2013-05-30 14:36:15');
insert into `tb_fees`(`id`,`email`,`amount`,`created_on`) values (12,'nim@gmail.com','5000','2013-05-30 14:36:15');

学生表值

insert into `tb_student`(`id`,`name`,`email`,`class`,`created_on`) values (5,'Ram','ram@gmail.com','6','2013-04-30 14:00:56');
insert into `tb_student`(`id`,`name`,`email`,`class`,`created_on`) values (6,'Sam','Sam@gmail.com','6','2013-03-30 14:01:30');
insert into `tb_student`(`id`,`name`,`email`,`class`,`created_on`) values (7,'Nimmy','nim@gmail.com','7','2013-04-30 13:59:59');
insert into `tb_student`(`id`,`name`,`email`,`class`,`created_on`) values (8,'jak','jak@gmail.com','6','2013-03-30 14:07:32');
insert into `tb_student`(`id`,`name`,`email`,`class`,`created_on`) values (9,'rose','rose@gmail.com','5','2013-04-30 14:07:51');

谢谢

Answer 1

要获取最新费用，请执行以下操作:-

SELECT s.* , f.*
FROM tb_student s 
INNER JOIN
    (SELECT email, MAX(created_on) AS created_on
    FROM tb_fees
    GROUP BY email) Sub1
ON s.email = sub1.email
INNER JOIN tb_fees f 
ON s.email = f.email AND Sub1.created_on = f.created_on
WHERE s.class = 6

顺便说一下，您可能需要在电子邮件字段上建立索引(或者更好的是，使用 tb_fees 表上的 tb_student id 字段而不是电子邮件字段并为其建立索引)