SQL查询如何获取payment表to_id、from_id对应用户名且无空值与重复行

问题根源 #

你原来的写法存在两个逻辑问题：

• 关联条件写users.id = payment.to_id AND users.id = payment.from_id，要求用户ID同时等于收款方ID和付款方ID，只有同账户转账的记录才会匹配到用户数据，所以绝大多数场景下返回NULL
• 把关联条件换成OR后，每条转账记录会分别匹配收款方、付款方两个用户，因此单条记录重复返回两次

解决方案 #

对users表做两次关联，分别用不同别名对应收款方、付款方的身份，即可在单条记录中同时拿到两个ID对应的用户名，不会出现重复行。

调整后查询语句 #

SELECT
  to_user.username AS to_username, -- 收款方用户名
  from_user.username AS from_username, -- 付款方用户名
  payment.id,
  to_id,
  from_id,
  amount,
  state,
  type,
  timedate
FROM
  payment
LEFT JOIN users AS to_user ON to_user.id = payment.to_id
LEFT JOIN users AS from_user ON from_user.id = payment.from_id
WHERE payment.to_id = 3 OR payment.from_id = 3

效果说明 #

调整后每条支付记录只会返回1行，你可以根据自己的需求取to_username或者from_username字段的值即可。如果只需要保留其中一个用户名，删掉不需要的字段即可。

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