with cid(id) as (
select company_id from tableB
where year = 2014 and earning > 20
union
select company_id from tableB
where year in (2013, 2014)
group by company_id having sum(earning) > 50
), cid_earning(id, earning) as (
select company_id, sum(earning) from tableB
where company_id in (select id from cid) and year in (2013, 2014)
group by company_id
)
select a.company_name, c.earning
from cid_earning c left join tableA a using(id)
思路二
如果把2013和2014年的earning作为表的两个field,SQL的逻辑会清晰很多:
with
e3(id, earning) as (
select company_id, earning from tableB
where year = 2013),
e4(id, earning) as (
select company_id, earning from tableB
where year = 2014)
select a.company_name, e3.earning + e4.earning as earning
from e3 inner join e4 using(id)
left join tableA a using(id)
where e4.earning > 20 or e3.earning + e4.earning > 50
思路一
分两种情况选出符合要求的
company_id
并union
把这些
company_id
的earning
求和(2013-2014)连接上
company_name
好像搞的比较复杂。
思路二
如果把2013和2014年的earning作为表的两个field,SQL的逻辑会清晰很多:
好复杂哦,同问,这样的sql怎么写,我在想是不是可以写个存储过程,毕竟存储过程处理这样复杂的逻辑容易一点