如何根据列值选择行是重复的，并在另一列中具有不同的值

Question

为了返回行，我需要查询一个表，但是我无法正确地查询该表。这是我的桌景：

Id  Name    Date        Subject     TrackingToken   RegardingObjectId                       Type    TypeName
1   XXXX    8/26/2015   RE: XXXXXX  CRM:0030062     496BF810-4DBE-E311-9357-00505686395E    112 RE: YYYY
1   XXXX    8/27/2015   RE: XXXXXX  CRM:0030055     AA8C2F71-CDD1-E311-894A-005056863ADA    112 RE: YYYY
1   XXXX    8/28/2015   RE: XXXXXX  CRM:0030055     4DF02C89-2FBE-E311-9357-00505686395E    112 RE: YYYY
1   XXXX    8/29/2015   RE: XXXXXX  CRM:0030049     496BF810-4DBE-E311-9357-00505686395E    112 RE: YYYY
1   XXXX    8/30/2015   RE: XXXXXX  CRM:0030049     06393EF9-71CC-E311-894A-005056863ADA    112 RE: YYYY
1   XXXX    8/31/2015   RE: XXXXXX  CRM:0030047     8BE51823-52BE-E311-9357-00505686395E    112 RE: YYYY
1   XXXX    9/1/2015    RE: XXXXXX  CRM:0030003     6ABE11CA-BABF-E311-89E9-005056863ADA    112 RE: YYYY

结果集应该返回：

Id  Name    Date        Subject     TrackingToken   RegardingObjectId                       Type    TypeName
1   XXXX    8/27/2015   RE: XXXXXX  CRM:0030055     AA8C2F71-CDD1-E311-894A-005056863ADA    112 RE: YYYY
1   XXXX    8/28/2015   RE: XXXXXX  CRM:0030055     4DF02C89-2FBE-E311-9357-00505686395E    112 RE: YYYY
1   XXXX    8/29/2015   RE: XXXXXX  CRM:0030049     496BF810-4DBE-E311-9357-00505686395E    112 RE: YYYY
1   XXXX    8/30/2015   RE: XXXXXX  CRM:0030049     06393EF9-71CC-E311-894A-005056863ADA    112 RE: YYYY

换句话说:选择列TrackingToken重复的所有记录，RegardingObjectId具有不同的值。

当前查询：

select [OwnerId],[OwnerIdName],[CreatedOn],[Subject],
[TrackingToken],[RegardingObjectId],
[RegardingObjectTypeCode],[RegardingObjectIdName]

from [TableX].[dbo].[Email] a
where not exists (select [TrackingToken], [RegardingObjectId]
                  from [TableX].[dbo].[Email] b
                  where a.[TrackingToken] = b.[TrackingToken]
                    and a.[RegardingObjectId] = b.[RegardingObjectId]
                    AND RegardingObjectTypeCode = 112
                  group by [TrackingToken],[RegardingObjectId]
                  having count(*)> 1)
  and a.TrackingToken in (select TrackingToken
                          from [TableX].[dbo].[Email]
                          group by TrackingToken
                          having count(*) > 1)
  and a.RegardingObjectId is not null
  and a.RegardingObjectTypeCode = 112
  and a.TrackingToken is not null
order by a.TrackingToken desc

Answer 1

您可以使用计数的解析版本在一定范围内计数记录(在您的情况下在TrackingToken中)。然后，只有在不同RegardingObjectId的计数大于1的情况下才能保存这些记录。

select *
from
(
  select mytable.* 
    , count(distinct regardingobjectid) over (partition by trackingtoken) as cnt
  from mytable
) counted
where cnt > 1;

编辑:正如所指出的，Server并不完全支持倒计时，因此您不能在其中使用DISTINCT。因此，正常计数聚合也是如此：

select mytable.* 
from mytable
where 
(
  select count(distinct token.regardingobjectid) 
  from mytable token
  where token.trackingtoken = mytable.trackingtoken
) > 1;

或者没有关联子查询，而是派生表：

select mytable.* 
from mytable
join
(
  select trackingtoken
  from mytable
  group by trackingtoken
  having count(distinct regardingobjectid) > 1
) tokens on tokens.trackingtoken = mytable.trackingtoken;

Answer 2

您可以计算RegardingObjectId的不同数量

select TrackingToken
from [TableX].[dbo].[Email]
group by TrackingToken
having count(distinct [RegardingObjectId]) > 1

Answer 3

下面是一个有用的例子

CREATE TABLE #X(Id INT,Name NVARCHAR(8),Date DATE,Subject NVARCHAR(16),TrackingToken NVARCHAR(14),RegardingObjectId UNIQUEIDENTIFIER,Type INT,TypeName NVARCHAR(8))

INSERT INTO #X(Id,Name,Date,Subject,TrackingToken,RegardingObjectId,Type,TypeName)
VALUES
(1,'XXXX','8/26/2015','RE: XXXXXX','CRM:0030062','496BF810-4DBE-E311-9357-00505686395E',112,'RE: YYYY'),
(1,'XXXX','8/27/2015','RE: XXXXXX','CRM:0030055','AA8C2F71-CDD1-E311-894A-005056863ADA',112,'RE: YYYY'),
(1,'XXXX','8/28/2015','RE: XXXXXX','CRM:0030055','4DF02C89-2FBE-E311-9357-00505686395E',112,'RE: YYYY'),
(1,'XXXX','8/29/2015','RE: XXXXXX','CRM:0030049','496BF810-4DBE-E311-9357-00505686395E',112,'RE: YYYY'),
(1,'XXXX','8/30/2015','RE: XXXXXX','CRM:0030049','06393EF9-71CC-E311-894A-005056863ADA',112,'RE: YYYY'),
(1,'XXXX','8/31/2015','RE: XXXXXX','CRM:0030047','8BE51823-52BE-E311-9357-00505686395E',112,'RE: YYYY'),
(1,'XXXX','9/1/2015' ,'RE: XXXXXX','CRM:0030003','6ABE11CA-BABF-E311-89E9-005056863ADA',112,'RE: YYYY')

SELECT      A.*
FROM        #X AS A
INNER JOIN
    (
        SELECT      TrackingToken
        FROM        #X
        GROUP BY    TrackingToken
        HAVING      COUNT(*) > 1
    ) AS O ON O.TrackingToken = A.TrackingToken

DROP TABLE #X

您可以通过向TrackingToken列添加索引来优化这一点。