Sql Server 按多列分组，仅在一列上唯一

发布于 2024-11-09 01:59:20 字数 410 浏览 1 评论 0原文

我正在构建一个发送电子邮件的系统，我需要进入 [User] 表来检索要发送的电子邮件，以及用于取消订阅目的的 GUID（唯一标识符类型）。

它适用于电子商务解决方案，因此由于匿名用户和登录用户可以具有相同的电子邮件地址，因此会创建重复的电子邮件条目，这些条目在查询中需要不同。

我无法找到检索电子邮件和指南的解决方案，但只能在电子邮件上区分。这是我到目前为止的查询。

SELECT Email, User_GUID
FROM [User]
WHERE 
    IsActive = 1 AND 
    IsEmailValid = 1 AND
    IsNotActiveBecauseUnsubscribed = 0 AND
    Subscribed = 1
GROUP BY Email, User_GUID

原文

I am building a system to send emails and I am needing to go into the [User] table to retrieve the email to send to, as well as a GUID(uniqueidentifier type) which will be used for unsubscribe purposes.

It is for an ecommerce solution so because anonymous and logged in users can have the same email addresses it creates duplicate entries of emails that need to be distinct in the query.

I am having trouble finding a solution to retrieve the email and the guid but only be distinct on the email. Here is my query so far.

SELECT Email, User_GUID
FROM [User]
WHERE 
    IsActive = 1 AND 
    IsEmailValid = 1 AND
    IsNotActiveBecauseUnsubscribed = 0 AND
    Subscribed = 1
GROUP BY Email, User_GUID

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你曾走过我的故事 2024-11-16 01:59:20

with cte
as
(
    select *, row_number() over (partition by Email order by User_GUID) RowNumber
    from [User]
    where 
        IsActive = 1 and 
        IsEmailValid = 1 and 
        IsNotActiveBecauseUnsubscribed = 0 and 
        Subscribed = 1
)
select Email, User_GUID
from cte
where RowNumber = 1

或者

select Email, User_GUID
from
(
    select *, row_number() over (partition by Email order by User_GUID) RowNumber
    from [User]
    where 
        IsActive = 1 and 
        IsEmailValid = 1 and 
        IsNotActiveBecauseUnsubscribed = 0 and 
        Subscribed = 1
) tt
where RowNumber = 1

with cte
as
(
    select *, row_number() over (partition by Email order by User_GUID) RowNumber
    from [User]
    where 
        IsActive = 1 and 
        IsEmailValid = 1 and 
        IsNotActiveBecauseUnsubscribed = 0 and 
        Subscribed = 1
)
select Email, User_GUID
from cte
where RowNumber = 1

select Email, User_GUID
from
(
    select *, row_number() over (partition by Email order by User_GUID) RowNumber
    from [User]
    where 
        IsActive = 1 and 
        IsEmailValid = 1 and 
        IsNotActiveBecauseUnsubscribed = 0 and 
        Subscribed = 1
) tt
where RowNumber = 1

回复收藏 0 原文

善良天后 2024-11-16 01:59:20

你可以做

SELECT Distinct Email, User_GUID
FROM [User]
WHERE IsActive = 1 AND IsEmailValid = 1 And IsNotActiveBecauseUnsubscribed = 0 AND Subscribed = 1
GROUP BY Email, User_GUID

You could do

SELECT Distinct Email, User_GUID
FROM [User]
WHERE IsActive = 1 AND IsEmailValid = 1 And IsNotActiveBecauseUnsubscribed = 0 AND Subscribed = 1
GROUP BY Email, User_GUID

回复收藏 0 原文

~没有更多了~