preface

Recently, one of the requirements in a report function is to count the number of employees and departures in each department in a certain month

My steps

  • Find out how many people are hired


SELECT  dept ,COUNT(1) rcNumber    FROMThe employee tableWHERE(Entry time! =' '
        ORIn the timeIS NOT NULL) and DATE_FORMAT(Entry time,'%Y-%m') ='the 2019-09'
GROUP BYdepartmentID
ORDER BYDepartment nameCopy the code


Query log



  • SQL: Query the number of people who quit;
SELECT  dept ,COUNT(1) rcNumber    FROMThe employee tableWHERE(Quit time! =' '
        ORDeparture timeIS NOT NULL) and DATE_FORMAT(Entry time,'%Y-%m') ='the 2019-09'
GROUP BYdepartmentID
ORDER BYDepartment nameCopy the code

The result set



  • The data I want is this


I tried the following

  • 1. I regard the two query results as two tables and use left Join. To be honest, the data format is what I want.

  • 2. I used union all, which was not the desired data, to directly add the two results and join them vertically

  • Select * from a,b; select * from b

The above method of SQL will not post the meaning should be clear

I don’t believe I ask Baidu all the time, Baidu finally had an answer I tried

1. Process the incoming SQL as follows
SELECT a.dept,a.rcNumber,0 as lcNumber  FROM (SELECT  dept ,COUNT(1) rcNumber    FROMThe employee tableWHERE(Entry time! =' '
        ORIn the timeIS NOT NULL) and DATE_FORMAT(Entry time,'%Y-%m') ='the 2019-09'
GROUP BYdepartmentID
ORDER BYACopy the code

Demission SQL processing is as follows:

SELECT a.dept,a.lcNumber,0 as rcNumber FROM (SELECT  dept ,COUNT(1) rcNumber    FROMThe employee tableWHERE(Quit time! =' '
        ORDeparture timeIS NOT NULL) and DATE_FORMAT(Entry time,'%Y-%m') ='the 2019-09'
GROUP BYdepartmentID
ORDER BYACopy the code

It is not enough to add a layer to the original SQL so as not to break the basic statement

2. Combine the two statements vertically to join sum
SELECT dept ,sum(cm_1) as rcNumber,sum(cm_0) as lcNumber FROM( SELECT c.id,c.dept,SUM(c.lcNumber) as cm_0,c.rcNumber as cm_1 FROM 
(SELECT a.dept,a.rcNumber,0 as lcNumber  FROM (SELECT  dept ,COUNT(1) rcNumber    FROMThe employee tableWHERE(Entry time! =' '
        ORIn the timeIS NOT NULL) and DATE_FORMAT(Entry time,'%Y-%m') ='the 2019-09'
GROUP BYdepartmentID
ORDER BYA) of C) of D) ofGROUP BY c.dept
UNION ALL 
SELECT d.id,d.dept,d.lcNumber as cm_0,SUM(d.rcNumber) as cm_1 FROM 
(SELECT a.dept,a.lcNumber,0 as rcNumber  FROM (SELECT  dept ,COUNT(1) rcNumber    FROMThe employee tableWHERE(Quit time! =' '
        ORTime of departureIS NOT NULL) and DATE_FORMAT(Entry time,'%Y-%m') ='the 2019-09'
GROUP BYdepartmentID
ORDER BYA) department B) department D) departmentGROUP BY d.dept) t GROUP BY t.dept ORDER BY t.id
Copy the code

I ended up with what I wanted