SQL 语法:将多列连接成一列 [英] SQL syntax: Concatenating multiple columns into one
问题描述
我使用的是 PostgreSQL 9.2.我需要从这个名为 house 的表中复制地址(它们被分成多列):
I am using PostgreSQL 9.2. I need to copy the addresses (which are split across multiple columns) from this table entitled houses:
house_id | unit_number| street_number | street| street_extension| suburb | postcode | state | sale_price | sale_date |
----------+------------+-------+-------+-------+-----------------+--------+----------+-------+------------+-----------+
1 | 2 | 17 | Rose | Av | Rye | 3099 | VIC | 240000 | 2003-12-02|
2 | | 3 | Jack | St | Rye | 3099 | VIC | 260000 | 2003-10-02|
进入该表中名为 address_list 的单列:
into a single column in this table entitled address_list:
house_id | address | formatted_address | lat | lon | wkb_geometry | is_processed
----------+------------------------------------------+-------------------+-----+-----+--------------+--------------
| | | | | |
| | | | | |
我的语法是
INSERT INTO address_list (house_id, address)
SELECT house_id, unit_number || '/' || street_number || ' ' || street || ' ' || street_extension || ', ' || suburb || ', ' || state || ' ' || postcode
FROM houses;
由于源表的 unit_number 字段中有一些空条目,我的语法不起作用.
My syntax does not work because of some null entries in the unit_number field of the source table.
有没有办法复制 unit_number 加上\"(如果 NOT NULL),而忽略 unit_number 字段和\"IF NULL?
Is there a way of copying the unit_number plus a "\" if NOT NULL, and ignoring the unit_number field and the "\" IF NULL?
我花了几个小时寻找解决方案,但运气不佳,因此我将非常感谢您的帮助.
I have spent several hours searching for a solution without luck so I would be extremely grateful for any assistance.
推荐答案
使用format
函数
insert into address_list (house_id, address)
select
house_id,
format(
'%s%s %s %s, %s, %s %s',
unit_number || '\',
street_number,
street, street_extension, suburb, state, postcode
)
from houses;
%s
标记参数位置.
http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-OTHER
这篇关于SQL 语法:将多列连接成一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!