MySQL CASE WHEN 语法如何并接子查询

[cs5117155]

    select amount,sid,
	CASE 
	WHEN force_status=1 THEN  select sum(amount) as amount,sid  FROM(select amount-force_amount as amount,sid from lease_order WHERE force_status=1 and `sid` IN (7,19)) as temp GROUP BY sid
  else 0 end as amount 
    from order;

我有一个表order,当字段force_status=1时则使用force_amount-amount差额进行累加,并对sid进行分组统计，当字段force_status!=1,则使用amount统计并sid分组。 如果我单独执行

select sum(amount) as amount,sid  FROM(select amount-force_amount as amount,sid from order WHERE force_status=1 and `sid` IN (7,19)) as temp GROUP BY sid

则没有问题，会显示如下信息

amount	sid
1.04	7
0.03	19

当使用CASE WHEN则报错，望大神能帮我抽空看看是哪里出问题了。最终效果需要实现成

amount	sid
2.03	8
0.03	19
8.03	31
9.13	20

这样效果

[c6h6benzene]

sum (case when force_status=1 then force_amount-amount else amount) group by sid

[c6h6benzene]

(少了一个 end😅）

[cs5117155]

@c6h6benzene 还是不知道怎么写😅，能把 CASE WHEN 也加上，写个详细的吗

[c6h6benzene]

@cs5117155 #3 就…其实不需要子查询。case when 可以写在 sum 里面的。

select sid, sum(case when force_status=1 then force_amount-amout else amount end) from order group by sid

[cs5117155]

@c6h6benzene good,谢谢可以了，第一次写这个 case when 有点绕了