楼主
wise |
Q：如何通过SQL汇总各员工的本月业绩及最近三个月业绩？
A：主要的SQL语句如下：
- SELECT B.工号 AS 工号,本月业绩,最近三个月业绩
- FROM
- (SELECT 工号,SUM(业绩) AS 本月业绩
- FROM [Sheet2$]
- WHERE MONTH(日期)=(SELECT MONTH(MAX(日期)) FROM [Sheet2$])
- GROUP BY 工号) A
- RIGHT OUTER JOIN
- (SELECT 工号,SUM(业绩) AS 最近三个月业绩
- FROM [Sheet2$]
- WHERE MONTH(日期)>=(SELECT MONTH(MAX(日期))-2 FROM [Sheet2$])
- GROUP BY 工号) B
- ON A.工号=B.工号
 按时间周期求和.rar
|
2楼
Rongson_Chart |
学习~！ |
3楼
LOGO |
员工各月都有业绩时，各员工最近一个月业绩以及最近三个月业绩：- select b.工号,最近一个月业绩,最近三个月业绩 from (select 工号,sum(业绩) as 最近一个月业绩 from [Sheet2$] where month(日期)=month(dmax("日期","Sheet2$","工号='"&工号&"'")) group by 工号) a
- right join
- (select 工号,SUM(业绩) as 最近三个月业绩 from [sheet2$] where monTH(dmax("日期","Sheet2$","工号='"&工号&"'"))-MONTH(日期)<3 GROUP BY 工号) b
- on a.工号=b.工号
|
4楼
LOGO |
同一年内最近一期业绩及最近三期业绩（以月为期,员工并不是每期都有业绩，期与期之间的月份差距有可能为1,2...）- select b.工号,最近一期业绩,最近三期业绩 from (select 工号,sum(业绩) as 最近一期业绩 from [Sheet2$] where month(日期)=month(dmax("日期","Sheet2$","工号='"&工号&"'")) group by 工号) a right join (select 工号,sum(业绩) as 最近三期业绩 from [sheet2$] where 工号&'-'&month(日期) in (select 工号&'-'&月份 from (select 工号,month(日期) as 月份 from [Sheet2$] group by 工号,month(日期)) a where 月份 in (select top 3 月份 from (select 工号,month(日期) as 月份 from [Sheet2$] group by 工号,month(日期)) b where b.工号= a.工号 order by 月份 desc)) group by 工号) b on a.工号=b.工号
|
5楼
LOGO |
把可能会存在跨年与跨期同时考虑进去的最近一期业绩以及最近三期业绩：- select b.工号,最近一期业绩,最近三期业绩 from (select 工号,sum(业绩) as 最近一期业绩 from [Sheet2$] where format(日期,'yyyymm')=format(dmax("日期","Sheet2$","工号='"&工号&"'"),'yyyymm') group by 工号) a right join (select 工号,sum(业绩) as 最近三期业绩 from [sheet2$] where 工号&'-'&format(日期,'yyyymm') in (select 工号&'-'&月份 from (select 工号,format(日期,'yyyymm') as 月份 from [Sheet2$] group by 工号,format(日期,'yyyymm')) a where 月份 in (select top 3 月份 from (select 工号,format(日期,'yyyymm') as 月份 from [Sheet2$] group by 工号,format(日期,'yyyymm')) b where b.工号= a.工号 order by 月份 desc)) group by 工号) b on a.工号=b.工号
|