Queries between Multiple Tables in MySQL Database

  mysql, question

I have two tables tableA and tableB.

tableA

id company_name
1 AL
2 BD
3 TX
4 SY

tableB

id company_id year earning
1 1 2012 10
2 1 2013 40
3 1 2014 20
4 2 2013 10
5 2 2014 30
6 3 2013 20
7 3 2014 10
8 4 2012 20
9 4 2013 30
10 4 2014 30

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

company_name earning(值为这个公司13年+14的收入)
AL 60
DB 50
SY 60

How did you realize this inquiry? How to write SQL statement?

Idea 1

  1. The eligible ones are selected in two situationscompany_idAndUnion

  2. Put thesecompany_idTheearningSummation (2013-2014)

  3. Connectcompany_name

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)

Idea 2

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

Related Posts: