根据唯一 ID 对 SQL Server 中联接的多个表中的某些列求和

Question

我的查询用于报告月薪，所以当从 1 - 2021 到 8 - 2021 中选择月份时，它会像这样报告 我想为所有选定的月份一行中的每个徽章编号获得相同的结果我有以下查询，从多个表中收集数据

SELECT
dbo.USERINFO.BADGENUMBER ,
dbo.Emp_OV_LT.Overtime ,
dbo.Emp_OV_LT.Absent , 
(dbo.Emp_OV_LT.Late + dbo.Emp_OV_LT.Early) , 
ROUND((dbo.Salaries.B_S), 3),
ROUND((dbo.Salaries.T_A + dbo.Salaries.W_A + dbo.Salaries.L_A + dbo.Salaries.C_A), 3) ,
ROUND( dbo.Salaries.Overtime, 3),
ROUND(dbo.Salaries.Absent, 3),
ROUND( dbo.Salaries.Late_Deduction,3) , 
ROUND(dbo.Salaries.Total_Salary,3), 
dbo.Salaries.SSN ,
ROUND(dbo.Salaries.n_salary,3) 
FROM 
dbo.USERINFO 
INNER JOIN 
dbo.Salaries ON dbo.USERINFO.USERID = dbo.Salaries.User_ID 
INNER JOIN 
dbo.Emp_Salary ON dbo.USERINFO.USERID = dbo.Emp_Salary.Emp_ID
INNER JOIN 
dbo.Emp_OV_LT ON dbo.Emp_OV_LT.Emp_ID = dbo.USERINFO.USERID 
WHERE
dbo.Salaries.Month = dbo.Emp_OV_LT.Month 
AND dbo.Salaries.Month BETWEEN '1 - 2021' AND '8 - 2021' 
AND dbo.Emp_OV_LT.year = '2021' 
AND dbo.Salaries.ssn > 1 
ORDER BY
dbo.userinfo.ssn,
dbo.Emp_OV_LT.Month

我想根据更改月份条件超过一个月后重复USERINFO.BADGENUMBER对所有选定的列求和

Answer 1

我的查询用于报告月薪，因此当从 1 - 2021 到 1 - 2021 中选择月份时，它会像这样报告好的，我将尝试一下，我相信您要完成的事情。我已经盯着你的帖子一段时间了，并做了一些假设。

你说

我想对所有选定的列求和

起初，我以为您想对 SELECT 子句中包含的所有列求和，但这没有意义。所以我假设你想选择你求和的列。因此，假设您要对dbo求和。Emp_OV_LT。每个 dbo 每月/每年加班。用户信息。徽章编号。我相信你会做这样的事情。

WITH CTE AS
(
SELECT
dbo.USERINFO.BADGENUMBER ,
dbo.Emp_OV_LT.Overtime ,
dbo.Emp_OV_LT.Absent , 
(dbo.Emp_OV_LT.Late + dbo.Emp_OV_LT.Early) , 
ROUND((dbo.Salaries.B_S), 3),
ROUND((dbo.Salaries.T_A + dbo.Salaries.W_A + dbo.Salaries.L_A + 
dbo.Salaries.C_A), 3) ,
ROUND( dbo.Salaries.Overtime, 3),
ROUND(dbo.Salaries.Absent, 3),
ROUND( dbo.Salaries.Late_Deduction,3) , 
ROUND(dbo.Salaries.Total_Salary,3), 
dbo.Salaries.SSN ,
ROUND(dbo.Salaries.n_salary,3) ,
dbo.Salaries.Month,
dbo.Emp_OV_LT.year
FROM 
dbo.USERINFO 
INNER JOIN 
dbo.Salaries ON dbo.USERINFO.USERID = dbo.Salaries.User_ID 
INNER JOIN 
dbo.Emp_Salary ON dbo.USERINFO.USERID = dbo.Emp_Salary.Emp_ID
INNER JOIN 
dbo.Emp_OV_LT ON dbo.Emp_OV_LT.Emp_ID = dbo.USERINFO.USERID 
WHERE
dbo.Salaries.Month = dbo.Emp_OV_LT.Month     
AND dbo.Salaries.ssn > 1 
ORDER BY
dbo.userinfo.ssn,
dbo.Emp_OV_LT.Month
)
SELECT DISTINCT *,
SUM(Overtime) OVER (PARTITION BY [Month], 
BADGENUMBER]) AS SUM_OF_OverTime
FROM CTE
WHERE [Month] BETWEEN '1 - 2021' AND '8 - 2021' AND [year] = '2021' 

在没有访问数据库和表或一些示例数据的情况下，这是我能想到的最好的。

编辑时，您必须将dbo.Salaries.Month添加到原始SELECT，因为您正在按该字段进行分区。此外，还应将字段移动到SELECTdbo.Emp_OV_LT.year，并应在WHERE子句中使用该字段。