SQL：查詢每個類別最新的5條記錄

快乐的总统95發表於2024-09-16
SQL

場景

根據 主表的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釋出於部落格園

相關文章