sql - 过滤重复条目
问题描述
我正在使用这个 SQL 查询来获取所有 Azure DevOps Server 用户的列表以及他们的项目和上次访问时间。
Select Object1.DisplayName as 'User Name',
Object1.SamAccountName as 'R-User',
Object2.DisplayName as 'Projekt Name',
StartTime AS Last_Access
From ADObjectMemberships Member1,
ADObjects Object1,
ADObjects Object2,
tbl_command
Where Object1.ObjectSID = Member1.MemberObjectSID and
Object2.ObjectSID = Member1.ObjectSID and
Object1.DisplayName NOT LIKE '%\%' and
Object1.DisplayName NOT LIKE 'APP_%' and
Object1.DisplayName NOT LIKE 'CLI_%' and
Object1.DisplayName NOT LIKE '%admin%' and
Object1.DisplayName NOT LIKE '%svc%'
Order By 'Projekt Name', 'User Name'
可悲的是,我得到了这个结果,因为您可以看到每个访问都有一个结果。
Username User Login Project Time
Mustermann, Max RXXXXXX [FOO]\BAR 2019-10-21
06:26:26.987
Mustermann, Max RXXXXXX [FOO]\BAR 2019-10-21
06:26:26.940
Mustermann, Max RXXXXXX [FOO]\BAR 2019-10-21
06:26:26.923
Mustermann, Max RXXXXXX [FOO]\BAR 2019-10-21
06:26:26.940
Mustermann, Max RXXXXXX [FOO]\BAR 2019-10-21
06:26:26.940
Mustermann, Max RXXXXXX [FOO]\BAR2 2019-10-21
06:26:25.937
Mustermann, Max RXXXXXX [FOO]\BAR2 2019-10-21
06:26:26.923
Mustermann, Max RXXXXXX [FOO]\BAR2 2019-10-21
06:26:27.000
Mustermann, Max RXXXXXX [FOO]\BAR2 2019-10-21
06:26:26.327
我现在的目标是每个用户每天只有一个条目。当用户在多个项目中工作时,例如上面的 Foo\Bar 和 Foo\Bar2 示例,每个项目只需要一个条目。
我已经尝试过 DISTINCT 命令,但对我来说它不起作用,也许我做错了什么。希望你们中的某个人可以帮助我:)
//编辑 06.11.2019
我现在使用这段代码,它向我展示了所有项目的所有用户以及所有访问时间,所以我必须在最后一次(最大)时间过滤它。当我使用 MAX(StarTime) 时,我为每个用户获得相同的时间。
USE [Tfs_DefaultCollection]
Select
Object1.DisplayName as 'User Name',Object1.SamAccountName as 'R-User',
Object2.DisplayName as 'Projekt Name',StartTime AS 'Letzter Zugriff'
From ADObjectMemberships Member1,
ADObjects Object1,
ADObjects Object2,
tbl_command
Where Object1.ObjectSID = Member1.MemberObjectSID and
Object2.ObjectSID = Member1.ObjectSID and
Object1.DisplayName NOT LIKE '%\%' and
Object1.DisplayName NOT LIKE 'APP_%' and
Object1.DisplayName NOT LIKE 'CLI_%' and
Object1.DisplayName NOT LIKE '%admin%' and
Object1.DisplayName NOT LIKE '%svc%'
GROUP By Object1.DisplayName, Object1.SamAccountName, Object2.DisplayName, StartTime
//编辑 05.11.2019
SELECT TOP (10000000) [PartitionId] ,[CommandId] ,[Application] ,[Command] ,[Status] ,[StartTime] ,[ExecutionTime] ,[IdentityName] ,[IPAddress] ,[UniqueIdentifier] ,[UserAgent] ,[ CommandIdentifier] ,[ExecutionCount] ,[TempCorrelationId] ,[AuthenticationType] ,[AgentId] ,[ResponseCode] ,[TimeToFirstPage] ,[DelayTime] FROM [Tfs_DefaultCollection].[dbo].[tbl_Command]
就是这样的结构。在 [IdentityName] 中是“R-User”-> Object1.SamAccountName 作为“R-User”,在“StartTime”上我们有最后一次访问时间
解决方案
关于您的查询的几点说明 -
- 用户适当的显式连接语法以增加查询的可读性。
- 您使用了 4 个表,但连接条件只有 2 个。所以我猜您缺少 1 个连接条件。
您可以尝试以下查询以获得所需的结果 -
Select Object1.DisplayName as 'User Name',
Object1.SamAccountName as 'R-User',
Object2.DisplayName as 'Projekt Name',
MAX(StartTime) AS Last_Access --or MIN as per your requirement
From ADObjectMemberships Member1
Join ADObjects Object1 ON Object1.ObjectSID = Member1.MemberObjectSID
Join ADObjects Object2 ON Object2.ObjectSID = Member1.ObjectSID
--tbl_command You Need a join condition for this table.
Where Object1.DisplayName NOT LIKE '%\%' and
Object1.DisplayName NOT LIKE 'APP_%' and
Object1.DisplayName NOT LIKE 'CLI_%' and
Object1.DisplayName NOT LIKE '%admin%' and
Object1.DisplayName NOT LIKE '%svc%'
Group By Object1.DisplayName,
Object1.SamAccountName,
Object2.DisplayName
Order By 'Projekt Name', 'User Name'