MySql中，求一张表中同字段不同条件的sum值

最新推荐文章于 2022-02-21 17:14:20 发布

zhangwei_123a

最新推荐文章于 2022-02-21 17:14:20 发布

阅读量9.3k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： sql 文章标签： sql

本文链接：https://blog.youkuaiyun.com/zhangjianmin_123/article/details/52610974

sql 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，旨在根据不同条件查询同一字段的不同汇总值。通过联接多个表并使用union all操作来实现对fina_finaflow表中finaflow_amount字段的两组不同条件下的汇总查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求：我想根据不同的条件，去查询某个表中同一个字段的不同的sum值

解决：

1、首先有3张表，如下图

2、问题：我想查询fina_finaflow 表中finaflow_amount两个不同条件不同的sum值。

3、方案：sql语句如下：

select 
    sum(a.finaflow_amount) AS sumCome 
from
    fina_finaflow a
    join fina_finaflow b ON a.id = b.id
    right join fina_contract_mainbill c on a.mainbillid = c.id
    left join fina_finaflow_property d on a.finaflow_prop = d.id
where 
    c.id in (select id from fina_contract_mainbill where mainbilltype = 1)
    and d.proptype = 1
    and a.communityid = 5
    and a.customerid = 1 
    and a.occuryear >= "2016-09-19"

union all 

select 
    sum(b.finaflow_amount) AS sumAmount
from
    fina_finaflow a
    join fina_finaflow b ON a.id = b.id
    right join fina_contract_mainbill c on a.mainbillid = c.id
    left join fina_finaflow_property d on a.finaflow_prop = d.id
where 
    c.id in (select id from fina_contract_mainbill where mainbilltype = 1)
    and d.propname <> "定金" 
    and d.propname <> "押金" 
    and d.proptype = 2
    and a.communityid = 5
    and a.customerid = 1
    and a.occuryear >= "2016-09-19"