带IF条件的SELECT AS查询：如何从MSG表获取买卖双方ID

根据你给出的规则，我们可以用CASE条件判断语句实现需求，直接从MSG表中计算出对应的买家ID：

SELECT
    ITEM_ID,
    SELLER_ID AS SELLER,
    CASE
        WHEN SELLER_ID = MSG_SENDER_ID THEN MSG_RECEIVER_ID
        WHEN SELLER_ID = MSG_RECEIVER_ID THEN MSG_SENDER_ID
    END AS BUYER
FROM MSG;

语句说明 #

• CASE语句会逐条判断记录：
  • 当SELLER_ID等于消息发送方MSG_SENDER_ID时，买家为消息接收方MSG_RECEIVER_ID
  • 当SELLER_ID等于消息接收方MSG_RECEIVER_ID时，买家为消息发送方MSG_SENDER_ID
• 针对你提供的示例数据，执行后输出结果如下：

如果需要去重，只保留每个商品对应的唯一买卖双方组合，可添加DISTINCT关键字：

SELECT DISTINCT
    ITEM_ID,
    SELLER_ID AS SELLER,
    CASE
        WHEN SELLER_ID = MSG_SENDER_ID THEN MSG_RECEIVER_ID
        WHEN SELLER_ID = MSG_RECEIVER_ID THEN MSG_SENDER_ID
    END AS BUYER
FROM MSG;

内容的提问来源于stack exchange，提问作者topcan5