SELECT 姓名, SUM(挡车出勤) AS 挡车出勤, SUM(挡车工资) AS 挡车工资,case when SUM(挡车出勤)<>0 then SUM(挡车工资) / SUM(挡车出勤) else 0 end AS 挡日均工资, SUM(帮车出勤) AS 帮车出勤, SUM(帮车工资)
AS 帮车工资, SUM(帮车工资) / SUM(帮车出勤) AS 帮日均工资, SUM(装梭出勤)
AS 装梭出勤, SUM(装梭工资) AS 装梭工资,case when SUM(装梭出勤)<>0 then SUM(装梭工资) / SUM(装梭出勤) else 0 end
AS 装日均工资
FROM (SELECT 姓名, SUM(挡车出勤) AS 挡车出勤, 0 挡车工资, SUM(帮车出勤) AS 帮车出勤,
0 帮车工资, SUM(装梭出勤) AS 装梭出勤, 0 装梭工资
FROM (SELECT xm 姓名, COUNT(DISTINCT rq) 挡车出勤, 0 帮车出勤,
0 装梭出勤
FROM zyxjk8
WHERE month(rq) = '8'
GROUP BY xm
UNION
SELECT bcgxm 姓名, 0 挡车出勤, COUNT(DISTINCT rq) 帮车出勤,
0 装梭出勤
FROM zyxjk8
WHERE month(rq) = '8'
GROUP BY bcgxm
UNION
SELECT zsgxm 姓名, 0 挡车出勤, 0 帮车出勤, COUNT(DISTINCT rq)
装梭出勤
FROM zyxjk8
WHERE month(rq) = '8'
GROUP BY zsgxm) a
GROUP BY 姓名
UNION all
SELECT 姓名, 0 挡车出勤, SUM(挡车工资) 挡车工资, 0 帮车出勤, SUM(帮车工资)
帮车工资, 0 装梭出勤, SUM(装梭工资) 装梭工资
FROM (SELECT xm 姓名, SUM(xj) 挡车工资, 0 帮车工资, 0 装梭工资
FROM zgzsjhs
WHERE gx = '挡车工'
GROUP BY xm
UNION
SELECT xm 姓名, 0 挡车工资, SUM(xj) 帮车工资, 0 装梭工资
FROM zgzsjhs
WHERE gx = '帮车工'
GROUP BY xm
UNION
SELECT xm 姓名, 0 挡车工资, 0 帮车工资, SUM(xj) 装梭工资
FROM zgzsjhs
WHERE gx = '装梭工'
GROUP BY xm) b
GROUP BY 姓名) c
GROUP BY 姓名
AS 帮车工资, SUM(帮车工资) / SUM(帮车出勤) AS 帮日均工资, SUM(装梭出勤)
AS 装梭出勤, SUM(装梭工资) AS 装梭工资,case when SUM(装梭出勤)<>0 then SUM(装梭工资) / SUM(装梭出勤) else 0 end
AS 装日均工资
FROM (SELECT 姓名, SUM(挡车出勤) AS 挡车出勤, 0 挡车工资, SUM(帮车出勤) AS 帮车出勤,
0 帮车工资, SUM(装梭出勤) AS 装梭出勤, 0 装梭工资
FROM (SELECT xm 姓名, COUNT(DISTINCT rq) 挡车出勤, 0 帮车出勤,
0 装梭出勤
FROM zyxjk8
WHERE month(rq) = '8'
GROUP BY xm
UNION
SELECT bcgxm 姓名, 0 挡车出勤, COUNT(DISTINCT rq) 帮车出勤,
0 装梭出勤
FROM zyxjk8
WHERE month(rq) = '8'
GROUP BY bcgxm
UNION
SELECT zsgxm 姓名, 0 挡车出勤, 0 帮车出勤, COUNT(DISTINCT rq)
装梭出勤
FROM zyxjk8
WHERE month(rq) = '8'
GROUP BY zsgxm) a
GROUP BY 姓名
UNION all
SELECT 姓名, 0 挡车出勤, SUM(挡车工资) 挡车工资, 0 帮车出勤, SUM(帮车工资)
帮车工资, 0 装梭出勤, SUM(装梭工资) 装梭工资
FROM (SELECT xm 姓名, SUM(xj) 挡车工资, 0 帮车工资, 0 装梭工资
FROM zgzsjhs
WHERE gx = '挡车工'
GROUP BY xm
UNION
SELECT xm 姓名, 0 挡车工资, SUM(xj) 帮车工资, 0 装梭工资
FROM zgzsjhs
WHERE gx = '帮车工'
GROUP BY xm
UNION
SELECT xm 姓名, 0 挡车工资, 0 帮车工资, SUM(xj) 装梭工资
FROM zgzsjhs
WHERE gx = '装梭工'
GROUP BY xm) b
GROUP BY 姓名) c
GROUP BY 姓名
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货