【这个月简历数】-【上个月的简历数】=【环比数】(如图)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');
试试可不可以 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')
月份 简历数 年份 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指令?
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
DATE_FORMAT(m_adddate,'%Y%m') = DATE_FORMAT(DATE_SUB(m_adddate,INTERVAL 1 MONTH),'%Y%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
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')
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指令?
(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 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语句还有其他写法吗