一條SQL語句如何求環比！這個月簡曆數減去上個月的（如圖）

【這個月簡曆數】-【上個月的簡曆數】=【環比數】（）

sql語句如何寫？
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');

回複討論(解決方案)

原來並不是有那麼多人啊！

冒出個高手，幫忙一下

01月份怎麼算上個月？

01月份怎麼算上個月？

01就不用比了

只能純sql計算？

只能純sql計算？ 結合php也行

如果用PHP就非常簡單了，直接用sql語句有點繁瑣，不過不是不可以！

mysql裡面不是有減法嗎？

自串連當月與上月關聯即可
DATE_FORMAT(m_adddate,'%Y%m') = DATE_FORMAT(DATE_SUB(m_adddate,INTERVAL 1 MONTH),'%Y%m')

自串連當月與上月關聯即可
DATE_FORMAT(m_adddate,'%Y%m') = DATE_FORMAT(DATE_SUB(m_adddate,INTERVAL 1 MONTH),'%Y%m') 斑斑求詳細，串連之後的文法 我貼進去 查詢出：0

你給出測試資料

試試可不可以

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

月份 簡曆數 年份 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語句】：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')

試試可不可以

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

【不行哦 迴圈錯的】

你給出測試資料 月份 簡曆數 年份 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語句】：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')

那你還要我建表錄入資料嗎？
你就不能匯出sql指令？

那你還要我建表錄入資料嗎？
你就不能匯出sql指令？


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 '應聘職位',
`m_cmember` varchar(20) NOT NULL,
`m_pmember` varchar(20) 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 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', '李強', '1', '1980-01-01', '5', '251168', '助理醫師', 'qq102971141', 'hzcxlgq', '2013-05-28 13:43:17', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('77', '625047', '李強', '1', '1980-01-01', '5', '245030', '住院醫師', 'aabb41724125', 'hzcxlgq', '2010-12-28 13:54:27', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('78', '675931', '王醫生', '1', '0000-01-01', '0', '271982', '肛腸科醫師', 'zbyyyy', 'blair123', '2010-12-29 21:57:50', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('79', '691982', '謝勇訪', '1', '1990-05-10', '1', '270201', '司機', 'persist', '司機', '2010-12-29 21:59:26', '0', '本司機帶車想在門診裡面工作。聯絡電話 15817540656', '0');
INSERT INTO `job_myreceive` VALUES ('80', '691989', '林澤萍', '2', '1985-09-24', '5', '270166', '婦產科住院醫師', 'dgsgyy', 'linzeping', '2010-12-29 22:00:36', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('82', '691989', '林澤萍', '2', '1985-09-24', '5', '265725', '婦產科', '6514585891AAA', 'linzeping', '2010-12-29 22:05:28', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('83', '687648', '林子航', '1', '1977-06-06', '5', '270969', '經營院長', 'xacayy', 'l608636', '2011-05-29 22:07:46', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('90', '687648', '林子航', '1', '1977-06-06', '5', '261560', '門診主任', 'bagdnui', 'l608636', '2010-12-29 22:12:50', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('54523', '697204', '姚傑', '1', '1968-10-21', '6', '346100', '門診主任', 'njcxyy', 'hbjzyaojie', '2011-07-16 13:29:51', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('92', '687648', '林子航', '1', '1977-06-06', '5', '271967', '門診主任', '518fck', 'l608636', '2011-12-29 22:12:50', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('3232', '692140', '呂鋒', '1', '0000-00-00', '5', '272140', '醫院總經理', '遵義女子醫院', '開拓發展', '2011-01-16 10:20:40', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('660', '692067', '卓志彬', '1', '1984-09-27', '4', '265632', '醫院經營管理主任', 'yihao', '1291190', '2011-01-03 15:43:02', '0', '', '0');
INSERT INTO `job_myreceive` VALUES ('96', '650782', '張醫生', '1', '0000-00-00', '6', '264340', '痔科', '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      0 

在你給出的資料中
m_adddate 只有這些
'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'

顯然只有桃紅的存在上月資料

嗯，這樣寫清楚些

SELECT DATE_FORMAT(a.m_adddate,'%Y%m') as Ym, count(*) as `當月`,(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 上月 FROM job_myreceive a group by 1

Ym     當月   上月  201012 6      0 201101 2      6 201105 1      0 201107 2      0 201112 1      0 201305 1      0

嗯，這樣寫清楚些

SELECT DATE_FORMAT(a.m_adddate,'%Y%m') as Ym, count(*) as `當月`,(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 上月 FROM job_myreceive a group by 1

Ym     當月   上月  201012 6      0 201101 2      6 201105 1      0 201107 2      0 201112 1      0 201305 1      0

感謝【xuzuning版主】的幫忙，實現了我的需求！

再請問一下這種2個select語句還有其他寫法嗎

