我正在尝试根据该列中的值将结果集中的列分为2列。因此，用户可以订阅多个项目，并且该用户可以具有2个可以接收此订阅的电子邮件地址。结果集提供了订阅列表及其订阅的电子邮件ID的对应条目。
数据库详细信息
Table 1 - user_subscriptions
user_id
email_id - 1 for email id 1 and 2 for email id 2
subscription_id
Table 2 - subscriptions
subscription_id
subscription_name
现在，无论是否通过电子邮件ID进行订阅，我都需要该用户的所有订阅。所以我得到这样的结果集
+----------------------+----------+
| subscription_name | email_id |
+----------------------+----------+
| item1 | 1 |
| item1 | 2 |
| item2 | null |
| item3 | 1 |
| item4 | null |
| item5 | 2 |
+----------------------+----------+
所以我想将上面的结果集拆分为如下所示的内容
+-------------------+---------+---------+
| subscription_name | email_1 | email_2 |
+-------------------+---------+---------+
| item1 | 1 or Y | 1 or Y |
| item2 | 0 or N | 0 |
| item3 | 1 | 0 |
| item4 | 0 | 0 |
| item5 | 0 | 1 |
+-------------------+---------+---------+
希望这个问题有意义。任何帮助，将不胜感激！
更新 - - - - - -
样本数据：
subscriptions - +-----------------+-------------------+ | subscription_id | subscription_name | +-----------------+-------------------+ | 1 | item1 | | 2 | item2 | | 3 | item3 | | 4 | item4 | | 5 | item5 | +-----------------+-------------------+
user_subscriptions
+---------+----------+-----------------+ | user_id | email_id | subscription_id | +---------+----------+-----------------+ | 101 | 1 | 1 | | 101 | 2 | 1 | | 101 | 1 | 3 | | 101 | 2 | 5 | | 102 | 1 | 1 | | 102 | 2 | 1 | +---------+----------+-----------------+
预期结果：
对于user_id = 101
+-----------------+-------------------+--------+--------+ | subscription_id | subscription_name | mail_1 | mail_2 | +-----------------+-------------------+--------+--------+ | 1 | item1 | Y | Y | | 2 | item2 | N | N | | 3 | item3 | Y | N | | 4 | item4 | N | N | | 5 | item5 | N | Y | +-----------------+-------------------+--------+--------+
SELECT
S.subscription_id,
S.subscription_name,
CASE
WHEN US1.mail_ID IS NULL THEN 'N'
ELSE 'Y'
END mail_1,
CASE
WHEN US2.mail_ID IS NULL THEN 'N'
ELSE 'Y'
END mail_2
FROM subscriptions S
LEFT JOIN user_subscriptions US1
ON S.subscription_id = US1.subscription_id
AND US1.mail_id = 1
LEFT JOIN user_subscriptions US2
ON S.subscription_id = US2.subscription_id
AND US2.mail_id = 2
WHERE us1.user_id = 5 -- or use a variable @user_ID
OR us2.user_id = 5
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句