sql 不同，得到 2 列 [英] sql distinct, getting 2 columns

问题描述

我正在尝试为我的网站创建一个简单的消息功能，但我无法从 2 列(**from** 列和 **to** 列)

I'm trying to create a simple message feature for my website but I couldn't get distinct datas from 2 columns (**from** column and **to** column)

您在图片上看到示例数据

you see examples datas on the picture

我怎样才能得到回报1,4,23,45,345"?

how can I get the return "1,4,23,45,345"?

推荐答案

您应该合并两列，然后过滤不同的值:

You should to union both columns and then filter for distinct values:

select distinct T.from_to from
( select `from` as from_to
  from messages
  union
  select `to` as from_to
  from messages
) T

如果你真的需要逗号分隔的字符串，请使用 GROUP_CONCAT([DISTINCT] 聚合函数.

if you really need all in a comma separate string, use GROUP_CONCAT([DISTINCT] aggregation function.

已编辑:

您应该将 Gerald 的答案标记为解决方案.测试联合运算符并重读 mysql联合运算符文档后，默认情况下, mysql 过滤器对不同的值:

You should mark as solution Gerald answer. After test union operator and reread mysql union operator documentation, by default, mysql filter for distinct values:

mysql> create table ta( a int );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into ta values (1),(1),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from ta
    -> union
    -> select * from ta;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

那么，最终查询是:

  select `from` as from_to
  from messages
  union distinct
  select `to` as from_to
  from messages

请注意，distinct 不是强制性的.

Notice that distinct is not mandatory.

仅当您需要逗号分隔字符串时，才需要第一个解决方案:

Only if you need a comma sparate string the first solution is necessary:

select distinct GROUP_CONCAT( distinct T.from_to from )
( select `from` as from_to
  from messages
  union
  select `to` as from_to
  from messages
) T

这篇关于sql 不同，得到 2 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！