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
时间: 2024-02-26 17:56:58 浏览: 16
你可以将该 SQL 作为一个子查询,在外部 SELECT 语句中添加一个 WHERE 子句来实现该要求:
```sql
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` 的记录。