一条SQL语句如何求环比！这个月简历数减去上个月的（如图）

Justy1989 2013-08-16 03:43:56

【这个月简历数】-【上个月的简历数】=【环比数】（如图）

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');

...全文

3455 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

Justy1989 2013-08-20

打赏
举报

引用 19 楼 xuzuning 的回复:

嗯，这样写清楚些

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语句还有其他写法吗

xuzuning 2013-08-16

打赏
举报

嗯，这样写清楚些

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 2013-08-16

打赏
举报

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' 显然只有桃红的存在上月数据

xuzuning 2013-08-16

打赏
举报

那你还要我建表录入数据吗？你就不能导出sql指令？

Justy1989 2013-08-16

打赏
举报

引用 11 楼 xuzuning 的回复:

你给出测试数据

月份简历数年份 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')

Justy1989 2013-08-16

打赏
举报

引用 12 楼 jordan102 的回复:

试试可不可以

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

【不行哦循环错的】

Justy1989 2013-08-16

打赏
举报

一起混吧 2013-08-16

打赏
举报

试试可不可以

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

xuzuning 2013-08-16