Implementation steps of Mysql merge results and horizontal splicing fields

Preface

Recently, I was working on a report function and there was a requirement to count the number of people who joined and left each department in a certain month.

My steps

First find out the number of employees

SELECT dept ,COUNT(1) rcNumber FROM employee table WHERE (Joining time!= ''
  OR Joining Date IS NOT NULL) and DATE_FORMAT(Joining Date, '%Y-%m') = '2019-09'
GROUP BY department ID
ORDER BY Department Name

Query records

In querying the number of people who have resigned, sql:

SELECT dept ,COUNT(1) rcNumber FROM employee table WHERE (resignation date != ''
  OR Leaving Date IS NOT NULL) and DATE_FORMAT(Joining Date, '%Y-%m') = '2019-09'
GROUP BY department ID
ORDER BY Department Name

Result Set

The data I want is this

I have 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, but I think if there are more records in the right table, won’t there be less data if I use this method? (The same applies to the right)

2. I use union all, which is not the data I want, to directly add the two results and splice them vertically

3. I used select * from a,b and the result is the Cartesian product of the two tables.

I won't post the SQL for the above method, but the meaning should be clear.

I didn't believe it so I kept asking Baidu. Baidu finally gave me an answer so I tried it.

1. Process the entry sql as follows

SELECT a.dept,a.rcNumber,0 as lcNumber FROM (SELECT dept ,COUNT(1) rcNumber FROM employee table WHERE (Joining time!= ''
  OR Joining Date IS NOT NULL) and DATE_FORMAT(Joining Date, '%Y-%m') = '2019-09'
GROUP BY department ID
ORDER BY department name) a

The resignation sql is processed as follows:

SELECT a.dept,a.lcNumber,0 as rcNumber FROM (SELECT dept ,COUNT(1) rcNumber FROM employee table WHERE (resignation date != ''
  OR Leaving Date IS NOT NULL) and DATE_FORMAT(Joining Date, '%Y-%m') = '2019-09'
GROUP BY department ID
ORDER BY department name) a

You can also add a layer outside or not. I just add it on the original SQL to avoid destroying the basic statement. Of course, this is not enough.

2. Vertically join the two statements and join them with 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 FROM employee table WHERE (Joining time!= ''
  OR Joining Date IS NOT NULL) and DATE_FORMAT(Joining Date, '%Y-%m') = '2019-09'
GROUP BY department ID
ORDER BY department name) a) c GROUP 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 FROM employee table WHERE ( resignation time != ''
  OR quit_date IS NOT NULL) and DATE_FORMAT(join_date, '%Y-%m') = '2019-09'
GROUP BY department ID
ORDER BY department name) a) d GROUP BY d.dept) t GROUP BY t.dept ORDER BY t.id

Finally I got the result I wanted

Summarize

This is the end of this article about MySQL merge results and horizontal splicing fields. For more relevant MySQL merge results and horizontal splicing fields, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: