sql - 在 GROUP BY 后连接一个字段

Question

这个问题在 SO 中被问过很多次，但没有一个答案能满足我的情况。

1. Question 1
2. Question 2
3. Question 3
4. Question 4

我正在处理一个 DataObjectVersions 表，其中包含大约 120 万个唯一对象(并且还在增加)的多个版本。我需要为每个唯一对象连接来自特定字段的更改。

现在我正在使用第 3 季度提供的带有 XML 路径的解决方案，但在此表上运行这样的查询完全是性能灾难。 SQL Server 在 19mn 后开始重新调整数据。知道此数据将被连接两次，您可以想象其影响。

我正在寻找最有效的可扩展性感知方式来连接由另一个字段(当然不是键)分组的不同行的相同字段的值。更准确地说，这是在数据仓库的 View 中使用的。

编辑:

我试图简化描述，但这里有一个完整的概述 我有多个包含以下列的表

   [ID]   [CreatedTime]   [CreatedBy]   [DeletedTime]   [DeletedBy]   [ResourceId]   [AccountId]   [Type]

A view is used to return the union of all records from all tables, which will still return the same columns (described in my questions by the versions table). [ResourceId] and [AccountId] are a unique composite identifier of an object (Group membership, System account, etc.. a resource assignment specifically). The [Type] is used to identify different levels (like Read/Write/Execute in the case of a file assignment)

All other fields contain the same values (in different tables) for different unique objects. I need to get the objects and concatenate the values of the [Type] column. All the row are processed afterward and the ([ResourceId],[AccountId]) combination must be unique (not the case when different types exists).

EDIT 2:

I am using this function:

CREATE FUNCTION [dbo].[GetUniqueType]
(
    @ResourceId as uniqueidentifier,
    @Account as uniqueidentifier
)
RETURNS nvarchar(100)
AS
BEGIN   
    return STUFF((select ',' + raType.Type from vwAllAssignments raType where raType.AccountId = @Account and raType.ResourceId = @ResourceId and raType.DeletedBy is null for xml path('')), 1,1,'')
END

GO

vwAllAssignments 是返回所有表行的并集的 View 。

最后我选择了

SELECT [CreatedTime]
      ,[DeletedTime]
      ,[DeletedBy]
      ,[ResourceId]
      ,[AccountId]
      ,dbo.GetUniqueType([ResourceId],[AccountId]) AS [Type]
FROM vwAllAssignments
GROUP BY [ResourceId], [AccountId], [CreatedTime], [DeletedTime], [DeletedBy]

Answer 1

试试这个:

SELECT [CreatedTime]
      ,[DeletedTime]
      ,[DeletedBy]
      ,[ResourceId]
      ,[AccountId]
      ,STUFF((select ',' + raType.Type 
              from vwAllAssignments raType 
              where raType.AccountId = vwAllAssignments.AccountId and 
                    raType.ResourceId = vwAllAssignments.ResourceId and 
                    raType.DeletedBy is null 
              for xml path('')), 1,1,'') AS [Type]
FROM vwAllAssignments
GROUP BY [ResourceId], [AccountId], [CreatedTime], [DeletedTime], [DeletedBy]

像这样的索引应该会有帮助。

create index IX_vwAllAssignments on vwAllAssignments(AccountId, ResourceId, DeletedBy) include(Type)