sql - 使用 SQL Server 在 UNION 中过滤重复项

Question

我正在尝试删除因日期值不同而导致的重复条目。我尝试在 group by 中使用 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

Answer 1

您可以使用带有union 的select 作为临时表，然后从中select 并执行group by 您之前在没有 DateCreated 字段的情况下所做的。

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