I have two tables tableA and tableB.
Note: id in table a corresponds to company_id in table b.
Requirement description: Take out companies that meet (2013+2014) earning>50 or 2014 earning>20
The desired query is this
How did you realize this inquiry? How to write SQL statement?
The eligible ones are selected in two situations
It seems to be complicated.
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)
If earning in 2013 and 2014 are taken as the two field of the table, the logic of SQL will be much clearer:
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.early plus e4.early as early from e3 inner join e4 using(id) left join tableA a using(id) Where e4.early > 20ore3.early plus e4.earning > 50