参考: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',''));