请教一个sql多表查询问题

Question

请教一个sql连表查询问题
以下是表数据(表字段sql在下面)

dep表(主编号表)

id	manif_id	prefix	number	postfix	leg_de	leg_dest	ULD_ID
1	2015-02-02/Me	400	1001	000	CAN	ABC
2	2022-07-01/CX	400	1002	000	XDS	VBN
3	2023-09-19/FC	400	1003	000	XDS	XCV

dep_basic表(编号基本表)

prefix	number	postfix	routing	Carriers	piece	weight	goods_code
400	1001	000	ABC/DKL	MF	3	20	COP
400	1002	000	EFL/EFN	MF	1	20	COP
400	1003	000	MCN/DOP	MF	1	20	COP

dep_h表(分编号表)

hnumber	goods_code	prefix	number	postfix	routing	piece	weight
7841	COP	400	1001	000	ABC/DKL	3	20

code_info表(编码信息表)

id	number	prefix	postfix	hnumber	goods_code	encode	encodeCN	encodeEN	piece	weight	packageCode
1	1001	400	000	7841	COP	20230406	水果	ete	1	10	rec

表关系如下

需要查出来的结果显示如下

number	hnumber	manif_id	routing	goods_code	encode	encodeCN	encodeEN
1001		2015-02-02/Me	ABC/DKL	COP
1001	7841	2015-02-02/Me	ABC/DKL	COP	20230406	水果	ete
1002		2022-07-01/CX	EFL/EFN	COP
1003		2023-09-19/FC	MCN/DOP	COP

注:number manif_id 取dep表 hnumber 取dep_h表 routing goods_code 取dep_basic表 encode encodeCN encodeEN 取codei_info表

以下是表字段sql

CREATE TABLE `dep`  (
  `id` int(10) NOT NULL COMMENT 'id',
  `manif_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '容器号',
  `prefix` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号前缀',
  `number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主编号',
  `postfix` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号后缀',
  `leg_de` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '始发站',
  `leg_dest` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '终点站',
  `ULD_ID` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '板箱号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '主编号表' ROW_FORMAT = Dynamic;

INSERT INTO `dep`(`id`, `manif_id`, `prefix`, `number`, `postfix`, `leg_de`, `leg_dest`, `ULD_ID`) VALUES (1, '2015-02-02/Me', '400', '1001', '000', 'CAN', 'ABC', NULL);
INSERT INTO `dep`(`id`, `manif_id`, `prefix`, `number`, `postfix`, `leg_de`, `leg_dest`, `ULD_ID`) VALUES (2, '2022-07-01/CX', '400', '1002', '000', 'XDS', 'VBN', NULL);
INSERT INTO `dep`(`id`, `manif_id`, `prefix`, `number`, `postfix`, `leg_de`, `leg_dest`, `ULD_ID`) VALUES (3, '2023-09-19/FC', '400', '1003', '000', 'XDS', 'XCV', NULL);

CREATE TABLE `dep_basic`  (
  `prefix` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号前缀',
  `number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主编号',
  `postfix` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号后缀',
  `routing` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '航线',
  `Carriers` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '承运人',
  `piece` smallint(11) NULL DEFAULT NULL COMMENT '件数',
  `weight` decimal(10, 2) NULL DEFAULT NULL COMMENT '重量',
  `goods_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '货物代码',
  PRIMARY KEY (`prefix`, `number`, `postfix`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '编号基本表' ROW_FORMAT = Dynamic;

INSERT INTO `dep_basic`(`prefix`, `number`, `postfix`, `routing`, `Carriers`, `piece`, `weight`, `goods_code`) VALUES ('400', '1001', '000', 'ABC/DKL', 'MF', 3, 20.00, 'COP');
INSERT INTO `dep_basic`(`prefix`, `number`, `postfix`, `routing`, `Carriers`, `piece`, `weight`, `goods_code`) VALUES ('400', '1002', '000', 'EFL/EFN', 'MF', 1, 20.00, 'COP');
INSERT INTO `dep_basic`(`prefix`, `number`, `postfix`, `routing`, `Carriers`, `piece`, `weight`, `goods_code`) VALUES ('400', '1003', '000', 'MCN/DOP', 'MF', 1, 20.00, 'COP');

CREATE TABLE `dep_h`  (
  `hnumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分编号',
  `goods_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '货物代码',
  `prefix` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号前缀',
  `number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主编号',
  `postfix` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号后缀',
  `routing` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '航线',
  `piece` smallint(5) NULL DEFAULT NULL COMMENT '件数',
  `weight` decimal(10, 2) NULL DEFAULT NULL COMMENT '重量',
  PRIMARY KEY (`hnumber`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '分编号表' ROW_FORMAT = Dynamic;

INSERT INTO `dep_h`(`hnumber`, `goods_code`, `prefix`, `number`, `postfix`, `routing`, `piece`, `weight`) VALUES ('7841', 'COP', '400', '1001', '000', 'ABC/DKL', 3, 20.00);

CREATE TABLE `code_info`  (
  `id` int(10) NOT NULL COMMENT 'id',
  `prefix` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号前缀',
  `number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主编号',
  `postfix` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号后缀',
  `hnumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '分编号',
  `goods_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '货物代码',
  `encode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码',
  `encodeCN` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码中文名称',
  `encodeEN` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码英文名称',
  `piece` smallint(10) NULL DEFAULT NULL COMMENT '件数',
  `weight` decimal(10, 2) NULL DEFAULT NULL COMMENT '重量',
  `packageCode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '包装代码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '编码信息表' ROW_FORMAT = Dynamic;

INSERT INTO `project`.`code_info`(`id`, `prefix`, `number`, `postfix`, `hnumber`, `goods_code`, `encode`, `encodeCN`, `encodeEN`, `piece`, `weight`, `packageCode`) VALUES (1, '400', '1001', '000', '7841', 'COP', '20230406', '水果', 'ete', 1, 10.00, 'rec');

Answer 1

看起来，像是列出基本信息，然后附加实际订单，所以用 union

select * from (
    select a.number 主编号,'' 分编号,manif_id 主单容器号,routing 主单航线,goods_code 主单货物代码,'' 编码,'' 编码中文名称,'' 编码英文名称 
    from dep a
    inner join dep_basic b on a.number=b.number
    union all
    select c.number,hnumber,manif_id,routing,c.goods_code,encode,encodeCN,encodeEN 
    from code_info c
    inner join dep_basic b on c.number=b.number
    inner join dep a on b.number=a.number
) a
order by 1,2