使用SQL Server过滤UNION中的重复项

Slinky 发表于 Dev

紧身的

我正在尝试删除由日期值不同引起的重复条目。我试图在分组依据中使用min（date），但这是不允许的

例如，当我需要的只是第一行时，返回以下两行

MasterCustomerId    NewClubKeyId    DateAssigned
000000201535        K18752          2014-08-13 20:25:18.717
000000201535        K18752          2015-01-08 00:41:03.037

这是我的查询。有任何想法吗？谢谢

SELECT  nc.CreatorMasterCustomerId MasterCustomerId,nc.NewClubKeyId,MIN(nc.DateCreated) DateAssigned
FROM NewClub nc
WHERE nc.IsActive = 1 AND nc.NewClubKeyId IS NOT NULL AND nc.DateCreated IS NOT NULL
AND nc.DateCreated >='2013-10-10'
GROUP BY nc.CreatorMasterCustomerId,nc.NewClubKeyId,nc.DateCreated

UNION

SELECT   ncb.MasterCustomerId,nc.NewClubKeyId,MIN(ncb.DateCreated) DateAssigned
FROM NewClubBuilder ncb
JOIN NewClub nc ON nc.Id = ncb.NewClubId
WHERE nc.IsActive = 1 AND nc.NewClubKeyId IS NOT NULL AND ncb.DateCreated IS NOT NULL
AND ncb.DateCreated >='2013-10-10'
GROUP BY ncb.MasterCustomerId,nc.NewClubKeyId,ncb.DateCreated

根据下面@suslov的建议，我按所述实现了查询，并且效果很好。这里是：

select 
t.MasterCustomerId,
t.NewClubKeyId,
MIN(t.DateCreated)DateAssigned
FROM
(
    SELECT DISTINCT nc.CreatorMasterCustomerId MasterCustomerId,nc.NewClubKeyId,nc.DateCreated
    FROM NewClub nc
    WHERE nc.IsActive = 1 AND nc.NewClubKeyId IS NOT NULL AND nc.DateCreated IS NOT NULL
    AND nc.DateCreated >='2013-10-10'

    UNION

    SELECT DISTINCT  ncb.MasterCustomerId,nc.NewClubKeyId,ncb.DateCreated
    FROM NewClubBuilder ncb
    JOIN NewClub nc ON nc.Id = ncb.NewClubId
    WHERE nc.IsActive = 1 AND nc.NewClubKeyId IS NOT NULL AND ncb.DateCreated IS NOT NULL
    AND ncb.DateCreated >='2013-10-10'
)t

GROUP BY t.MasterCustomerId,t.NewClubKeyId

钾盐

您可以将selectwithunion用作临时表，然后select从中使用它，然后执行group by没有DateCreated字段的操作。

select t.CreatorMasterCustomerId as MasterCustomerId
     , t..NewClubKeyId
     , min(t.DateCreated) as DateAssigned
from (<...>) t
group by t.MasterCustomerId
       , t.NewClubKeyId

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。