SQL：查询每个类别最新的5条记录

场景

根据 主表的ID列表，查询 各个ID 的 text_comment 中 最新的5条记录。

table: text_comment

field: text_uuid

ben发布于博客园

v1：使用 union

select * from
(select * from text_comment
where text_uuid = 'cf6fd43a5f0527840fcdbbad55ce44c5'
order by create_time desc
limit 5) a
union
select * from
(select * from text_comment
where text_uuid = '75e84ef20ff5988617834f9fd7183d78'
order by create_time desc
) a
union
select * from
(select * from text_comment
where text_uuid = 'e7bbbcaa6525254e2737336a858502a2'
order by create_time desc
) a

 

v2：窗口函数 row_number() over(...)

MySQL 8 + 才可以 使用 窗口函数，之前的不支持。

select * from
(
select *, row_number() over (
partition by text_uuid
order by create_time desc) as row_num 
from text_comment
where text_uuid in ('cf6fd43a5f0527840fcdbbad55ce44c5',
'75e84ef20ff5988617834f9fd7183d78', 
'e7bbbcaa6525254e2737336a858502a2', 'dads')) as x
where row_num <=5
;

上面，最外层的 select 是必须的。是否可以改进，需要再探索 TODO。ben发布于博客园

 

mybatis 中 实现

在 Mapper 中 使用 org.apache.ibatis.annotations.SelectProvider 注解 定义一个 查询函数。ben发布于博客园

public interface TextCommentMapper extends BaseMapper<TextComment> {

    @SelectProvider(type = DynamicSql1.class, method = "func1")
    List<TextComment> getLastestRecordsByWindowFunc(@Param("uuids") List<String> uuids,
                                                    @Param("maxNum") int maxNum);

    class DynamicSql1 {

        public String func1(@Param("uuids") List<String> uuids, @Param("maxNum") int maxNum) {
            if (CollectionUtils.isEmpty(uuids)) {
                return "selec * from text_comment where 1=0";
            }

            StringBuilder sql = new StringBuilder();
            sql.append("select * from (select *, row_number() over ( partition by text_uuid")
                    .append(" order by create_time desc) as row_num from text_comment")
                    .append(" where text_uuid IN (");
            for (int i = 0; i < uuids.size(); i++) {
                if (i > 0) {
                    sql.append(", ");
                }
                sql.append("#{uuids[").append(i).append("]}");
            }
            sql.append(")) AS tab")
                    .append(" where row_num <= ")
                    .append(maxNum);

            return sql.toString();
        }
    }
}

 

END

 

ben发布于博客园

ben发布于博客园