|
这个语句。SELECT 出差人员,SUM(住宿补贴) AS 住宿补贴,SUM(餐费补贴) AS 餐费补贴 FROM (SELECT T1.日期,T1.出差人员1 AS 出差人员,T1.住宿补贴/T2.人数 AS 住宿补贴,T1.餐费补贴/T2.人数 AS 餐费补贴 FROM (SELECT 日期,出差人员1,住宿补贴,餐费补贴 FROM [Sheet1$] WHERE 出差人员1 IS NOT NULL UNION ALL SELECT 日期,出差人员2,住宿补贴,餐费补贴 FROM [Sheet1$] WHERE 出差人员2 IS NOT NULL UNION ALL SELECT 日期,出差人员3,住宿补贴,餐费补贴 FROM [Sheet1$] WHERE 出差人员3 IS NOT NULL) AS T1 ,(SELECT 日期,COUNT(*) AS 人数 FROM (SELECT 日期 FROM [Sheet1$] WHERE 出差人员1 IS NOT NULL UNION ALL SELECT 日期 FROM [Sheet1$] WHERE 出差人员2 IS NOT NULL UNION ALL SELECT 日期 FROM [Sheet1$] WHERE 出差人员3 IS NOT NULL) GROUP BY 日期) AS T2 WHERE T1.日期=T2.日期) GROUP BY 出差人员
不错啊。 |
|