mysql - 如何在此联合结果中添加一列？

Question

我有这个查询(为简洁起见，我删除了一些键):

SELECT id as in_id, out_id, recipient, sender, read_flag 
  FROM received WHERE recipient=1
UNION ALL 
SELECT in_id, id AS out_id, recipient, sender, read_flag  
  FROM sent WHERE sender=1 

它结合了两个表格的结果，显示给定用户发送和接收的消息。我想做的是在结果中添加一个列/标志以区分该行属于哪个表，因此当我显示它们时，我可以显示发送或接收消息的相关图标。我将如何添加这个？

Answer 1

只需为每个查询添加一个常量列。只要两个部分相同，类型是什么都没有关系。所以你可以使用 0 和 1 或两个字符串，例如:

SELECT id as in_id, out_id, recipient, sender, read_flag , 'received' as source
  FROM received WHERE recipient=1
UNION ALL 
SELECT in_id, id AS out_id, recipient, sender, read_flag , 'sent' as source 
  FROM sent WHERE sender=1