每个id sql server只选择一个

Question

我知道这已经问过一百万次了，但由于连接，我发现很难正确编写所需的代码

My SQL is

SELECT DISTINCT
newFvItems.Id, outerFvia.[UserRoleId], outerFvia.[DefaultStatusId], outerFvia.[CanBeAllocated], outerFvia.[CanCreate], outerFvia.[CanUpdate], outerFvia.[CanDelete], outerFvia.[CanSeeDraft], outerFvia.[CanSeeChecking], outerFvia.[CanSeeCompleted], outerFvia.[CanDispute], outerFvia.[CanResolveDispute], outerFvia.[CanAudit], 1, GETUTCDATE(), 393, GETUTCDATE(), 393, 0, outerFvia.[RecycleBinId], outerFvia.[FlowAccessId]
FROM FlowVersionItemAccess outerFvia
JOIN FlowVersionItems outerFvi ON outerFvi.Id = outerFvia.FlowVersionItemId
JOIN FlowVersions outerFv ON outerFv.Id = outerFvi.FlowVersionId
JOIN FlowVersionItems newFvItems ON newFvItems.FlowVersionId = 143
WHERE outerFv.Id = 133
AND outerFvia.Deleted = 0 AND outerFvi.Deleted = 0 AND outerFv.Deleted = 0 

我想要的输出是21行，我得到27如果我删除DISTINCT我得到63

样本数据:

<表类>IDUserIdvalvalvalvalvalvalvalvalvalvalvalvalDateTimevalDateTimevalvalvalvaltbody><<tr>315220111101000112/12/2022 16:5339312/12/2022 16:533930空7315620111101000112/12/2022 16:5339312/12/2022 16:533930空12315720000001000112/12/2022 16:5339312/12/2022 16:533930空16315720110001000112/12/2022 16:5339312/12/2022 16:533930空163151820000000000112/12/2022 16:5339312/12/2022 16:533930空693151820110001000112/12/2022 16:5339312/12/2022 16:533930空69

删除DISTINCT，在查询的最后添加GROUP BY newFvItems.Id，并为您返回的所有列定义聚合函数(MIN, MAX，…)

DISTINCT找到所有列的所有唯一组合，而您只需要一个列。

或者，只留下DISTINCT newFvItems.Id在选择部分-取决于您实际需要。

有两种可靠的方法可以做到这一点，我想我会把我的解决方案贴出来，我最终用它来帮助别人。按解决方案分组很好，也确实提供了正确的答案，但这更适合边缘情况。

SELECT
newFvItems.Id,
outerFvia.[Id],
outerFvia.[UserRoleId],
outerFvia.[DefaultStatusId],
outerFvia.[CanBeAllocated],
outerFvia.[CanCreate],
outerFvia.[CanUpdate],
outerFvia.[CanDelete],
outerFvia.[CanSeeDraft],
outerFvia.[CanSeeChecking],
outerFvia.[CanSeeCompleted],
outerFvia.[CanDispute], outerFvia.[CanResolveDispute], outerFvia.[CanAudit], 1, GETUTCDATE(), 393, GETUTCDATE(), 393, 0, outerFvia.[RecycleBinId], outerFvia.[FlowAccessId]
FROM FlowVersionItemAccess outerFvia
INNER JOIN FlowVersionItems outerFvi ON outerFvi.Id = outerFvia.FlowVersionItemId
AND outerFvi.Deleted = 0
INNER JOIN FlowVersions outerFv ON outerFv.Id = outerFvi.FlowVersionId
AND outerFv.Deleted = 0
INNER JOIN FlowVersionItems newFvItems ON newFvItems.FlowVersionId = 143
AND newFvItems.Deleted = 0
AND newFvItems.[Order] = outerFvi.[Order]
WHERE outerFvia.Deleted = 0
AND outerFv.Id = 133

这也是按解分组。

SELECT
newFvItems.Id, 
outerFvia.[UserRoleId], 
MAX(outerFvia.[DefaultStatusId]), 
MAX(CAST(outerFvia.[CanBeAllocated] As tinyint)), 
MAX(CAST(outerFvia.[CanCreate] as tinyint)), 
MAX(CAST(outerFvia.[CanUpdate] as tinyint)), 
MAX(CAST(outerFvia.[CanDelete] as tinyint)), 
MAX(CAST(outerFvia.[CanSeeDraft] as tinyint)), 
MAX(CAST(outerFvia.[CanSeeChecking] as tinyint)), 
MAX(CAST(outerFvia.[CanSeeCompleted] as tinyint)), 
MAX(CAST(outerFvia.[CanDispute] as tinyint))
--, MAX(outerFvia.[CanResolveDispute]), MAX(outerFvia.[CanAudit]), 1, GETUTCDATE(), 393, GETUTCDATE(), 393, 0, MAX(outerFvia.[RecycleBinId]), MAX(outerFvia.[FlowAccessId])
FROM FlowVersionItemAccess outerFvia
JOIN FlowVersionItems outerFvi ON outerFvi.Id = outerFvia.FlowVersionItemId
JOIN FlowVersions outerFv ON outerFv.Id = outerFvi.FlowVersionId
JOIN FlowVersionItems newFvItems ON newFvItems.FlowVersionId = 143
WHERE outerFv.Id = 133
AND outerFvia.Deleted = 0 AND outerFvi.Deleted = 0 AND outerFv.Deleted = 0 AND newFvItems.Deleted = 0
group by newFvItems.Id, outerFvia.[UserRoleId]

希望我张贴的解决方案，我想出了帮助别人，我知道这个问题被问到很多，如果它不是为连接它会容易得多。