mysql 分组查询取某字段前N条数据

参考：https://blog.csdn.net/junzi528/article/details/84404412，https://blog.csdn.net/gz530/article/details/99947417

因业务需要，自己需要一个分组排序后取每组数据前两条数据的操作，并且将取出前两条数据作为另一张表的取值范围

首先完成分组排序取每组前两条数据操作

select  aa.rCode,aa.publish, aa.end, aa.cut  FROM (select * from promain where end >= '20200331') as aa where (
SELECT count(1) FROM (select * from promain where end >= '20200331') as bb WHERE aa.rCode=bb.rCode and bb.end >= aa.end
        )<=2  GROUP BY aa.rCode ORDER BY a.rCode, a.Date DESC

然后取出前两条数据作为领一张表的取值范围

SELECT A.Name, A.Abbr, A.Code, A.rCode, A.yCode,A.Date,A.tcap, B.min, B.max  FROM ma_tcap AS A INNER JOIN (
        select  aa.rCode,MAX(aa.publish) AS max, MIN(aa.publish) AS min FROM (select * from promain_npcut where Date >= '20200331') as aa where (
                SELECT count(1) FROM (select * from promain_npcut where Date >= '20200331') as bb WHERE aa.rCode=bb.rCode and bb.Date >= aa.Date
        )<=2  GROUP BY aa.rCode ORDER BY a.rCode, a.Date DESC) AS B  ON A.rCode = B.rCode WHERE A.trade BETWEEN B.min AND B.max

注意有些SQL版本不支持上述查询方式，会报错:

1055 - Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'project_raw.a.Date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方法是：

select version(),
@@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));