字段不是通过创建多个记录而不是单个记录来分组的。

Question

我被困住了，已经看了太久了。下面的查询是，我想返回一个记录，显示销售价格、成本价格和随后的费用，但是当我在我的案例中添加时，这些记录会创建多个记录。寻求任何帮助。

电流输出

​

预期产出

​

当前查询

SELECT DISTINCT
CONCAT(OBCUNO,'',OKCUNM) CUSTOMER,
OBORNO CO_NUMBER,
OBPONR CO_LINE,
OBPROJ PROJECT,
OBITNO ITEM,
OBHDPR M3_STYLE,
MMITDS CUST_STYLE,
MMFUDS DESCRIPTION,
OBORQA ORDER_QTY,
M9UCOS FACILITY_COST,
OBUCOS DISTRO_COST,

CASE WHEN OBUCOS = M9UCOS THEN 'MATCH' ELSE 'NO MATCH' END AS COST_PRC_MATCH,

OBSAPR SALE_PRC,

CASE WHEN O7CRID = 'PNP' THEN O7CRFA * OBORQA ELSE 0 END AS 'PNP CHG', 

CASE WHEN O7CRID = 'DTY' THEN O7CRAM * OBORQA ELSE 0 END AS 'DUTY CHG', 

OBVTCD VAT_CD,
SUM(CASE WHEN O7CRID = 'PNP' THEN OBSAPR * OBORQA + (CASE WHEN O7CRID = 'PNP' THEN OBORQA * O7CRFA ELSE 0 END) ELSE 0 END) AS TOTAL_LN_AMT
FROM MVXJDTA.OOLINE
    LEFT JOIN MVXJDTA.OOLICH 
        ON O7CONO = OBCONO
        AND O7ORNO = OBORNO
        AND O7PONR = OBPONR
    LEFT JOIN MVXJDTA.OCUSMA 
        ON OKCONO = OBCONO
        AND OKCUNO = OBCUNO
    LEFT JOIN MVXJDTA.MITMAS
        ON MMCONO = OBCONO
        AND MMITNO = OBITNO
    LEFT JOIN MVXJDTA.MITFAC
        ON M9CONO = OBCONO
        AND M9FACI = OBFACI
        AND M9ITNO = OBITNO
    LEFT JOIN MVXJDTA.CVATPC
        ON CVCONO = OBCONO
        AND CVDIVI = OBDIVI
        AND CVVTCD = OBVTCD
WHERE OBCONO = '610'
        AND OBORNO = '2000000190'
                
GROUP BY OBCUNO,OKCUNM,OBORNO,OBCUOR,OBPROJ,OBITNO,OBHDPR,MMITDS,MMFUDS,OBSAPR,M9UCOS,OBUCOS,OBVTCD,OBORQA,CVVTP1,O7CRFA,O7CRAM, O7CRID,OBPONR

Answer 1

CASE语句正在生成多个值，因此您将在结果中得到多个行。例如，pnp_chg的值为0和168.4。

可能您需要在每个案例语句中加上SUM --假设这就是您想要处理这种情况的方式。