我这么写统计不出所有员工一个月的考勤,SUM只是统计某个员工某个月的考勤,等于没用
SELECT SUM(lateCount)as lc,sum(earlyCount)as ec,sum(outCount)as oc,userName,lateCount,earlyCount,outCount from AttendanceInfo group by moth,userName,lateCount,earlyCount,outCount
我这么写SUM是起作用了,可是其他信息又没查出来
SELECT SUM(lateCount)as lc,sum(earlyCount)as ec,sum(outCount)as oc from AttendanceInfo group by moth各位前辈帮帮忙啊,谢谢啦
SELECT SUM(lateCount)as lc,sum(earlyCount)as ec,sum(outCount)as oc,userName,lateCount,earlyCount,outCount from AttendanceInfo group by moth,userName,lateCount,earlyCount,outCount
我这么写SUM是起作用了,可是其他信息又没查出来
SELECT SUM(lateCount)as lc,sum(earlyCount)as ec,sum(outCount)as oc from AttendanceInfo group by moth各位前辈帮帮忙啊,谢谢啦
寫SQL要和報表工具結合起來.不一定是SQL全寫好.報表只要Query就可以了.
很多報表樣式,報表只需要作一些設置就可以完成.
userName,moth from AttendanceInfo group by moth,userName
首先把每个人在每个月的统计出来。然后在报表中按照月的进行分组就是了,报表中都有合计的功能的。
统计总的也问题应该不大。
insert into @TableTest values( '张三',3,2,6,1)
insert into @TableTest values( '张三',1,2,1,2)
insert into @TableTest values( '张三',15,12,12,3) insert into @TableTest values( '李四',23,5,3,1)
insert into @TableTest values( '李四',21,2,1,2)
insert into @TableTest values( '李四',0,12,12,3) insert into @TableTest values( '王五',3,2,4,1)
insert into @TableTest values( '王五',5,6,1,2)
insert into @TableTest values( '王五',3,12,12,3)
select userName , Moth , sum(lateCount) as lateCount , sum(earlyCount) as earlyCount,sum(outCount) as outCount from @TableTest
group by userName , Moth
union
select Convert(varchar(50),Moth) as userName , Moth , sum(lateCount) as lateCount , sum(earlyCount) as earlyCount,sum(outCount) as outCount from @TableTest
group by Moth
union
select '合计' as userName, 13 as Moth ,sum(lateCount) as lateCount , sum(earlyCount) as earlyCount,sum(outCount) as outCount from @TableTest
2、或者是用存储过程,返回不同查询值。
3、或者用触发器,每次记录改变时根据条件,添加一个月合计记录,如果有了合计的记录,就累加。
大概就想到这些