正在尝试更新单行中的多个字段，但却创建了重复的行

Question

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

我在Salesforce Marketing Cloud中使用SQL查询，这意味着我只能执行"Select"语句来覆盖表[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]和[Ooffers]进行左联合，其中Reward Code=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]

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应该支持这种语法。

此SQLServer2017演示返回：

subscriberkey|memberID|status|reward1|reward2：---------------|-------：|:-----|-------:|[e-mail]|111|active|1|[e-mail]|2222|活动|1|[e-mail]|33333|活动|1|[e-mail]|4444|active|null|null