--------------------------------------------------------------------------------------
-- 1、GONGZI表结构(存放工资数据,其它字段略)ZHID CHAR(5) --员工唯一ID
YINGFA FLOAT --员工应发工资
JIGOUHAO CHAR(9) --员工所属机构号,9位数字
JIGOU CHAR(20) --员工所属机构名称
ZFBT FLOAT --住房补贴
GZYEAR CHAR(4) --工资年份,如2010
GZMONTH CHAR(2) --工资月份 如4,12--列出GONGZI表的数据(只列出部份数据,其它略)
SELECT zhid,jigouhao FROM GONGZI WHERE GZYEAR='2010' AND GZMONTH='5' AND JIGOU<>''Z0009 519000030 A营业厅
Z0008 519015010 B营业厅
Z0004 440401005 C支行
Z0011 440401017 D支行
L0584 519060040 E营业厅
L0449 519070010 F营业厅
L0583 519060010 A营业厅
P0069 440401021 B支行
L0409 519000030 A营业厅 --------------------------------------------------------------------------------------
-- 2、JIANGJINALL表结构(存放奖金数据,其它字段略)
ZHID CHAR(5) --员工唯一ID
KH_JJ FLOAT --员工奖金
LEIBIE CHAR(3) --奖金类别(其中LEIBIE='01'代表月奖)
GZYEAR CHAR(4) --工资年份,如2010
GZMONTH CHAR(2) --工资月份 如4,12--列出JIANGJINALL表的数据(只列出部份数据,其它略)
SELECT ZHID,KH_JJ,LEIBIE FROM JIANGJINALL WHERE GZYEAR='2010' AND GZMONTH='5'(注意,上条件中可能会出现同一ZHID有多个员工奖金,因奖金类别LEIBIE不同)
Z0004 1400 01
Z0004 5000 11
Z0011 1100 01
Z0008 400 121
Z0009 1300 164
L0584 80000 55--------------------------------------------------------------------------------------
-- 3、JIGOU表结构(机构表,存放机构名称和机构号)
ID INT --主键递增1
JIGOU CHAR(20) --机构名称
JIGOUHAO CHAR(9) --机构号--列出JIGOU表的数据
SELECT ID,JIGOU,JIGOUHAO FROM JIGOU1 A营业厅 519000030
2 B营业厅 519015010
3 C支行 440401005
4 D支行 440401017
5 E营业厅 519060040
6 F营业厅 519070010----------------------------------------------------------------------------------------Question:
--现需统计以下报表(第一、二列需以JIGOU表的所有数据列出,第三、四、五列需从GONGZI和JIANGJINALL表中匹配员工属于对应的机构的求和)
--统计条件是GZYEAR='2010' AND GZMONTH='5' 即统计当前年月即可机构名称 机构号 应发工资+(奖金类别不为121、55)+住房补贴 福利费(=(工资应发+奖金类别为01)*0.02) 工会费(=(工资应发+奖金类别为01)*0.015)
A营业厅 519000030 5000 2000 800
...
...
...
--------------------------------------------------------------------------------------
我用以下视图但只要JIANGJINALL表里有同一ZHID但LEIBIE不一样时统计GONGZI表的实发YINGFA就会有误,即上报表第三列的数值有误,如何改才正确,求?
SELECT c.id, c.jigou, c.jigouhao, SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END)
+ SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(b.kh_jj, 0) ELSE 0 END) + SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.ZFBT, 0)
ELSE 0 END) AS col1, (SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END) + SUM(CASE WHEN a.LEIBIE <> '劳务工' AND
b.LEIBIE = '01' THEN b.KH_JJ ELSE 0 END)) * 0.02 AS col2, (SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END)
+ SUM(CASE WHEN a.LEIBIE <> '劳务工' AND b.LEIBIE = '01' THEN b.KH_JJ ELSE 0 END)) * 0.015 AS col3,
SUM(CASE WHEN a.LEIBIE = '在岗职工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.18 WHEN a.LEIBIE = '聘用工' THEN ISNULL(a.yanglao_bx / 0.08, 0)
* 0.10 ELSE 0 END) AS col4, SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.06 ELSE 0 END) AS col5,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.003 ELSE 0 END) AS col6,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.007 ELSE 0 END) AS col7,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.002 ELSE 0 END) AS col8,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.15 ELSE 0 END) AS col9,
SUM(CASE WHEN a.LEIBIE = '劳务工' THEN (ISNULL(a.YingFa, 0) + (ISNULL(a.yanglao_bx_lw, 0) / 0.08) * 0.172 + 40) ELSE 0 END)
+ SUM(CASE WHEN a.LEIBIE = '劳务工' THEN ISNULL(b.kh_jj, 0) ELSE 0 END) AS col10, SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN 50 ELSE 0 END)
AS col11
FROM dbo.GongZi AS a LEFT OUTER JOIN
dbo.JIGOU AS c ON a.JIGOUHAO = c.jigouhao LEFT OUTER JOIN
dbo.JiangJinAll AS b ON a.zhid = b.ZHID AND a.GZYear = b.GZYear AND a.GZMonth = b.GZMonth
WHERE (a.GZYear = '2010') AND (a.GZMonth = '8') AND (a.JIGOU <> '')
GROUP BY c.jigouhao, c.jigou, c.id
ORDER BY c.id
-----
统计月工资,为什么不见你的视图里有这个条件?
JIANGJINALL表里统计时不需要考虑LEIBIE了,只要关联表时ZHID作为ON条件就SUM(kh_jj)
1、先统计gongzi和jiangjinall,得出每个月每个zhid的一条合计数据
create view v_1 as
select a.zhid,a.GZYear,a.GZMonth,a.jigouhao
(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END)+
SUM(CASE WHEN a.LEIBIE <> '劳务工' and b.LEIBIE not in('121','55') THEN ISNULL(b.kh_jj, 0) ELSE 0 END)+
(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.ZFBT, 0) ELSE 0 END) AS col1, (CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END)
+ SUM(CASE WHEN a.LEIBIE <> '劳务工' AND b.LEIBIE = '01' THEN b.KH_JJ ELSE 0 END) * 0.02 AS col2, (CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END)
+ SUM(CASE WHEN a.LEIBIE <> '劳务工' AND b.LEIBIE = '01' THEN b.KH_JJ ELSE 0 END) * 0.015 AS col3from GongZi as a left join dbo.JiangJinAll AS b ON a.zhid = b.ZHID AND a.GZYear = b.GZYear AND a.GZMonth = b.GZMonth
group by a.zhid,a.GZYear,a.GZMonth,a.jigouhao,a.LEIBIE,ISNULL(a.YingFa, 0)
2、再用视图v_1连接jigou表,汇总机构select c.id, c.jigou, c.jigouhao, sum(a.col1),sum(a.col2),sum(a.col3)
from dbo.JIGOU AS c left join v_1 a ON a.JIGOUHAO = c.jigouhao
WHERE (a.GZYear = '2010') AND (a.GZMonth = '8') AND (a.JIGOU <> '')
GROUP BY c.jigouhao, c.jigou, c.id
ORDER BY c.id
SELECT c.id, c.jigou, c.jigouhao, SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END)
+ SUM(CASE WHEN a.LEIBIE <> '劳务工' AND b.LEIBIE NOT IN ('121','55') THEN ISNULL(b.kh_jj, 0) ELSE 0 END) + SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.ZFBT, 0)
ELSE 0 END) AS col1, (SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END) + SUM(CASE WHEN a.LEIBIE <> '劳务工' AND
b.LEIBIE = '01' THEN b.KH_JJ ELSE 0 END)) * 0.02 AS col2, (SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.YingFa, 0) ELSE 0 END)
+ SUM(CASE WHEN a.LEIBIE <> '劳务工' AND b.LEIBIE = '01' THEN b.KH_JJ ELSE 0 END)) * 0.015 AS col3,
SUM(CASE WHEN a.LEIBIE = '在岗职工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.18 WHEN a.LEIBIE = '聘用工' THEN ISNULL(a.yanglao_bx / 0.08, 0)
* 0.10 ELSE 0 END) AS col4, SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.06 ELSE 0 END) AS col5,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.003 ELSE 0 END) AS col6,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.007 ELSE 0 END) AS col7,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.002 ELSE 0 END) AS col8,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.15 ELSE 0 END) AS col9,
SUM(CASE WHEN a.LEIBIE = '劳务工' THEN (ISNULL(a.YingFa, 0) + (ISNULL(a.yanglao_bx_lw, 0) / 0.08) * 0.172 + 40) ELSE 0 END)
+ SUM(CASE WHEN a.LEIBIE = '劳务工' THEN ISNULL(b.kh_jj, 0) ELSE 0 END) AS col10, SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN 50 ELSE 0 END)
AS col11
FROM dbo.GongZi AS a LEFT OUTER JOIN
dbo.JIGOU AS c ON a.JIGOUHAO = c.jigouhao LEFT OUTER JOIN
dbo.JiangJinAll AS b ON a.zhid = b.ZHID AND a.GZYear = b.GZYear AND a.GZMonth = b.GZMonth
WHERE (a.GZYear = '2010') AND (a.GZMonth = '8') AND (a.JIGOU <> '')
GROUP BY c.jigouhao, c.jigou, c.id
ORDER BY c.id