select (CASE WHEN b.month = '2023-05' THEN b.create_income ELSE 0 END) as two_income from a left join b on b.kefuid = a.id left join d on d.kefuid = a.id where a.brand = '16' and a.is_del = 0 and a.is_lizhi = 0 group by a.id limit 1;必须要通过sum才能正确的输出case when

根据你提供的 SQL 语句，CASE WHEN 后面的语句会根据 b.month 的值来进行逻辑判断，并返回 b.create_income 或 0。在这个查询语句中，由于使用了 GROUP BY 对 a.id 进行分组，所以必须使用聚合函数才能对每个分组进行计算。如果不使用聚合函数，会出现错误或者无法得到正确的结果。

因此，你需要使用 SUM 函数来对 two_income 进行求和，正确输出结果。例如，可以修改你的 SQL 语句如下：

select sum(CASE WHEN b.month = '2023-05' THEN b.create_income ELSE 0 END) as two_income from a left join b on b.kefuid = a.id left join d on d.kefuid = a.id where a.brand = '16' and a.is_del = 0 and a.is_lizhi = 0 group by a.id limit 1;

这里使用了 SUM 函数对 two_income 进行求和，从而得到正确的结果。

相关问题

select a.id,SUM(CASE WHEN d.shoukuan_month = '2023-05' and d.is_del = 0 then d.netincome ELSE 0 end) as one_shoukuan,SUM(CASE WHEN d.shoukuan_month = '2023-04' AND d.is_del = 0 THEN d.netincome ELSE 0 END) as two_shoukuan,(CASE WHEN b.month = '2023-05' THEN b.create_income ELSE 30000 END) as one_income,(CASE WHEN b.month = '2023-04' THEN b.create_income ELSE 30000 END) as two_income from a left join b on b.kefuid = a.id left join d on d.kefuid = a.id where a.brand = '1' and a.is_del = 0 and a.is_lizhi = 0 group by a.id这个sql如何让select 中的d.one_shoukuan >= b.one_income and d.two_shoukuan >= b.two_income

你可以将该 SQL 作为一个子查询，在外部 SELECT 语句中添加一个 WHERE 子句来实现该要求：

SELECT *
FROM (
    SELECT a.id,
        SUM(CASE WHEN d.shoukuan_month = '2023-05' AND d.is_del = 0 THEN d.netincome ELSE 0 END) AS one_shoukuan,
        SUM(CASE WHEN d.shoukuan_month = '2023-04' AND d.is_del = 0 THEN d.netincome ELSE 0 END) AS two_shoukuan,
        (CASE WHEN b.month = '2023-05' THEN b.create_income ELSE 30000 END) AS one_income,
        (CASE WHEN b.month = '2023-04' THEN b.create_income ELSE 30000 END) AS two_income
    FROM a
    LEFT JOIN b ON b.kefuid = a.id
    LEFT JOIN d ON d.kefuid = a.id
    WHERE a.brand = '1' AND a.is_del = 0 AND a.is_lizhi = 0
    GROUP BY a.id
) AS subquery
WHERE subquery.one_shoukuan >= subquery.one_income AND subquery.two_shoukuan >= subquery.two_income;

这样，子查询将返回每个客服的 `one_shoukuan`，`two_shoukuan`，`one_income` 和 `two_income` 值，而外部查询将筛选出满足 `one_shoukuan >= one_income` 和 `two_shoukuan >= two_income` 的记录。