我以这样的方式编写了以下查询,该查询返回了我需要的数据:
SELECT SLCA.CustomerAccountNumber, SLCD.DefaultEmail, SOR.DocumentDate, SLST.*
FROM SLCustomerAccount AS SLCA
INNER JOIN SOPOrderReturn AS SOR ON SOR.CustomerID = SLCA.SLCustomerAccountID
INNER JOIN SLCustomerContactDefaultsVw AS SLCD ON SLCD.SLCustomerAccountID = SLCA.SLCustomerAccountID
LEFT OUTER JOIN MMS182SLStatus AS SLST ON SLST.Customer = SLCA.SLCustomerAccountID
WHERE SLCD.IsDefaultRole = 1 AND SLCD.DefaultEmail IS NOT NULL AND SLCD.DefaultEmail <> ''
AND SLCD.DefaultEmail = '[email protected]'
AND SLST.[Status] IS NULL
ORDER BY SOR.DocumentDate DESC
唯一的问题是,我在where过滤器中使用的这封电子邮件会产生多个结果(尽管正确):
如何使用group by
删除重复的行,所以对于最新的DocumentDate我只有一条记录?
我知道我可以做到,TOP 1
但这不是我所需要的。因为,我现在通过将范围限定为一封重复的特定电子邮件来测试查询。稍后,我需要在没有该行的所有记录上运行此查询AND SLCD.DefaultEmail = '[email protected]'
-这样您就可以了解为什么我不能使用TOP 1
。
当我尝试这样使用时group by
:
SELECT MAX(SLCA.CustomerAccountNumber), MAX(SLCD.DefaultEmail), MAX(SOR.DocumentDate), MAX(SLST.MMS182SLStatus), MAX(SLST.Customer), MAX(SLST.[Status])
FROM SLCustomerAccount AS SLCA
INNER JOIN SOPOrderReturn AS SOR ON SOR.CustomerID = SLCA.SLCustomerAccountID
INNER JOIN SLCustomerContactDefaultsVw AS SLCD ON SLCD.SLCustomerAccountID = SLCA.SLCustomerAccountID
LEFT OUTER JOIN MMS182SLStatus AS SLST ON SLST.Customer = SLCA.SLCustomerAccountID
WHERE SLCD.IsDefaultRole = 1 AND SLCD.DefaultEmail IS NOT NULL AND SLCD.DefaultEmail <> ''
AND SLCD.DefaultEmail = '[email protected]'
AND SLST.[Status] IS NULL
GROUP BY SLCD.DefaultEmail
ORDER BY MAX(SOR.DocumentDate) DESC
它返回错误的结果:
我期望它返回此记录:
有什么想法我可能在这里做错了吗?
用途row_number()
:
with q as (<your query here without the `order by`>)
select q.*
from (select q.*,
row_number() over (partition by DefaultEmail order by documentdate desc) as seqnum
from q
) q
where seqnum = 1;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句