只应返回一条记录时返回多条记录的SQL查询

Question

我有一个SQL查询，我正在运行多个连接以返回所需的结果，但是，当添加到我的select语句中时，这个字段要求MX.TFNO AS 'BULK TO SPLIT REFERENCE'我得到不正确的重复数据行，但当我注释掉这一行时，查询工作正常，但没有应该返回到上面提到的列的数据。

下面是我的问题。请注意，我已经为表MPEXOR注释掉了一个连接，因为我还没有在我们的datalake中设置这个表来拉入该数据。

SELECT distinct
MP.SUNO AS 'SUPPLIER',
CI.SUNM AS 'SUPPLIER NAME',
MP.FACI AS 'FACILITY',
MP.WHLO AS 'WAREHOUSE',
MP.OURR AS 'SUPPLIER PO',
CONCAT(MM.BUAR ,'/', MP.PROJ) AS 'PO GROUP',
MP.PUNO AS 'PURCHASE ORDER NUMBER',
    IIF (MP.PUNO LIKE '533%', 'SPLIT',
    IIF (MP.PUNO LIKE '511%', 'BULK', '')) as 'PO TYPE',
--MX.TFNO AS 'BULK TO SPLIT REFERENCE',
MP.PNLI AS 'PO LINE NUMBER',
MP.ITNO AS 'ITEM NUMBER',
MP.PITD AS 'STYLE',
MH.TX15 AS 'SIZE',
MH.TY15 AS 'COLOR',
MM.BUAR AS 'BRAND',
CONVERT(varchar,MP.DWDT, 101) AS 'EX FACTORY ORIGINAL DATE',
CONVERT(varchar,MP.CODT,101) AS 'REVISED EX FACTORY DATE',
    IIF (MP.MODL = 'S', 'OCEAN',
    IIF (MP.MODL = 'C', 'COURIER',
    IIF (MP.MODL = 'G', 'CONSIGNEE OPTION',
    IIF (MP.MODL = 'H', 'CUSTOMER PICK UP',
    IIF (MP.MODL = 'M', 'MOTOR',
    IIF (MP.MODL = 'A', 'AIR', '')))))) as 'DELIVERY METHOD',
MP.ORQA AS 'ORDERED QTY',
MF.UCOS AS 'COST PRICE',
MP.PUPR AS 'PURCHASE PRICE',
MP.PUST AS 'PO LOW STATUS',
MP.PUSL AS 'PO HIGH STATUS',
MP.ORCO AS 'COUNTRY OF ORIGIN'
--MX.HREF AS 'PO REFERENCE'
FROM MPLINE MP
INNER JOIN CIDMAS CI WITH (NOLOCK)
    ON CI.CONO = MP.CONO
    AND CI.SUNO = MP.SUNO
    AND CI.DELETED = 'N'
LEFT JOIN MITFAC MF WITH (NOLOCK)
    ON MF.CONO = MP.CONO
    AND MF.FACI = MP.FACI
    AND MF.ITNO = MP.ITNO
    AND MF.DELETED = 'N'
LEFT JOIN MITMAH MH WITH (NOLOCK)
    ON MH.CONO = MP.CONO
    AND MH.ITNO = MP.ITNO
    AND MH.DELETED = 'N'
LEFT JOIN MITMAS MM WITH (NOLOCK)
    ON MM.CONO = MP.CONO
    AND MM.ITNO = MP.ITNO
    AND MM.DELETED = 'N'
LEFT JOIN MPHEAD MX WITH (NOLOCK)
    ON MX.CONO = MP.CONO
    AND MX.SUNO = MP.SUNO
    AND MX.FACI = MP.FACI
    AND MX.WHLO = MP.WHLO
    AND MX.ORTY = MP.ORTY
    AND MX.DELETED = 'N'
--LEFT JOIN MPEXOR ME
    --ON ME.CONO = MP.CONO
    --AND ME.PUNO = MP.PUNO
WHERE MP.PUST < 50
    AND MP.PUSL < 50
    AND MP.PUNO = '5330000172'
--ORDER BY MP.PUNO, MP.PNLI
;

下面是MX.TFNO就绪后的返回结果

SUPPLIER    SUPPLIER NAME   FACILITY    WAREHOUSE   SUPPLIER PO PO GROUP    PURCHASE ORDER NUMBER   PO TYPE BULK TO SPLIT REFERENCE PO LINE NUMBER  ITEM NUMBER STYLE   SIZE    COLOR   BRAND   EX FACTORY ORIGINAL DATE    REVISED EX FACTORY DATE DELIVERY METHOD ORDERED QTY COST PRICE  PURCHASE PRICE  PO LOW STATUS   PO HIGH STATUS  COUNTRY OF ORIGIN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT       1   P001656-03162XL ALAYA-SF    2XL Juniper SK  20210809    0   OCEAN   3.000000    58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT       2   P001656-0316L   ALAYA-SF    L   Juniper SK  20210809    0   OCEAN   20.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT       3   P001656-0316M   ALAYA-SF    M   Juniper SK  20210809    0   OCEAN   25.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT       4   P001656-0316S   ALAYA-SF    S   Juniper SK  20210809    0   OCEAN   16.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT       5   P001656-0316XL  ALAYA-SF    XL  Juniper SK  20210809    0   OCEAN   12.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT       6   P001656-0316XS  ALAYA-SF    XS  Juniper SK  20210809    0   OCEAN   4.000000    58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29371    1   P001656-03162XL ALAYA-SF    2XL Juniper SK  20210809    0   OCEAN   3.000000    58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29371    2   P001656-0316L   ALAYA-SF    L   Juniper SK  20210809    0   OCEAN   20.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29371    3   P001656-0316M   ALAYA-SF    M   Juniper SK  20210809    0   OCEAN   25.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29371    4   P001656-0316S   ALAYA-SF    S   Juniper SK  20210809    0   OCEAN   16.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29371    5   P001656-0316XL  ALAYA-SF    XL  Juniper SK  20210809    0   OCEAN   12.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29371    6   P001656-0316XS  ALAYA-SF    XS  Juniper SK  20210809    0   OCEAN   4.000000    58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29373    1   P001656-03162XL ALAYA-SF    2XL Juniper SK  20210809    0   OCEAN   3.000000    58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29373    2   P001656-0316L   ALAYA-SF    L   Juniper SK  20210809    0   OCEAN   20.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29373    3   P001656-0316M   ALAYA-SF    M   Juniper SK  20210809    0   OCEAN   25.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29373    4   P001656-0316S   ALAYA-SF    S   Juniper SK  20210809    0   OCEAN   16.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29373    5   P001656-0316XL  ALAYA-SF    XL  Juniper SK  20210809    0   OCEAN   12.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#29373    6   P001656-0316XS  ALAYA-SF    XS  Juniper SK  20210809    0   OCEAN   4.000000    58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#427  1   P001656-03162XL ALAYA-SF    2XL Juniper SK  20210809    0   OCEAN   3.000000    58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#427  2   P001656-0316L   ALAYA-SF    L   Juniper SK  20210809    0   OCEAN   20.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#427  3   P001656-0316M   ALAYA-SF    M   Juniper SK  20210809    0   OCEAN   25.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#427  4   P001656-0316S   ALAYA-SF    S   Juniper SK  20210809    0   OCEAN   16.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#427  5   P001656-0316XL  ALAYA-SF    XL  Juniper SK  20210809    0   OCEAN   12.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   PO#427  6   P001656-0316XS  ALAYA-SF    XS  Juniper SK  20210809    0   OCEAN   4.000000    58.750000   47.500000   20  20  CN

下面是没有使用MX.TFNO的返回结果

SUPPLIER    SUPPLIER NAME   FACILITY    WAREHOUSE   SUPPLIER PO PO GROUP    PURCHASE ORDER NUMBER   PO TYPE PO LINE NUMBER  ITEM NUMBER STYLE   SIZE    COLOR   BRAND   EX FACTORY ORIGINAL DATE    REVISED EX FACTORY DATE DELIVERY METHOD ORDERED QTY COST PRICE  PURCHASE PRICE  PO LOW STATUS   PO HIGH STATUS  COUNTRY OF ORIGIN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   1   P001656-03162XL ALAYA-SF    2XL Juniper SK  20210809    0   OCEAN   3.000000    58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   2   P001656-0316L   ALAYA-SF    L   Juniper SK  20210809    0   OCEAN   20.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   3   P001656-0316M   ALAYA-SF    M   Juniper SK  20210809    0   OCEAN   25.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   4   P001656-0316S   ALAYA-SF    S   Juniper SK  20210809    0   OCEAN   16.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   5   P001656-0316XL  ALAYA-SF    XL  Juniper SK  20210809    0   OCEAN   12.000000   58.750000   47.500000   20  20  CN
5046    ZHEJIANG AMP GLOBE IMP & EXP CO LTD US1 U01     SK/FW21 5330000172  SPLIT   6   P001656-0316XS  ALAYA-SF    XS  Juniper SK  20210809    0   OCEAN   4.000000    58.750000   47.500000   20  20  CN

Answer 1

发现的问题实际上是数据库中的一个问题，我们需要软件开发人员提供修复。当安装补丁并运行查询时，问题得到了解决，并将此报告推送到生产环境中，现在正在使用它。