34,590
社区成员
发帖
与我相关
我的任务
分享
[code=SQL]
--------------------------------------------------------------------------------------
-- 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'
Z0004 1400 01
Z0011 1100 01
Z0008 400 121
Z0009 1300 164
L0584 80000 55
Z0004 52100 02 (此处Z0004员工奖金有2个,当有1个以上时,我的视图发觉该员工的工资统计会以N=2重复)
--------------------------------------------------------------------------------------
-- 3、JIGOU表结构(机构表,存放机构名称和机构号)
ID INT --主键递增1
JIGOU CHAR(20) --机构名称
JIGOUHAO CHAR(9) --机构号
--列出JIGOU表的数据
SELECT ID,JIGOU,JIGOUHAO FROM JIGOU
1 A营业厅 519000030
2 B营业厅 519015010
3 C支行 440401005
4 D支行 440401017
5 E营业厅 519060040
6 F营业厅 519070010
--------------------------------------------------------------------------------------
--Question:
--现需统计以下报表(第一、二列需以JIGOU表的所有数据列出,第三、四、五列需从GONGZI和JIANGJINALL表中匹配员工属于对应的机构的求和)
--统计条件是GZYEAR='2011' AND GZMONTH='3' 即统计当前年月即可
机构名称 机构号 应发工资+(奖金类别不为121、55)+住房补贴 福利费(=(工资应发+奖金类别为01)*0.02) 工会费(=(工资应发+奖金类别为01)*0.015)
A营业厅 519000030 5000 2000 800
...
...
...
--------------------------------------------------------------------------------------
--以下是我的视图内容
SELECT TOP (100) PERCENT 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.08 ELSE 0 END) AS col5,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.01 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.004 ELSE 0 END) AS col8,
SUM(CASE WHEN a.LEIBIE <> '劳务工' THEN ISNULL(a.yanglao_bx / 0.08, 0) * 0.16 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 = '2011') AND (a.GZMonth = '3') AND (a.JIGOU <> '')
GROUP BY c.jigouhao, c.jigou, c.id
ORDER BY c.id
select 计算值 as col1,0 as col2,0 as col3 tb
union all
select 0 as col1,计算值 as col2,0 as col3 tb
union all
select 0 as col1,0 as col2,计算值 as col3 tb
select a+(select sum(b.kh_jj) from b where a.主键=b.主键 and b.类别='01'),
a+(select sum(b.kh_jj) from b where a.主键=b.主键 and b.类别='02')...
from a
FROM dbo.GongZi AS a LEFT OUTER JOIN
dbo.JIGOU AS c ON a.JIGOUHAO = c.jigouhao LEFT OUTER JOIN
(SELECT ZHID, SUM(kh_jj) AS kh_jj
FROM dbo.JiangJinAll AS t
WHERE (GZYear = '2011') AND (GZMonth = '3')
GROUP BY ZHID) AS b ON a.zhid = b.ZHID
WHERE (a.GZYear = '2011') AND (a.GZMonth = '3') AND (a.JIGOU <> '')
GROUP BY c.jigouhao, c.jigou, c.id
ORDER BY c.id
SELECT TOP (100) PERCENT 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
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
-- 问题就出在这个左连接上(右表有2条记录)
WHERE (a.GZYear = '2011') AND (a.GZMonth = '3') AND (a.JIGOU <> '')
GROUP BY c.jigouhao, c.jigou, c.id
ORDER BY c.id