请教一个sql多表查询问题

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

dep表(主编号表)

idmanif_idprefixnumberpostfixleg_deleg_destULD_ID
12015-02-02/Me4001001000CANABC
22022-07-01/CX4001002000XDSVBN
32023-09-19/FC4001003000XDSXCV

dep_basic表(编号基本表)

prefixnumberpostfixroutingCarrierspieceweightgoods_code
4001001000ABC/DKLMF320COP
4001002000EFL/EFNMF120COP
4001003000MCN/DOPMF120COP

dep_h表(分编号表)

hnumbergoods_codeprefixnumberpostfixroutingpieceweight
7841COP4001001000ABC/DKL320

code_info表(编码信息表)

idnumberprefixpostfixhnumbergoods_codeencodeencodeCNencodeENpieceweightpackageCode
110014000007841COP20230406水果ete110rec

表关系如下

img

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

numberhnumbermanif_idroutinggoods_codeencodeencodeCNencodeEN
10012015-02-02/MeABC/DKLCOP
100178412015-02-02/MeABC/DKLCOP20230406水果ete
10022022-07-01/CXEFL/EFNCOP
10032023-09-19/FCMCN/DOPCOP

注: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');

看起来，像是列出基本信息，然后附加实际订单，所以用 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


select
t1.number as number,
t1.hnumber as hnumber,
t1.manif_id as manif_id,
t2.routing as routing,
t2.goods_code as goods_code,
t3.encode as encode,
t3.encodeCN as encodeCN,
t3.encodeEN as encodeEN
from (select 
                d.number,
                h.hnumber,
                d.manif_id
            from dep d
            left join dep_h h on d.number = h.number
            UNION
            select 
                d.number,
                NULL AS hnumber,
                d.manif_id
            from dep d
            ORDER BY number,hnumber) t1
left join dep_basic t2 on t1.number = t2.number
left join code_info t3 on t1.number = t3.number and t1.hnumber = t3.hnumber;


SELECT
    a.* 
FROM
    (
    SELECT
        dep.number AS number,
        NULL AS hnumber,
        dep.manif_id AS manif_id,
        dep_basic.routing AS routing,
        NULL AS goods_code,
        NULL AS encode,
        NULL AS encodeCN,
        NULL AS encodeEN 
    FROM
        dep
        LEFT JOIN dep_h ON dep.number = dep_h.number
        LEFT JOIN dep_basic ON dep_basic.number = dep.number
        -- LEFT JOIN code_info ON dep.number = code_info.number 
    WHERE
        dep.number IN (
        SELECT DISTINCT
            dep_h.number AS number 
        FROM
            dep
            LEFT JOIN dep_h ON dep.number = dep_h.number 
        WHERE
            dep_h.hnumber IS NOT NULL 
        ) 
    ) a
UNION ALL

SELECT
    a.* 
FROM
    (
    SELECT
        dep.number AS number,
        dep_h.hnumber AS hnumber,
        dep.manif_id AS manif_id,
        dep_basic.routing AS routing,
        code_info.goods_code AS goods_code,
        code_info.encode AS encode,
        code_info.encodeCN AS encodeCN,
        code_info.encodeEN AS encodeEN 
    FROM
        dep
        LEFT JOIN dep_h ON dep.number = dep_h.number
        LEFT JOIN dep_basic ON dep_basic.number = dep.number
        LEFT JOIN code_info ON dep.number = code_info.number 
    ) a 



SELECT d.number AS '主编号', d.manif_id AS '主单容器号', db.routing AS '主单航线', db.goods_code AS '主单货物代码',
dh.hnumber AS '分编号', dh.goods_code AS '货物代码', db.prefix AS '编号前缀', db.postfix AS '编号后缀',
db.routing AS '航线', db.Carriers AS '承运人', db.piece AS '件数', db.weight AS '重量',
ci.encode AS '编码', ci.encodeCN AS '编码中文名称', ci.encodeEN AS '编码英文名称'
FROM dep d
LEFT JOIN dep_h dh ON d.number=dh.number AND d.prefix=dh.prefix AND d.postfix=dh.postfix
LEFT JOIN dep_basic db ON d.prefix=db.prefix AND d.number=db.number AND d.postfix=db.postfix AND dh.goods_code=db.goods_code
LEFT JOIN code_info ci ON dh.hnumber=ci.hnumber AND dh.goods_code=ci.goods_code

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
可以使用JOIN语句连接多个表，根据题目需求，可以这样写：

SELECT d.number, dh.hnumber, d.manif_id, db.routing, db.goods_code, ci.encodeCN, ci.encodeEN
FROM dep d
JOIN dep_h dh ON d.id = dh.prefix + dh.number + dh.postfix
JOIN dep_basic db ON d.prefix = db.prefix AND d.number = db.number AND d.postfix = db.postfix
JOIN code_info ci ON dh.hnumber = ci.hnumber AND db.goods_code = ci.goods_code

解释一下，首先通过JOIN将dep表和dep_h表连接起来，通过d.id = dh.prefix + dh.number + dh.postfix这个条件进行关联。然后再将dep表和dep_basic表连接起来，通过d.prefix = db.prefix AND d.number = db.number AND d.postfix = db.postfix这个条件进行关联。最后再将dep_h表和code_info表连接起来，通过dh.hnumber = ci.hnumber AND db.goods_code = ci.goods_code这个条件进行关联。最终就可以得到想要的结果。

代码如下：

SELECT d.number, dh.hnumber, d.manif_id, db.routing, db.goods_code, ci.encodeCN, ci.encodeEN
FROM dep d
JOIN dep_h dh ON d.id = dh.prefix + dh.number + dh.postfix
JOIN dep_basic db ON d.prefix = db.prefix AND d.number = db.number AND d.postfix = db.postfix
JOIN code_info ci ON dh.hnumber = ci.hnumber AND db.goods_code = ci.goods_code;
如果我的回答解决了您的问题，请采纳！

SELECT
dep.number AS '主编号',
dep_h.hnumber AS '分编号',
dep.manif_id AS '主单容器号',
dep_basic.routing AS '主单航线',
dep_basic.goods_code AS '主单货物代码',
code_info.encode AS '编码',
code_info.encodeCN AS '编码中文名称',
code_info.encodeEN AS '编码英文名称'
FROM
dep
LEFT JOIN dep_h ON dep.number = dep_h.number
LEFT JOIN dep_basic ON dep.prefix = dep_basic.prefix
AND dep.number = dep_basic.number
AND dep.postfix = dep_basic.postfix
LEFT JOIN code_info ON dep_h.hnumber = code_info.hnumber
AND dep.prefix = code_info.prefix
AND dep.number = code_info.number
AND dep.postfix = code_info.postfix
WHERE
dep.prefix = '400';

以下内容部分参考ChatGPT模型：

可以使用多表联合查询来实现需求。具体思路如下：

  1. 首先从dep表中查询出主编号、主单容器号、主单航线、主单货物代码，以及分编号。
  2. 然后根据分编号从dep_h表中查询出对应的货物代码、编号前缀、编号后缀、航线、件数和重量。
  3. 再根据主单货物代码从dep_basic表中查询出对应的航线、承运人、件数、重量和货物代码。
  4. 最后根据编码从code_info表中查询出对应的编码、编码中文名称和编码英文名称。

具体的SQL语句如下：

SELECT d.number AS '主编号', d.manif_id AS '主单容器号', db.routing AS '主单航线', db.goods_code AS '主单货物代码',
dh.hnumber AS '分编号', dh.goods_code AS '货物代码', db.prefix AS '编号前缀', db.postfix AS '编号后缀',
db.routing AS '航线', db.Carriers AS '承运人', db.piece AS '件数', db.weight AS '重量',
ci.encode AS '编码', ci.encodeCN AS '编码中文名称', ci.encodeEN AS '编码英文名称'
FROM dep d
LEFT JOIN dep_h dh ON d.number=dh.number AND d.prefix=dh.prefix AND d.postfix=dh.postfix
LEFT JOIN dep_basic db ON d.prefix=db.prefix AND d.number=db.number AND d.postfix=db.postfix AND dh.goods_code=db.goods_code
LEFT JOIN code_info ci ON dh.hnumber=ci.hnumber AND dh.goods_code=ci.goods_code

其中，LEFT JOIN用于保证即使没有匹配的记录，也会返回左表中的记录。

如果我的建议对您有帮助、请点击采纳、祝您生活愉快