如何从多列中选择不同的列

发布于 2024-09-26 18:53:03 字数 804 浏览 4 评论 0原文

你好我确信有人对这种操作有疑问，但我似乎无法理解它。
我有一个像这样的表：


product_id | saved_by | deleted_by | accepted_by | published_by
-----------+----------+------------+-------------+-------------
 1         | user1    |            | user1       |
-----------+----------+------------+-------------+-------------
 2         |          | user2      |             |
-----------+----------+------------+-------------+-------------
 3         | user2    |            |             | user3
-----------+----------+------------+-------------+-------------
 4         |          |            | user1       | user4
-----------+----------+------------+-------------+-------------

我正在尝试创建一个查询，该查询将选择这些字段中的所有用户，从而明显地产生如下结果：


users
------
user1
user2
user3
user4

有指针吗？

原文

Hi
I'm sure there's query for this kind of operation, but I just can't seem to get it.
I have a table like this:


product_id | saved_by | deleted_by | accepted_by | published_by
-----------+----------+------------+-------------+-------------
 1         | user1    |            | user1       |
-----------+----------+------------+-------------+-------------
 2         |          | user2      |             |
-----------+----------+------------+-------------+-------------
 3         | user2    |            |             | user3
-----------+----------+------------+-------------+-------------
 4         |          |            | user1       | user4
-----------+----------+------------+-------------+-------------

And I'm trying to create a query that would select all the users in those fields distinctly resulting in something like this:


users
------
user1
user2
user3
user4

Any pointers?

分享到QQ

分享到微博

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

发布评论

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

莳間冲淡了誓言ζ 2024-10-03 18:53:03

首先想到的是对列进行 UNION：

  SELECT t.users
  FROM
  (
  SELECT saved_by AS users
  FROM table
  UNION 
  SELECT deleted_by
  FROM table
  UNION 
  SELECT accepted_by
  FROM table
  UNION 
  SELECT published_by
  FROM table
  ) AS t;

http://postgresql.org /docs/9.0/interactive/sql-select.html

作为旁注，如果此数据已标准化，那么按照您想要的方式获取数据会容易得多。

First thing that comes to mind is to UNION the columns:

  SELECT t.users
  FROM
  (
  SELECT saved_by AS users
  FROM table
  UNION 
  SELECT deleted_by
  FROM table
  UNION 
  SELECT accepted_by
  FROM table
  UNION 
  SELECT published_by
  FROM table
  ) AS t;

http://postgresql.org/docs/9.0/interactive/sql-select.html

As a side note if this data was normalized it would be much easier to get the data the way you want.

回复收藏 0 原文

~没有更多了~