select C.GZ_YM AS 缴费年月, SUM(C.C21163) AS 养老保险单位缴纳, SUM(C.C2144) AS 养老保险个人缴纳, SUM(C.C21163+C.C2144) AS 养老合计, SUM(C.C21165) AS 失业保险单位缴纳, SUM(C.C2153) AS 失业保险个人缴纳, SUM(C.C21165+C.C2153) AS 失业合计, SUM(C.C21166) AS 工伤保险单位缴纳, SUM(C.C21164) AS 医疗保险单位缴纳, SUM(C.C2147) AS 医疗保险个人缴纳, SUM(C.C21164+C.C2147) AS 医疗合计, SUM(C.C21163+C.C21165+C.C21164+C.C21166) AS 单位缴纳总合计, SUM(C.C2144+C.C2153+C.C2147) AS 个人缴纳总合计, SUM(C.C21163+C.C21165+ C.C21164+C.C21166+C.C2144+C.C2153+C.C2147) AS 总合计 from c21 left join b01 on c21.dept_id = b01.dept_id where b01.dept_code like (SELECT A.DEPT_CODE FROM B01 A WHERE A.Dept_ID = B01.DEPT_ID )+'%' AND GRADE = 2 GROUP BY C.GZ_YM详解
时间: 2024-04-17 17:23:50 浏览: 16
这个查询语句是用于计算不同缴费年月的养老保险、失业保险、工伤保险和医疗保险的缴纳情况。下面是对查询语句的详细解释:
```sql
SELECT
C.GZ_YM AS 缴费年月,
SUM(C.C21163) AS 养老保险单位缴纳,
SUM(C.C2144) AS 养老保险个人缴纳,
SUM(C.C21163+C.C2144) AS 养老合计,
SUM(C.C21165) AS 失业保险单位缴纳,
SUM(C.C2153) AS 失业保险个人缴纳,
SUM(C.C21165+C.C2153) AS 失业合计,
SUM(C.C21166) AS 工伤保险单位缴纳,
SUM(C.C21164) AS 医疗保险单位缴纳,
SUM(C.C2147) AS 医疗保险个人缴纳,
SUM(C.C21164+C.C2147) AS 医疗合计,
SUM(C.C21163+C.C21165+C.C21164+C.C21166) AS 单位缴纳总合计,
SUM(C.C2144+C.C2153+C.C2147) AS 个人缴纳总合计,
SUM(C.C21163+C.C21165+C.C21164+C.C21166+C.C2144+C.C2153+C.C2147) AS 总合计
FROM c21
LEFT JOIN b01 ON c21.dept_id = b01.dept_id
WHERE b01.dept_code LIKE (SELECT A.DEPT_CODE FROM B01 A WHERE A.Dept_ID = B01.DEPT_ID)+'%'
AND GRADE = 2
GROUP BY C.GZ_YM
```
这个查询从 `c21` 表和 `b01` 表中获取数据,并通过 `dept_id` 进行左连接。然后,根据条件筛选出符合要求的数据,其中 `b01.dept_code` 的值与子查询结果匹配,并且 `GRADE` 等于 2。最后,使用 `GROUP BY` 对结果按照 `GZ_YM` 进行分组,计算每个缴费年月的养老保险、失业保险、工伤保险和医疗保险的缴纳情况,并计算各项合计值。