MySQL根据父ID排序类别(mysql sort category according to parent id)

转载

mb5fdb0ff6b2aaf 2021-09-17 09:52:00

这是表结构

id   parent_id   name
1    0           BMW
2    0           Mercedez
3    0           Porsche
4    1           3 Series
5    2           E60
6    1           5 Series
7    3           Cayenne

如何将表格显示为

BMW
3 Series
5 Series
Mercedez
E60
Porsche
Cayenne

上表显示升序ID，后跟与该ID相关联的parent_id，然后转到第二个ID，依此类推.我需要在单个查询中，是否可以这样做?

解决方案

尝试一下:

SELECT
  name,
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END AS Sort
FROM
  cars
ORDER BY
  Sort,
  id

http://sqlfiddle.com/#!2/9b05f/3

编辑:

鉴于此答案一直在增加，我重新考虑了这个问题并发现了一个缺陷.如果由于某种原因，父级的ID高于子级的ID，则排序会混乱.仅当父ID的编号小于所有子ID的编号时，以上查询才有效.

为演示该问题，请想象表看起来像这样:

id   parent_id   name
8    0           BMW
2    0           Mercedez
3    0           Porsche
4    8           3 Series
5    2           E60
6    8           5 Series
7    3           Cayenne

现在请注意，BMW的id为 8 ，而不是 1 .结果将如下所示:

Mercedez
E60
Porsche
Cayenne
3 Series
5 Series
BMW

请注意， BMW 显示在列表的底部，之后其子项！这是因为id的二级排序顺序，并且如果父ID碰巧高于任何子代，则父代可能不会出现在子代之上.

此查询将解决该问题:

SELECT
  name
FROM
  cars
ORDER BY
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END, -- another way of writing it: ISNULL(NULLIF(parent_id, 0), id)
  parent_id,
  id

http://sqlfiddle.com/#!2/6d6d73/3

要解释这里发生的情况，请先按父行的id字段和子行的parent_id字段排序.如果按此顺序进行排序，则所有子项都将与其父项归为一组，并且整个列表将由父项的id字段进行排序.

但是，这并没有设置家庭内部的排序，因此父母可以出现在家庭内部的任何位置(父母可以出现在顶部，或者可以出现在中间，或者可以是最后).

这是其他两个排序字段的输入位置.第二个字段按parent_id排序，父行的parent_id字段始终为0.安全地假设您的ID字段始终为正，这意味着父记录将始终显示在家族的顶部.其余子项的parent_id值均相同，因此第三个排序字段通过id字段对家庭中的子项进行排序.也可以将其更改为name，具体取决于您希望孩子如何排序.

原文

This is the table structure

id   parent_id   name
1    0           BMW
2    0           Mercedez
3    0           Porsche
4    1           3 Series
5    2           E60
6    1           5 Series
7    3           Cayenne

How can i show the table as

BMW
3 Series
5 Series
Mercedez
E60
Porsche
Cayenne

The above table shows ascending id followed by parent_id associated with that id, then go to second id and so on. I need in a single query, is it possible to do as such?

解决方案

Try this:

SELECT
  name,
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END AS Sort
FROM
  cars
ORDER BY
  Sort,
  id

http://sqlfiddle.com/#!2/9b05f/3

EDIT:

Given that this answer keeps getting upvotes, I revisited the question and found a flaw. If, for some reason, the parent has a higher ID than the child, the ordering gets messed up. The above query only works if the parent ID is a lower number than all the children.

To demonstrate the problem, imagine the table looked like this:

id   parent_id   name
8    0           BMW
2    0           Mercedez
3    0           Porsche
4    8           3 Series
5    2           E60
6    8           5 Series
7    3           Cayenne

Notice now that BMW has an id of 8 instead of 1. The result will look like this:

Mercedez
E60
Porsche
Cayenne
3 Series
5 Series
BMW

Notice above that BMW shows up at the bottom of the list, after its children! This is because the secondary sorting orders by id, and if the parent ID happens to be higher than any children, the parent may not show up on top of the children.

This query will solve that problem:

SELECT
  name
FROM
  cars
ORDER BY
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END, -- another way of writing it: ISNULL(NULLIF(parent_id, 0), id)
  parent_id,
  id

SELECT
duty_man,
CASE WHEN parent_id = 0 THEN id ELSE parent_id END AS Sort
FROM
bzyd_month_plan
ORDER BY
Sort,
id

DROP TABLE IF EXISTS `bzyd_month_plan`;
CREATE TABLE `bzyd_month_plan` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`member_room` varchar(128) DEFAULT '' COMMENT '车间',
`member_line` varchar(128) DEFAULT '' COMMENT '产品线',
`member_team` varchar(128) DEFAULT '' COMMENT '班组',
`big_module` varchar(128) DEFAULT '' COMMENT '模块',
`small_module` varchar(128) DEFAULT '' COMMENT '小板块',
`year` int(11) DEFAULT NULL COMMENT '年度',
`work_content` varchar(128) DEFAULT '' COMMENT '工作内容',
`deliverable` varchar(128) DEFAULT '' COMMENT '交付物',
`duty_man` varchar(128) DEFAULT '' COMMENT '责任人',
`member_sap` varchar(128) DEFAULT '' COMMENT 'SAP编号',
`start_time` date DEFAULT NULL COMMENT '开始时间',
`end_time` date DEFAULT NULL COMMENT '计划完成时间',
`real_time` date DEFAULT NULL COMMENT '实际完成时间',
`done_status` varchar(128) DEFAULT '' COMMENT '完成情况',
`def_analyze` varchar(128) DEFAULT '' COMMENT '差异分析',
`count_plan` varchar(128) DEFAULT '' COMMENT '对策',
`plan_remark` varchar(128) DEFAULT '' COMMENT '备注',
`train_type` varchar(128) DEFAULT '' COMMENT '培训方式',
`train_teacher` varchar(128) DEFAULT '' COMMENT '培训老师',
`train_man` varchar(128) DEFAULT '' COMMENT '培训对象',
`train_course` varchar(128) DEFAULT '' COMMENT '培训课时',
`create_user` varchar(50) CHARACTER SET armscii8 DEFAULT NULL COMMENT '创建人',
`create_time` date DEFAULT NULL COMMENT '创建时间',
`update_user` varchar(50) DEFAULT NULL COMMENT '更新人',
`update_time` date DEFAULT NULL COMMENT '更新时间',
`is_delete` int(11) DEFAULT '0' COMMENT '删除标志',
`parent_id` int(11) NOT NULL DEFAULT '0' COMMENT '父ID',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1438416536216629504 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='年度计划表';

-- ----------------------------
-- Records of bzyd_month_plan
-- ----------------------------
INSERT INTO `bzyd_month_plan` VALUES ('1', '三发车间', '试机线', '试机班组', '培训计划', '全线通', '2021', '工位培训', '上岗证', '李三1', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '0');
INSERT INTO `bzyd_month_plan` VALUES ('2', '二发车间', '试机线', '试机班组', '培训计划', '全线通', '2022', '工位培训', '上岗证', '李三2', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '0');
INSERT INTO `bzyd_month_plan` VALUES ('3', '二发车间', '试机线', '试机班组', '培训计划', '全线通', '2021', '工位培训', '上岗证', '李三3', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '0');
INSERT INTO `bzyd_month_plan` VALUES ('4', '三发车间', '试机线', '试机班组', '培训计划', '全线通', '2021', '工位培训', '上岗证', '李三-1', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '1');
INSERT INTO `bzyd_month_plan` VALUES ('5', '二发车间', '试机线', '试机班组', '培训计划', '全线通', '2022', '工位培训', '上岗证', '李三-2', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '2');
INSERT INTO `bzyd_month_plan` VALUES ('6', '二发车间', '试机线', '试机班组', '培训计划', '全线通', '2021', '工位培训', '上岗证', '李三-1', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '1');
INSERT INTO `bzyd_month_plan` VALUES ('7', '二发车间', '试机线', '试机班组', '培训计划', '全线通', '2021', '工位培训', '上岗证', '李三-2', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '2');
INSERT INTO `bzyd_month_plan` VALUES ('8', '二发车间', '试机线', '试机班组', '培训计划1', '全线通', '2021', '工位培训', '上岗证', '李三-2', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '2');
INSERT INTO `bzyd_month_plan` VALUES ('9', '二发车间', '试机线', '试机班组', '培训计划', '全线通', '2021', '工位培训', '上岗证', '李三-3', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '3');
INSERT INTO `bzyd_month_plan` VALUES ('10', '二发车间', '试机线', '试机班组', '培训计划', '全线通', '2021', '工位培训', '上岗证', '李三-3', '10050333', '2021-09-07', '2021-09-08', '2021-09-13', '良好', '培训不及时', '', '', '面授', '张五', '王一', '3', null, null, null, null, '0', '3');