How to calculate the period-over-period ratio of an SQL statement! The number of resumes in this month minus the number in the previous month (as shown in the figure)

How to calculate the period-over-period ratio of an SQL statement! The number of resumes in this month minus the number of resumes in the previous month () [number of resumes in this month]-[number of resumes in the previous month] = [number of resumes in the previous month ]()

How to write SQL statements?
SELECT DATE_FORMAT (m_adddate, '% M') as month, count (m_id) as zong, DATE_FORMAT (m_adddate,' % Y') as year from job_myreceive where DATE_FORMAT (m_adddate, '% Y') = 2011 group by DATE_FORMAT (m_adddate,' % M ');

Reply to discussion (solution)

There were not so many people!

Come up with a master. please help me.

How does one calculate last month in February?

How does one calculate last month in February?

01.

Only SQL computing is allowed?

Only SQL computing is allowed? It works with php.

If PHP is used, it is very simple. it is a little complicated to directly use SQL statements, but it is not impossible!

Is there any subtraction in mysql?

Associate the current month with the previous month.
DATE_FORMAT (m_adddate, '% Y % M') = DATE_FORMAT (DATE_SUB (m_adddate, INTERVAL 1 MONTH),' % Y % M ')

Associate the current month with the previous month.
DATE_FORMAT (m_adddate, '% Y % M') = DATE_FORMAT (DATE_SUB (m_adddate, INTERVAL 1 MONTH),' % Y % M') spot for details, after the connection, I paste it to query the following syntax: 0.

Test data

Can I try it?

select DATE_FORMAT(t1.m_adddate,'%m')  as month, t1.m_id,DATE_FORMAT(t1.m_adddate,'%Y') as year,t1.m_id-t2.m_id from job_myreceive t1 left join job_myreceive t2 on month(t1.m_adddate)-1=month(t2.m_adddate) where DATE_FORMAT(t1.m_adddate,'%Y')=2011

Monthly resume count year test)
01 4864 2011 0
02 8193 2011 0
03 7182 2011 0
04 8472 2011 0
05 7565 2011 0
06 7062 2011 0
07 5620 2011 0
08 5994 2011 0
09 5059 2011 0
10 5574 2011 0
11 3428 2011 0
12 5260 2011 0

[SQL statement]: SELECT DATE_FORMAT (m_adddate, '% M') as month, count (m_id) as zong, DATE_FORMAT (m_adddate,' % Y') as year,
DATE_FORMAT (m_adddate, '% Y % M') = DATE_FORMAT (DATE_SUB (m_adddate, INTERVAL 1 MONTH),' % Y % M') as test
From job_myreceive where DATE_FORMAT (m_adddate, '% Y') = 2011 group by DATE_FORMAT (m_adddate,' % M ')

Can I try it?

select DATE_FORMAT(t1.m_adddate,'%m')  as month, t1.m_id,DATE_FORMAT(t1.m_adddate,'%Y') as year,t1.m_id-t2.m_id from job_myreceive t1 left join job_myreceive t2 on month(t1.m_adddate)-1=month(t2.m_adddate) where DATE_FORMAT(t1.m_adddate,'%Y')=2011

[No. it's a loop error]

The number of resumes in the month that you provided the test data)
01 4864 2011 0
02 8193 2011 0
03 7182 2011 0
04 8472 2011 0
05 7565 2011 0
06 7062 2011 0
07 5620 2011 0
08 5994 2011 0
09 5059 2011 0
10 5574 2011 0
11 3428 2011 0
12 5260 2011 0

[SQL statement]: SELECT DATE_FORMAT (m_adddate, '% M') as month, count (m_id) as zong, DATE_FORMAT (m_adddate,' % Y') as year,
DATE_FORMAT (m_adddate, '% Y % M') = DATE_FORMAT (DATE_SUB (m_adddate, INTERVAL 1 MONTH),' % Y % M') as test
From job_myreceive where DATE_FORMAT (m_adddate, '% Y') = 2011 group by DATE_FORMAT (m_adddate,' % M ')

Do you want to create a table and input data?
Why can't you export SQL commands?

Do you want to create a table and input data?
Why can't you export SQL commands?


SET FOREIGN_KEY_CHECKS = 0;
------------------------------
-- Table structure for 'job _ myreceive'
------------------------------
Drop table if exists 'job _ myreceive ';
Create table 'job _ myreceive '(
'M _ id' int (10) unsigned not null auto_increment,
'M _ rid' int (10) not null default '0 ',
'M _ name' varchar (50) not null,
'M _ sex' tinyint (1) not null default '0 ',
'M _ birth 'date not null,
'M _ edu' tinyint (2) not null default '0 ',
'M _ hid 'int (10) not null default '0 ',
'M _ place 'varchar (50) not null comment 'position applicant ',
'M _ cmember' varchar (20) not null,
'M _ pmember' varchar (20) not null,
'M _ adddate' datetime not null default '2017-00-00 00:00:00 ',
'M _ READ' tinyint (1) not null default '0 ',
'M _ content' text not null,
'M _ lang 'tinyint (1) not null default '0 ',
Primary key ('m _ id '),
KEY'm _ place '('m _ place '),
KEY'm _ id' ('m _ id ')
) ENGINE = MyISAM AUTO_INCREMENT = 111546 default charset = gbk;

------------------------------
-- Records of job_myreceive
------------------------------
Insert into 'job _ myreceive 'values ('74 ', '000000', 'Li Qiang', '1', '2017-01-01 ', '5', '2016 ', 'assistant physician ', 'qq102971141', 'hzcxlgq', '2017-05-28 13:43:17 ', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('77 ', '000000', 'Li Qiang', '1', '2017-01-01 ', '5', '2016 ', 'resident physician ', 'aabb201724125', 'hzcxlgq', '2017-12-28 13:54:27 ', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('78 ', '000000', 'Dr Wang', '1', '2017-01-01 ', '0', '2016 ', 'anorectal physician ', 'zbyyyy', 'blair123', '2017-12-29 21:57:50', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('79 ', '000000', 'Xie Yong access', '1', '2017-05-10', '1', '2017010 ', 'Drivers ', 'persist', 'Drivers', '2017-12-29 21:59:26 ', '0', 'the driver wants to work in the clinic. Contact number 15817540656 ', '0 ');
Insert into 'job _ myreceive 'values ('80', '000000', 'ringzapp', '2', '2017-09-24 ', '5', '2016 ', 'obstetrics and gynecology resident ', 'dgsgyy', 'linzeping', '2017-12-29 22:00:36', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('82 ', '000000', 'Lin zeping', '2', '2017-09-24', '5', '123 ', 'obstetrics and gynecology ', '6514585891aaa', 'linzeping', '2017-12-29 22:05:28', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('83 ', '000000', 'Lin Zihang', '1', '2017-06-06 ', '5', '123 ', 'business Presidents', 'xacayy', 'l608636 ', '2017-05-29 22:07:46', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('90', '000000', 'Forest airlin', '1', '2017-06-06 ', '5', '2017060 ', 'outpatient directory', 'bagdnui ', 'l608636', '2017-12-29 22:12:50 ', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('20160301', '20160301', 'yaojie', '1', '2017-10-21 ', '6', '20160301 ', 'outpatient directory', 'njcxy', 'hbjzyaojie ', '2017-07-16 13:29:51', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('92 ', '123456', 'Lin Zihang', '1', '2017-06-06 ', '5', '123456 ', 'outpatient directory', '18fck', 'l608636 ', '2017-12-29 22:12:50', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('20160301', '20160301', 'lufeng', '1', '2017-00-00 ', '5', '20160301 ', 'General Manager of hospital ', 'zunyi Women's Hospital', 'pioneering development ', '2017-01-16 10:20:40', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('20160301', '20160901', 'zhuo bin', '1', '2017-09-27 ', '4', '20160301 ', 'hospital management director ', 'yihao', '123', '2017-01-03 15:43:02', '0', '', '0 ');
Insert into 'job _ myreceive 'values ('96 ', '000000', 'Dr Zhang', '1', '2017-00-00 ', '6', '123 ', 'yunko', 'zg3610', 'Yuanmeng', '2017-07-29 22:40:15 ', '0', '', '0 ');

SELECT DATE_FORMAT(a.m_adddate,'%Y%m') as Ym, count(*) as cnt_a,(select count(*) from job_myreceive where  DATE_FORMAT(a.m_adddate,'%Y%m') = DATE_FORMAT(DATE_SUB(m_adddate,INTERVAL 1 MONTH),'%Y%m')) as cnt_b FROM job_myreceive a group by 1

Ym     cnt_a  cnt_b  201012 6      2 201101 2      0 201105 1      0 201107 2      0 201112 1      0 201305 1      0 

In the data you provided
M_adddate only
'2017-12-29 22:12:50'
'2017-12-28 13:54:27'
'2017-12-29 21:57:50'
'2017-12-29 21:59:26'
'2017-12-29 22:00:36'
'2017-12-29 22:05:28'
'2017-01-16 10:20:40'
'2017-01-03 15:43:02'
'2017-05-29 22:07:46'
'2017-07-16 13:29:51'
'2017-07-29 22:40:15'
'2017-12-29 22:12:50'
'2017-05-28 13:43:17'

Apparently, only the data of the previous month exists in Taohong.

Well, you can write it clearly.

SELECT DATE_FORMAT (. m_adddate, '% Y % M') as Ym, count (*) as 'month', (select count (*) from job_myreceive where DATE_FORMAT (. m_adddate, '% Y % M') = DATE_FORMAT (DATE_add (m_adddate, INTERVAL 1 MONTH),' % Y % M') as last month from job_myreceive a group by 1

Ym in the month of 201101 201105 6 0 201107 2 6 201112 1 0 201305 2 0 1 0 1 0

Well, you can write it clearly.

SELECT DATE_FORMAT (. m_adddate, '% Y % M') as Ym, count (*) as 'month', (select count (*) from job_myreceive where DATE_FORMAT (. m_adddate, '% Y % M') = DATE_FORMAT (DATE_add (m_adddate, INTERVAL 1 MONTH),' % Y % M') as last month from job_myreceive a group by 1

Ym in the month of 201101 201105 6 0 201107 2 6 201112 1 0 201305 2 0 1 0 1 0

Thanks to the help of the [xuzuning moderator] to meet my needs!

Is there any other way to write these two select statements?