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:

Mysql implements three functions for field splicing
Detailed explanation of MySQL string concatenation function GROUP_CONCAT
Detailed example of concatenating multiple fields in mysql

<<: HTML Table Tag Tutorial (47): Nested Tables

>>: How to use VLAN tagged Ethernet card in CentOS/RHEL system

Navicat for MySQL scheduled database backup and data recovery details

Implementation steps of Mysql merge results and horizontal splicing fields

Preface

My steps

I have tried the following

Summarize

Navicat for MySQL scheduled database backup and data recovery details

Detailed explanation of homology and cross-domain required for front-end interviews

Solution to many line breaks and carriage returns in MySQL data

jQuery+Ajax to achieve simple paging effect

A universal nginx interface to implement reverse proxy configuration

HTML tag ID can be a variable

Implementation of code optimization for Vue2.x project performance optimization

Detailed explanation of several examples of insert and batch statements in MySQL

A brief discussion on the problem of forgotten mysql password and login error

js realizes packaging multiple pictures into zip

Recommend

Method example of safely getting deep objects of Object in Js

Example of usage of keep-alive component in Vue

Detailed explanation of new relational database features in MySQL 8.0

How to click on the a tag to pop up the input file upload dialog box

HTML head tag detailed introduction

Draw a heart with CSS3

How to mark the source and origin of CSS3 citations

Front-end implementation of GBK and GB2312 encoding and decoding of strings (summary)

Summary of common MySQL table design errors

A brief talk about JavaScript Sandbox

Use Docker Compose to quickly deploy ELK (tested and effective)

Reflection and Proxy in Front-end JavaScript

Detailed steps to change the default password when installing MySQL in Ubuntu

Linux directory switching implementation code example

JavaScript implements double-ended queue