如何使用LIMIT获取price_trend表中m_id为3、7的各前2条记录

我有一张price_trend表，其中m_id和c_id为外键。需要获取该表中c_id=1且m_id分别为3、7的各前2条记录。

表结构及数据 #

初始查询语句 #

我写了如下查询，但不知道怎么调整才能得到想要的结果：

select * from price_trend where c_id=1 and m_id in(3,7) limit 4;

期望结果 #

解决方案 #

方法1：窗口函数（适用于MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

用ROW_NUMBER()按m_id分组，给每组内记录按日期倒序编号，筛选编号≤2的记录：

SELECT id, c_id, m_id, date
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY m_id ORDER BY date DESC) AS rn
    FROM price_trend
    WHERE c_id=1 AND m_id IN (3,7)
) t
WHERE rn <= 2
ORDER BY m_id, date DESC;

方法2：子查询兼容低版本数据库 #

如果不支持窗口函数，用子查询统计同组内的记录排序：

SELECT p1.*
FROM price_trend p1
WHERE c_id=1 AND m_id IN (3,7)
AND (
    SELECT COUNT(*)
    FROM price_trend p2
    WHERE p2.c_id = p1.c_id
      AND p2.m_id = p1.m_id
      AND p2.date >= p1.date
) <= 2
ORDER BY p1.m_id, p1.date DESC;

方法3：UNION ALL拼接结果 #

分别查询两个m_id的前2条记录，再合并结果：

SELECT * FROM price_trend WHERE c_id=1 AND m_id=3 ORDER BY date DESC LIMIT 2
UNION ALL
SELECT * FROM price_trend WHERE c_id=1 AND m_id=7 ORDER BY date DESC LIMIT 2
ORDER BY m_id, date DESC;

内容的提问来源于stack exchange，提问作者Kiran Ranvirkar