How do you find a SQL statement for the chain? The number of resumes this month minus last month's (pictured)

"Number of resumes in the month"-"resumes last month" = "Number of years" ()

How is the SQL statement written?
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 are not so many people!

Come on, give me a hand.

How did January count last month?

How did January count last month?

01 doesn't have to be compared.

Only pure SQL calculation?

Only pure SQL calculation? Combined with PHP is also OK

If you use PHP is very simple, directly with the SQL statement is a bit cumbersome, but not not to!

Isn't there any subtraction in MySQL?

Self-connecting month with last month association
Date_format (m_adddate, '%y%m ') = Date_format (Date_sub (m_adddate,interval 1 MONTH), '%y%m ')

Self-connecting month with last month association
Date_format (m_adddate, '%y%m ') = Date_format (Date_sub (m_adddate,interval 1 MONTH), '%y%m ') spot for details, after connection syntax I'll put it in. Query: 0

You give the test data

Could you 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

Month CV years test (MOM)
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 ')

Could you 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, Oh, the loop is wrong."

You give the test data month resume number year test (MOM)
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 ')

Well, do you want me to build a table for data entry?
Can't you export SQL instructions?

Well, do you want me to build a table for data entry?
Can't you export SQL instructions?


SET foreign_key_checks=0;
-- ----------------------------
--Table structure for ' job_myreceive '
-- ----------------------------
DROP TABLE IF EXISTS ' job_myreceive ';
CREATE TABLE ' job_myreceive ' (
' m_id ' int (ten) unsigned not NULL auto_increment,
' M_rid ' int (ten) not NULL default ' 0 ',
' m_name ' varchar (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 (ten) not NULL default ' 0 ',
' m_place ' varchar (not NULL COMMENT ' candidate position '),
' M_cmember ' varchar (not NULL),
' M_pmember ' varchar (not NULL),
' M_adddate ' datetime not NULL default ' 0000-00-00 00:00:00 ',
' M_read ' tinyint (1) Not NULL default ' 0 ',
' M_content ' text is not NULL,
' M_lang ' tinyint (1) Not NULL default ' 0 ',
PRIMARY KEY (' m_id '),
KEY ' M_place ' (' M_place '),
KEY ' M_hid ' (' M_hid ')
) Engine=myisam auto_increment=111546 DEFAULT CHARSET=GBK;

-- ----------------------------
--Records of Job_myreceive
-- ----------------------------
INSERT into ' job_myreceive ' VALUES (' 74 ', ' 625047 ', ' Li Qiang ', ' 1 ', ' 1980-01-01 ', ' 5 ', ' 251168 ', ' assistant physician ', ' qq102971141 ', ' Hzcxlg Q ', ' 2013-05-28 13:43:17 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 77 ', ' 625047 ', ' Li Qiang ', ' 1 ', ' 1980-01-01 ', ' 5 ', ' 245030 ', ' residency ', ' aabb41724125 ', ' HZCXL GQ ', ' 2010-12-28 13:54:27 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 78 ', ' 675931 ', ' Dr. Wang ', ' 1 ', ' 0000-01-01 ', ' 0 ', ' 271982 ', ' anorectal ', ' zbyyyy ', ' blair123 ') , ' 2010-12-29 21:57:50 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 79 ', ' 691982 ', ' Xie Yong ', ' 1 ', ' 1990-05-10 ', ' 1 ', ' 270201 ', ' driver ', ' persist ', ' Driver ', ' 2010 '- 12-29 21:59:26 ', ' 0 ', ' the driver with the car wants to work inside the clinic. Contact Phone 15817540656 ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 80 ', ' 691989 ', ' Linzeping ', ' 2 ', ' 1985-09-24 ', ' 5 ', ' 270166 ', ' Obstetrics and Gynecology resident ', ' dgsgyy ', ' Linzepi Ng ', ' 2010-12-29 22:00:36 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 82 ', ' 691989 ', ' Linzeping ', ' 2 ', ' 1985-09-24 ', ' 5 ', ' 265725 ', ' Obstetrics and Gynecology ', ' 6514585891AAA ', ' Linz Eping ', ' 2010-12-29 22:05:28 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 83 ', ' 687648 ', ' Jungle Airways ', ' 1 ', ' 1977-06-06 ', ' 5 ', ' 270969 ', ' operating Dean ', ' Xacayy ', ' l608636 '), ' 2011-05-29 22:07:46 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 90 ', ' 687648 ', ' Jungle Airways ', ' 1 ', ' 1977-06-06 ', ' 5 ', ' 261560 ', ' outpatient director ', ' Bagdnui ', ' l608636 ', ' 2010-12-29 22:12:50 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 54523 ', ' 697204 ', ' Yiujie ', ' 1 ', ' 1968-10-21 ', ' 6 ', ' 346100 ', ' outpatient director ', ' Njcxyy ', ' Hbjzyaoj Ie ', ' 2011-07-16 13:29:51 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 92 ', ' 687648 ', ' Jungle Airways ', ' 1 ', ' 1977-06-06 ', ' 5 ', ' 271967 ', ' outpatient director ', ' 518fck ', ' l608636 ', ' 2011-12-29 22:12:50 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 3232 ', ' 692140 ', ' Lu Feng ', ' 1 ', ' 0000-00-00 ', ' 5 ', ' 272140 ', ' Hospital general manager ', ' Zunyi Women's Hospital ', ' pioneering development ', ' 2011-01-16 10:20:40 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 660 ', ' 692067 ', ' Cho Zhibin ', ' 1 ', ' 1984-09-27 ', ' 4 ', ' 265632 ', ' Hospital management director ', ' Yihao ', ' 129119 0 ', ' 2011-01-03 15:43:02 ', ' 0 ', ', ' 0 ');
INSERT into ' job_myreceive ' VALUES (' 96 ', ' 650782 ', ' Dr. Zhang ', ' 1 ', ' 0000-00-00 ', ' 6 ', ' 264340 ', ' hemorrhoid ', ' zg3610 ', ' Yuanmeng ', ' 2011-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      

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

There's obviously only the pink presence last month data

Well, write it clearly.

Select Date_format (a.m_adddate, '%y%m ') as Ym, COUNT (*) as ' month ', (select COUNT (*) from job_myreceive where  date_format (A.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     Month  201012 6      0 201101 2      6 201105 1      0 201107 2      0 201112 1      0 201305 1      0

Well, write it clearly.

Select Date_format (a.m_adddate, '%y%m ') as Ym, COUNT (*) as ' month ', (select COUNT (*) from job_myreceive where  Date_ FORMAT (a.m_adddate, '%y%m ') = Date_format (Date_add (m_adddate,interval 1 month), '%y%m ')) as last month from job_myreceive a group B Y 1

Ym     Month  201012 6      0 201101 2      6 201105 1      0 201107 2      0 201112 1      0 201305 1      0

Thanks to "xuzuning Moderator" help, the realization of my needs!

Can you tell me more about these 2 SELECT statements?

