求助说明
输出的表格按照“姓名”“实际完成”“实际出勤”“平均完成”来输出
实际完成=完成+系数 实际出勤=白班(1)+中班(1)+夜班(1)+半天(0.5)
平均完成=实际完成/实际出勤
自己书写的语句是
SELECT
姓名,实际完成/实际出勤 AS 平均完成
FROM(
SELECT 姓名, SUM(完成+系数) AS 实际完成,SUM(出勤天数) AS 实际出勤
FROM(
SELECT *,IIF(出勤="半天",0.5,IIF(出勤="白班",1,IIF(出勤="中班",1,IIF(出勤="夜班",1,0)))) AS 出勤天数
FROM [一月$])
GROUP BY 姓名)
select *,iif(出勤=0,0,完成/出勤) as 平均完成 from (select 姓名,sum(实际完成) as 完成,sum(出勤天数) as 出勤 from (select 姓名,完成+系数 as 实际完成,出勤天数 from (SELECT 姓名,iif(isnull(完成),0,完成) as 完成,iif(isnull(系数),0,系数) as 系数 ,IIF(出勤="半天",0.5,IIF(出勤="白班",1,IIF(出勤="中班",1,IIF(出勤="夜班",1,0)))) AS 出勤天数 FROM [一月$])) group by 姓名)
select 姓名,sum(出勤天数) as 实际出勤,sum(完成+系数) as 实际完成,sum(出勤天数)/sum(完成+系数) as 平均完成 from (SELECT 姓名,完成,iif(isnull(系数),0,系数) as 系数 ,IIF(出勤="半天",0.5,IIF(出勤="白班",1,IIF(出勤="中班",1,IIF(出勤="夜班",1,0)))) AS 出勤天数 FROM [一月$]) group by 姓名
select *,iif(出勤=0,0,完成/出勤) as 平均完成 from (select 姓名,sum(实际完成) as 完成,sum(出勤天数) as 出勤 from (select 姓名,完成+系数 as 实际完成,出勤天数 from (SELECT 姓名,iif(isnull(完成),0,完成) as 完成,iif(isnull(系数),0,系数) as 系数 ,IIF(出勤="半天",0.5,IIF(出勤="白班",1,IIF(出勤="中班",1,IIF(出勤="夜班",1,0)))) AS 出勤天数 FROM [一月$])) group by 姓名)