如何向该联合结果添加一列？

发布于 2024-09-05 06:51:50 字数 348 浏览 2 评论 0原文

我有这个查询（为了简洁起见，我删除了一些键）：

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

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

原文

I have this query (which I removed some keys from for brevity's sake):

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

Which combines the results from two tables showing messages sent and received by a given user. What I'd like to do is add a column/flag to the result to distinguish which table the row belongs to so when I display them I can show a relevant icon for sent or received messages. How would I add this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝咒 2024-09-12 06:51:50

只需向每个查询添加一个常量列即可。只要两个部分相同，类型是什么并不重要。因此您可以使用 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

Just add a constant column to each query. It doesn't matter what the type is as long as it's the same in both parts. So you could use 0 and 1 or two strings, for example:

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

回复收藏 0 原文

埖埖迣鎅 2024-09-12 06:51:50

只需在每个选择中添加具有硬编码值的列即可：

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

Just add the column in each select with a hard coded value:

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

回复收藏 0 原文

南七夏 2024-09-12 06:51:50

这将做到这一点：

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

This will do it:

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

回复收藏 0 原文

~没有更多了~