文章/答案/技术大牛

发布

社区首页 >问答首页 >如何在string_agg中包含空字符串？

问如何在string_agg中包含空字符串？
EN

Database Administration用户

提问于 2018-05-13 10:49:50

回答 1查看 1.3K关注 0票数 0

我已经做了一个摸索学习练习，直到：

确保列出所有属性，即使有一些未知的地址信息；用空字符串替换丢失的部分。

有人能指点我怎么做吗？

SELECT D.council_name, string_agg( P.house_number || ' ' || P.street  || ' ' ||    L.locality,  ', ' ORDER BY S.contract_date) AS commercial_properties
FROM Sales S, Properties P, Districts D, Localities L
WHERE S.property_id = P.property_id
AND P.locality_id = L.locality_id
AND P.district_code = D.district_code
AND P.primary_purpose = 'COMMERCIAL'
AND S.contract_date BETWEEN '2017-01-01' AND '2017-12-31'

GROUP BY D.council_name;

但它印的是：

+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY                                                                |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

而不是：

+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY, MACWOOD RD SMITHS LAKE                                        |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

它没有印刷“麦克伍德路史密斯湖”，因为它缺少house_number。如何将缺失的部分添加到结果中？

postgresql

aggregate

回答 1

Database Administration用户

回答已采纳

发布于 2018-05-13 13:02:50

对于可能包含null的所有属性，您可以使用合并将空字符串映射为空字符串：

string_agg( COALESCE(P.house_number, '') 
         || ' ' 
         || COALESCE(P.street, '')  
         ...
) AS commercial_properties

票数 3

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/206587

复制

相似问题

问如何在string_agg中包含空字符串？
EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在string_agg中包含空字符串？EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在string_agg中包含空字符串？
EN