如何连接表行中的所有结果？ [英] How to concatenate all results from table row?

问题描述

找不到解决方案来做类似的事情：

Can not find a solution to do something like:

SELECT CONCAT_WS(',', (
    SELECT * FROM table WHERE id = 1
))

如何在PostgreSQL中做到这一点？

How can I do that in PostgreSQL?

推荐答案

又快又脏：

SELECT t::text FROM tbl t WHERE id = 1;

• t 是表的别名，并非严格需要。您也可以使用原始表名。但是，如果您具有相同名称的列，则该列具有优先权。

  • t is an alias for the table and not strictly needed. You can use the original table name as well. But if you have a column of the same name it takes precedence.

    因此， t 代表该行表的类型，它会自动强制输出为文本表示形式。
    
    我在 make 中为其 text 内部也是如此-万一您想使用它做任何事情...

t :: text 是Postgres的SQL标准缩写 cast（t AS文本），您也可以使用。 手册中的详细信息。

    So t represents the row type of the table, which is automatically coerced to text representation on output.
    I added an explicit cast to make it text internally as well - in case you want to do anything with it ...
    t::text is Postgres short notation for the SQL standard cast (t AS text), which you can use as well. Details in the manual.

    您可能需要修剪表示行类型的（单个！）前导和尾括号：

    You may want to trim the (single!) leading and trailing parentheses that denote a row type:

    SELECT right(left(t::text, -1), -1))
    FROM   tbl AS t
    WHERE  id = 1;
    

  
  

  • 脏，因为您获得了Postgres行符号，所以分隔符恰好是您要求的逗号，但是某些值也可以转义和/或根据需要加双引号。

  • "dirty", becaue you get Postgres row notation, the separator happens to be just the comma you asked for, but some values are also escaped and / or double quoted if needed.

    这篇关于如何连接表行中的所有结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！