谢谢!我已经加了“总计”项,完美实现。
我对SQL还是不熟悉,有时候不知道语法错误怎么解决
- 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月') 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 (部门=""总计"") DESC,部门 pivot 日期"
复制代码
transform语句.zip
(79.42 KB, 下载次数: 34)
|