SQL Server 如何在不>>的情况下加入多个 ID 导致 SUM 从执行到执行的更改

Question

我有以下表格：

CREATE TABLE [Hours](
[Activity Month] [datetime],
[Employee_ID] [nvarchar](10),
[Activity Date] [datetime],
[Hours] [float]
) 
CREATE TABLE [Employee_IDs](
[Month] [datetime],
[Employee_ID_1] [nvarchar](10),
[Employee_ID_2] [nvarchar](10),
[Employee_ID_3] [nvarchar](10),
[Status] [nvarchar](10)
)

其中 [Employee_ID] 可以等于表 [Employee_IDs] 中的一个或多个 ID：

[Employee_ID_1]=[Employee_ID_2]=[Employee_ID_3]
[Employee_ID_1]<>[Employee_ID_2]<>[Employee_ID_3]
[Employee_ID_1]=[Employee_ID_2]<>[Employee_ID_3]
[Employee_ID_1]<>[Employee_ID_2]=[Employee_ID_3]

如何

Select [Employee_ID], [Activity Month], [Status], Sum([Hours]) 
WHERE [Activity Month]=[Month] 

尝试识别所有TABLE [Hours][Employee_IDs]上所有 3 个可能的 ID 上的 TABLE 中的所有[Employee_ID]，而不会重复导致不正确的Sum([Hours])？

没有重复项 如果我使用以下作为解决方案查询：

SELECT   [Employee_ID], [Activity Month], [Status] , SUM([Hours]) as Sum_Hours
FROM
(
SELECT [Employee_ID], [Activity Month], [Status], [Hours] 
FROM [Hours] H1
INNER JOIN [Employee_IDs] E1 ON H1.[Employee_ID] = E1.Employee_ID_1
UNION 
SELECT [Employee_ID], [Activity Month], [Status], [Hours] 
FROM [Hours] H1
INNER JOIN [Employee_IDs] E2 ON H1.[Employee_ID] = E2.Employee_ID_2
UNION 
SELECT [Employee_ID], [Activity Month], [Status], [Hours] 
FROM [Hours] H1
INNER JOIN [Employee_IDs] E3 ON H1.[Employee_ID] = E2.Employee_ID_3
)T
WHERE T.[Activity Month]=@Month
GROUP BY  [Employee_ID], [Activity Month], [Status]

但是，如果我尝试选择

SELECT   count(distinct [Employee_ID]) as HC, [Activity Month], [Status], SUM([Hours]) as Sum_Hours
FROM
(
SELECT [Employee_ID], [Activity Month], [Status], [Hours] 
FROM [Hours] H1
INNER JOIN [Employee_IDs] E1 ON H1.[Employee_ID] = E1.Employee_ID_1
UNION 
SELECT [Employee_ID], [Activity Month], [Status], [Hours] 
FROM [Hours] H1
INNER JOIN [Employee_IDs] E2 ON H1.[Employee_ID] = E2.Employee_ID_2
UNION 
SELECT [Employee_ID], [Activity Month], [Status], [Hours] 
FROM [Hours] H1
INNER JOIN [Employee_IDs] E3 ON H1.[Employee_ID] = E2.Employee_ID_3
)T
WHERE T.[Activity Month]=@Month
GROUP BY  [Activity Month], [Status]

然后我面临着Sum_Hours的问题，从执行到执行有一些微小的变化，而没有对数据表进行更改。

如果我将解决方案查询的结果插入到表解决方案中并运行：

SELECT count(distinct [Employee_ID]) as HC, [Activity Month], [Status], SUM([Hours]) as Sum_Hours FROM Solution Group By [Activity Month], [Status]

从执行到执行Sum_Hours没有变化。

您知道如何直接在解决方案查询中避免更改总和吗？

Answer 1

除非我错过了什么，否则一个简单的IN就可以完成这项工作：

SELECT DISTINCT [Employee_ID], [Activity Month], [Status], Sum(H.[Hours]) 
FROM [Hours] H
INNER JOIN [Employee_IDs] E ON H.[Activity Month] = E.[Month]
AND H.[Employee_ID] IN([Employee_ID_1], [Employee_ID_2], [Employee_ID_3])
GROUP BY [Employee_ID], [Activity Month], [Status]

Answer 2

select
A.[Employee_ID], A.[Activity Month], B.[Status], Sum(A.[Hours])
from
Hours A
inner join Employee_IDs B on (A.Activity Month = @Month
and (A.Employee_ID = B.Employee_ID_1
or A.Employee_ID = B.Employee_ID_2
or A.Employee_ID = B.Employee_ID_3))
group by A.[Employee_ID], A.[Activity Month], B.[Status]