mysql查询之间的数据_MySQL数据库多表之间的查询

思路一

分两种情况选出符合要求的company_id并union

把这些company_id的earning求和(2013-2014)

连接上company_name

好像搞的比较复杂。

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