mysql 分组内排序问题

之前遇到过一个需求，要从读者借阅记录查询出每人借阅的第一本图书，刚开始想着根据读者分组之后，再按借阅时间排序就可以了。写的查询语句如下，创表的sql语句详见

image.png

SELECT
    `name`,
    borrower_time
FROM
    lab_model_book_borrower_record 
GROUP BY
    `name` 
ORDER BY
    borrower_time;

mysql5.7以上的可能要先关闭 sql_mode=only_full_group_by，否则运行不通过

mysql> show variables like "%sql_mode%"\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.01 sec)

# vim my.cnf
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# 重启 mysql
mysql> show variables like "%sql_mode%"\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

但是发现结果不正确，这是因为order 是对分组的结果统一进行排序的。那咋办呢，要筛选每组最早的那条记录，如果此时恰好满足id 越大时间越大，我们筛选出每组id 最小那条就可以了。即

SELECT
    `name`,
    min( id ) AS id 
FROM
    lab_model_book_borrower_record 
GROUP BY
    `name`;

如果id和时间没有这种对应关系，我是用子查询来解决这个问题的。子查询是将一个查询语句嵌套在另一个查询语句中，在特定情况下，一个查询语句的条件需要另一个查询语句来获取，子查询一般出现在SELECT语句的各个部分。如：SELECT子句、FROM子句、WHERE子句、ORDER BY子句、HAVING子句…… where 部分还能借助exist/in/not in/any(some)/all 来做逻辑判断。

我们在where 语言来判断该条记录是否为这个读者第一次借阅记录，是则被筛选出来。

SELECT
    * 
FROM
    lab_model_book_borrower_record a 
WHERE
    a.borrower_time = (
    SELECT
        min( b.borrower_time ) 
    FROM
        lab_model_book_borrower_record b 
WHERE
    a.`name` = b.`name`)

image.png

如果要筛选出每位读者最早两条借阅记录，也可以通过子查询的方式在where 中判断在该条记录前面条数小于2。

SELECT
    * 
FROM
    lab_model_book_borrower_record a 
WHERE
    ( SELECT count(*) FROM lab_model_book_borrower_record b WHERE a.`name` = b.`name` AND a.borrower_time > b.borrower_time ) < 2 
ORDER BY
    a.borrower_time;

欢迎大家给我留言，提建议，指出错误，一起讨论学习技术的感受！