create table R(

nam char(20),

create_time time);

insert into R values(1,'M','12:12:12');

insert into R values(2,'C','13:12:12');

insert into R values(3,'C','13:16:19');

insert into R values(4,'D','14:12:12');

insert into R values(5,'D','15:17:17');

insert into R values(6,'F','12:12:12');

insert into R values(7,'M','17:17:17');

insert into R values(8,'G','12:12:12');

insert into R values(9,'M','18:18:18');

需求:筛选出按照 nam 分组的每个 create_time 的最大值

最麻烦的一种思路就是下面的这种方式:

FROM r,(SELECT *

SELECT nam,create_time

ORDER BY nam,create_time DESC) AS a

GROUP BY nam) AS b

WHERE r.nam=b.nam AND r.create_time=b.create_time;

结果发现简直不可思议这是要花费多久的时间,如果记录数超大的时候,呵呵了

下面我们换一种思路实现

这是一个真实的例子:

CREATE TABLE IF NOT EXISTS employee (

empid int(11) DEFAULT NULL,

deptid int(11) DEFAULT NULL,

salary decimal(10,2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO employee (empid,deptid,salary) VALUES

(1, 10, 5500.00),

(2, 10, 4500.00),

(3, 20, 1900.00),

(4, 20, 4800.00),

(5, 40, 6500.00),

(6, 40, 14500.00),

(7, 40, 44500.00),

(8, 50, 6500.00),

(9, 50, 7500.00);

需求是这样的:实现新加一列代表每个部门的工资等级,比如:

+-------+--------+----------+

| empid | deptid | salary  |

+-------+--------+----------+

+-------+--------+----------+

//实现下面结果

empid   deptid  salary   rank

+-------+--------+----------+--------

1       10   5500.00    1

2       10   4500.00    2

4       20   4800.00    1

3       20   1900.00    2

7       40   44500.00    1

6       40   14500.00    2

5       40   6500.00     3

9       50   7500.00     1

8       50   6500.00     2

下面就是实现的方法:

SELECT empid,deptid,salary,rank

FROM (SELECT *,

IF(@pa=ff.deptid,@rank:=@rank+1,@rank:=1) AS rank,@pa:=ff.deptid

FROM (SELECT empid,deptid,salary

FROM employee

ORDER BY deptid ASC,salary DESC)ff) AS result;

通常这个是加 HAVING 子句进行过滤的,比如我取出每个分组的前 4 条记录