在这篇文章:
laravel 中用group by和order by排序不正确的问题解决
中,提出了如下知识点:
mysql中order by是在group by之后执行的,而group by则是在同一组数据里挑选出id最小的数据记录作为汇总记录
以前的经验,是先在order by的子查询末尾加上:
limit 1000
结果今天发现它不生效。
相关表sql:
CREATE TABLE `tms_doc_manage` ( `doc_manage_id` INT(11) NOT NULL AUTO_INCREMENT, `doc_main_id` INT(11) NOT NULL COMMENT 'doc主表id', `section_id` INT(11) NOT NULL DEFAULT '1' COMMENT 'section id', `modify_times` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '文件修改次数', `status` TINYINT(2) NOT NULL DEFAULT '1' COMMENT '0-delete 1-inactive 2-new 3-submitted 4-processing 5-approve 6-reject', `creator_id` INT(11) NULL DEFAULT NULL, `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updater_id` INT(11) NULL DEFAULT NULL, `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`doc_manage_id`) ) COMMENT='文档管理表' COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=0 ;
示例数据:
INSERT INTO `tms_doc_manage` (`doc_manage_id`, `doc_main_id`, `section_id`, `modify_times`, `status`, `creator_id`, `create_time`, `updater_id`, `update_time`) VALUES (37, 4, 1, 1, 2, 40011, '2022-04-20 19:14:34', 178, '2022-04-22 11:45:15'); INSERT INTO `tms_doc_manage` (`doc_manage_id`, `doc_main_id`, `section_id`, `modify_times`, `status`, `creator_id`, `create_time`, `updater_id`, `update_time`) VALUES (28, 4, 1, 0, 5, 40011, '2022-04-20 19:14:34', 178, '2022-04-22 10:35:38'); INSERT INTO `tms_doc_manage` (`doc_manage_id`, `doc_main_id`, `section_id`, `modify_times`, `status`, `creator_id`, `create_time`, `updater_id`, `update_time`) VALUES (19, 3, 1, 0, 3, 40011, '2022-04-20 15:18:45', 40011, '2022-04-20 15:20:16'); INSERT INTO `tms_doc_manage` (`doc_manage_id`, `doc_main_id`, `section_id`, `modify_times`, `status`, `creator_id`, `create_time`, `updater_id`, `update_time`) VALUES (10, 2, 1, 0, 2, 40011, '2022-04-19 15:26:57', 40011, '2022-04-19 15:26:57'); INSERT INTO `tms_doc_manage` (`doc_manage_id`, `doc_main_id`, `section_id`, `modify_times`, `status`, `creator_id`, `create_time`, `updater_id`, `update_time`) VALUES (1, 1, 1, 0, 3, 40011, '2022-04-19 15:26:08', 40011, '2022-04-19 15:26:08');
现在发现如下,实现抓取doc_main_id每组下的最新记录(即每组里doc_manage_id最大值,跟上面默认的最小的刚好相反)的sql语句,结果并不能按照希望的那样展示:
SELECT * FROM (SELECT * FROM tms_doc_manage dm ORDER BY dm.doc_manage_id DESC LIMIT 1000) AS c WHERE c.section_id=1 GROUP BY c.doc_main_id
参考doc_main_id=4的数据,结果显示的是:
"28" "4" "1" "0" "5" "40011" "2022-04-20 19:14:34" "178" "2022-04-22 10:35:38"
经过尝试,发现去掉where条件就可以了。把需要筛选的where条件加到子查询里
加到子查询,变成如下,就可以了:
SELECT * FROM (SELECT * FROM tms_doc_manage dm where dm.section_id=1 ORDER BY dm.doc_manage_id DESC LIMIT 1000) AS c GROUP BY c.doc_main_id
至于是为何?俺也是一头雾水。
本文是翟码农个人博客里的mysql专题下的相关文章,转载请注明出处:http://www.zhai14.com/blog/how-to-get-the-first-row-of-grouped-data-after-sorting-by-field.html
可是,到了下面这种情形,外面的where条件有没有,却都不影响结果。
从每组里按照指定的状态顺序里抽取第一条数据:
SELECT * FROM (SELECT * FROM tms_doc_manage dm WHERE dm.section_id =1 ORDER BY FIELD(dm.`status`, 2,5,1,4,3) LIMIT 10000) AS c GROUP BY c.doc_main_id
实现从每组里,再将数据按照指定的顺序要求进行排序,然后才抽取第一笔数据。只需要用到:
ORDER BY FIELD(column_name, val1,val2,val3...)
为何上面外查询有没有where条件,结果不一致。而下面外查询有没有where条件却都一样?大家有知道的,欢迎指教一二。