- t1 = "select 工号,姓名,部门,FORMAT(日期,'YYYY年MM月') AS 日期,合计 from [salary$] UNION ALL select NULL as 工号,NULL as 姓名,部门&'小计' as 部门,FORMAT(日期,'YYYY年MM月') as 日期,sum(合计) as 合计 from [salary$] group by 部门,日期,FORMAT(日期,'YYYY年MM月')"
- t2 = "select 工号,姓名,部门,sum(合计) as 合计,count(FORMAT(日期,'YYYY年MM月')) AS 数量,FORMAT(日期,'YYYY年MM月') AS 日期 from (" & t1 & ") group by FORMAT(日期,'YYYY年MM月'),工号,姓名,部门"
- Sql = "transform sum(合计) select 工号,姓名,部门,SUM(合计) AS 合计,ROUND(AVG(合计),2) as 月均,ROUND(SUM(合计)/SUM(数量),2) as 日均 from (" & t2 & ") group by 工号,姓名,部门 ORDER BY 部门 pivot 日期"
复制代码
我这样写实现了,效率咋样不知道。 |