SQL:从表中获取最新的(未)订阅操作

Question

我有下表:

ID (int)
EMAIL (varchar(50))
CAMPAIGNID (int)
isSubscribe (bit)
isActionByUser (bit)

此表存储了用户对事件的所有订阅和取消订阅操作。这些操作可以由用户自己完成 (isActionByUser = true) 或由管理台完成 (isActionByUser = false)。

我需要获取最后一个操作以确定用户是订阅还是取消订阅。但请记住，当用户执行取消订阅事件的操作时，它将优先于管理台执行的其他订阅操作。

我找到了 nice solution获取按 EMAIL 和 CAMPAIGNID 分组的最新记录。但我无法弄清楚我如何结合 isActionByUser = true 的要求，绝对优先于 isActionByUser = false 的记录。另外:当管理台执行取消订阅操作时，它将优先于具有(isSubscribe = true 和 isActionByUser)的记录。

示例数据:

ID    EMAIL    CAMPAIGNID    ISSUBSCRIBE    ISACTIONBYUSER
-----------------------------------------------------------
1     a@aa.com    1             1                0
2     b@bb.com    1             1                0
3     c@cc.com    1             1                0
4     a@aa.com    1             0                1
5     a@aa.com    1             1                0
6     c@cc.com    1             1                1
7     c@cc.com    1             0                0

预期的结果是:

ID    EMAIL    CAMPAIGNID    ISSUBSCRIBE    ISACTIONBYUSER
-----------------------------------------------------------
2     b@bb.com    1             1                0   
4     a@aa.com    1             0                1
7     c@cc.com    1             0                0

使用以下查询

select cs1.*
from 
    [TABLE] cs1 
    left join 
    [TABLE] cs2
    on 
    cs1.EM_EMAIL = cs2.EM_EMAIL
    and 
    cs1.EM_CAMPAIGNID = cs2.EM_CAMPAIGNID
    and 
    cs1.id < cs2.id
where cs2.id is null

我得到以下结果:

ID    EMAIL    CAMPAIGNID    ISSUBSCRIBE    ISACTIONBYUSER
-----------------------------------------------------------
2     b@bb.com    1             1                0
5     a@aa.com    1             1                0
7     c@cc.com    1             0                0

另一种方法:

SELECT *
FROM [TABLE] cs
WHERE id in 
  (
    SELECT top 1 id 
    FROM [TABLE] ss
    WHERE 
        cs.EMAIL = ss.EMAIL
        and 
        cs.CAMPAIGNID = ss.CAMPAIGNID 
        and ISSUBSCRIBE = (
            select top 1 min(convert(int, ISSUBSCRIBE)) 
            FROM [TABLE] sss
            WHERE 
                cs.EMAIL = sss.EMAIL
                and 
                cs.CAMPAIGNID = sss.CAMPAIGNID
            )
       and ISACTIONBYUSER= (
            select top 1 max(convert(int, ISACTIONBYUSER)) 
            FROM [TABLE] ssss
            WHERE 
                cs.EMAIL = ssss.EMAIL
                and 
                cs.CAMPAIGNID = ssss.CAMPAIGNID
            )
        )   

这将产生以下结果:

ID    EMAIL    CAMPAIGNID    ISSUBSCRIBE    ISACTIONBYUSER
-----------------------------------------------------------
2     b@bb.com    1             1                0
4     a@aa.com    1             0                1
6     c@cc.com    1             1                1

这也不正确。恐怕性能将成为这种方法的一个大问题。

那么有什么办法可以解决这个问题吗？

Answer 1

好的，试试下面的查询:

SELECT DISTINCT B.*
FROM YourTable A
OUTER APPLY (SELECT TOP 1 *
             FROM YourTable
             WHERE Email = A.Email AND CampaignId = A.CampaignId
             ORDER BY CASE WHEN ISSUBSCRIBE = 0 THEN 1 ELSE 2 END,
             CASE WHEN ISACTIONBYUSER = 1 THEN 1 ELSE 2 END,
             ID DESC) B