MYSQL语法帮助

Question

有没有人能为我指出这个问题的正确方向？无论我用哪种方法尝试，我都会继续得到错误

SELECT b.SOLD_TO
     , b.CUSTOMER
     , b.JANUARY_BUDGET
     , Round(sum(d.TOTAL_WEIGHT)/1000,2) AS MONTHLY_DESPATCH
     , if(sum(d.TOTAL_WEIGHT)/1000=0 AND b.JANUARY_BUDGET=0,"DOUBLE_ZERO",
if(sum(d.TOTAL_WEIGHT)/1000>0 AND b.JANUARY_BUDGET=0,"NO_BUDGET_GOOD_SHIPMENT",
if(sum(d.TOTAL_WEIGHT)/1000>0 AND b.JANUARY_BUDGET>0 AND (sum(d.TOTAL_WEIGHT)/1000> b.JANUARY_BUDGET,"ABOVE_BUDGET",
if(sum(d.TOTAL_WEIGHT)/1000>0 AND b.JANUARY_BUDGET>0 AND (sum(d.TOTAL_WEIGHT)/1000< b.JANUARY_BUDGET,"POOR_RESULT")))) AS PERFORMANCE
FROM budget2021 b
left JOIN despatches2021 d on d.SOLD_TO = b.SOLD_TO
WHERE b.SOLD_TO=69946 AND d.INVOICE_DATE BETWEEN 20210101 and 20210131
GROUP BY b.SOLD_TO
     , b.CUSTOMER
     , b.JANUARY_BUDGET

Answer 1

嗯，很难解释，你把wron做了什么

IF( condition，true，false)是一种与嵌套数量无关的形式。

上帝的代码缩进有助于处理这样的事情。

我还必须在最后一个IF的末尾添加一个NULL，因为我不知道你想要做什么

SELECT 
    b.SOLD_TO,
    b.CUSTOMER,
    b.JANUARY_BUDGET,
    ROUND(SUM(d.TOTAL_WEIGHT) / 1000, 2) AS MONTHLY_DESPATCH,
    IF(SUM(d.TOTAL_WEIGHT) / 1000 = 0
            AND b.JANUARY_BUDGET = 0,
        'DOUBLE_ZERO',
        IF(SUM(d.TOTAL_WEIGHT) / 1000 > 0
                AND b.JANUARY_BUDGET = 0,
            'NO_BUDGET_GOOD_SHIPMENT',
            IF(SUM(d.TOTAL_WEIGHT) / 1000 > 0
                    AND b.JANUARY_BUDGET > 0
                    AND (SUM(d.TOTAL_WEIGHT) / 1000 > b.JANUARY_BUDGET),
                'ABOVE_BUDGET',
                IF(SUM(d.TOTAL_WEIGHT) / 1000 > 0
                        AND b.JANUARY_BUDGET > 0
                        AND (SUM(d.TOTAL_WEIGHT) / 1000 < b.JANUARY_BUDGET),
                    'POOR_RESULT',
                    NULL)))) AS PERFORMANCE   -- here i added the NULL
FROM
    budget2021 b
        LEFT JOIN
    despatches2021 d ON d.SOLD_TO = b.SOLD_TO
WHERE
    b.SOLD_TO = 69946
        AND d.INVOICE_DATE BETWEEN 20210101 AND 20210131
GROUP BY b.SOLD_TO , b.CUSTOMER , b.JANUARY_BUDGET