sqlite - 尝试更新单行中的多个字段，但创建重复行

Question

编辑： Salesforce Marketing Cloud 使用一组 SQL Server。

我在 Salesforce Marketing Cloud 中使用 SQL 查询，这意味着我仅限于执行“选择”语句来覆盖表 [Distribution]。我正在尝试执行以下操作：

select * from [Subscribers] A
inner join [Master Table] B on A.MemberID = B.MemberID

这部分按预期工作。

接下来我有两个字段，Reward1 和 Reward2，每个字段对应于 [Offers] 中的特定 RewardCode。所以我想要做的（我认为）应该是在 MemberID 上与 [Subscribers] 和 [Offers] 进行左连接，其中 RewardCode = 111111 和与 [Subscribers] 和 [Offers] 的左连接，其中 RewardCode = 222222。但是这是我似乎在创建重复记录的地方。

架构示例可能如下所示：

主成员表

memberID | email | fname | lname
1111 | [e-mail] | One | Name
2222 | [e-mail] | Two | Name
3333 | [e-mail] | Three | Name
4444 | [e-mail] | Four | Name

订户

memberID | status | subscriberkey
1111 | active | [e-mail]
2222 | active | [e-mail]
3333 | active | [e-mail]
4444 | active | [e-mail]

主奖励表

memberID | rewardcode
1111 | 111111
1111 | 111111
1111 | 111111
2222 | 111111
2222 | 222222
2222 | 222222
3333 | 222222
3333 | 111111
4444 | 222222
4444 | 333333
4444 | 222222

理想情况下，结果表将类似于：

[distribution]
subscriberkey | memberId | status| reward1 | reward2
[e-mail]| 1111 | active | TRUE | FALSE
[e-mail]| 2222 | active TRUE | TRUE
[e-mail]| 3333 | active TRUE | TRUE
[e-mail]| 4444 | active FALSE | TRUE

完整查询：

SELECT A.SubscriberKey AS SubscriberKey, A.EmailAddress AS EmailAddress,
A.MemberID AS MemberId, A.Status AS Status,
A.Decile AS Decile, A.Segment AS Segment, M.Firstname AS FirstName,
M.LastName AS LastName, M.Points AS PointsAvailable,
M.Experience AS Experience,
    CASE
        WHEN M.[Preferred Delivery Method for Offers] = 2 THEN 'SMS'
        ELSE 'Email'
    END AS PreferredDeliveryOffers,
    CASE
        WHEN M.[Preferred Delivery Method for Non-Offers] = 2 THEN 'SMS'
        ELSE 'Email'
    END AS PreferredDeliveryNonOffers,
    CASE
        WHEN R.MemberRewardBarcode IS NOT NULL AND
        R.[Member ID] = M.MemberId AND
        R.MemberRewardBarcode = 1111111 THEN 'Reward15'
        ELSE ''
    END AS Reward15,
    CASE
        WHEN B.MemberRewardBarcode IS NOT NULL AND
        B.[Member ID] = M.MemberId AND
        B.MemberRewardBarcode = 2222222 THEN 'RewardBDay'
        ELSE ''
    END AS RewardBDay
FROM Subscribers A
INNER JOIN [Master Member Table] M
    ON A.MemberID = M.MemberId
LEFT JOIN (
    SELECT DISTINCT MemberRewardBarcode, [Member ID]
    FROM [master rewards table] R
    WHERE R.MemberRewardBarcode = 1111111 AND
    R.CancelTimestamp IS NULL AND
    R.ExpireTimestamp IS NULL AND
    R.RedeemTimestamp IS NULL
  ) R ON A.MemberID = R.[Member ID]
LEFT JOIN (
    SELECT DISTINCT MemberRewardBarcode, [Member ID]
    FROM [master rewards table] R
    WHERE R.MemberRewardBarcode = 2222222 AND
    R.CancelTimestamp IS NULL AND
    R.ExpireTimestamp IS NULL AND
    R.RedeemTimestamp IS NULL
    GROUP BY MemberRewardBarcode, [Member ID]
  ) B ON A.MemberID = R.[Member ID]

标签： sqlite

Answer 1

您应该能够使用条件聚合来解决这个问题：

SELECT 
    s.subscriberkey,
    s.memberID,
    s.status,
    MAX(CASE WHEN r.rewardcode = 111111 THEN 1 END) reward1,
    MAX(CASE WHEN r.rewardcode = 222222 THEN 1 END) reward2
FROM 
    subscribers s
    LEFT JOIN reward r ON r.memberID = s.memberID
GROUP BY 
    s.subscriberkey,
    s.memberID,
    s.status

此查询使用非常常见的 SQL 语法（聚合和CASE），据我在您提供的文档中所见，Salesforce Marketing Cloud 应该支持这种语法。

此SQLServer 2017 演示返回：

订户密钥 | 会员ID | 状态 | 奖励1 | 奖励2
 :------------- | --------: | :----- | ------: | ------:
 [e-mail] | 1111 | 活跃 | 1 | 无效的
 [e-mail] | 第2222章 活跃 | 1 | 1
 [e-mail] | 第3333章 活跃 | 1 | 1
 [e-mail] | 4444 | 活跃 | 空 | 无效的