当两个列连接时,如何在字符串agg中添加顺序。 [英] How to add order by in string agg, when two columns are concatenated.
本文介绍了当两个列连接时,如何在字符串agg中添加顺序。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
SELECT string_agg(distinct a ||' - '|| b,','ORDER BY a,b)FROM table;
上面的sql给出错误
错误: aggregate DISTINCT , ORDER BY 表达式必须在参数列表中出现
如何解决问题。
我尝试过:
我试过上面的问题窗口中提到的sql。
解决方案
出现这种错误,我通常会使用一个子查询做我的脏工作。在 SQL Fiddle [^] [ ^ ],我想出了下面的SQL脚本。
CREATE TABLE 项目
(
itemid整数,
项目字符变化( 50 )
);
INSERT INTO item(itemid,item) VALUES ( 1 ,' 比萨饼'跨度>);
INSERT INTO 项目(itemid,item) VALUES ( 2 ,' cake'< /跨度>);
INSERT INTO 项目(itemid,item) VALUES ( 3 ,' sandwich'< /跨度>);
选择 string_agg(itemid || ' - ' || item,' |')< span class =code-keyword> FROM (( SELECT DISTINCT 项,itemid < span class =code-keyword> FROM item ORDER BY itemid))t;
SELECT string_agg( distinct a || '-' || b , ',' ORDER BY a,b) FROM table;
The above sql giving error
ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
How to resolve the issue.
What I have tried:
I have tried with above sql which mentioned in above problem window.
解决方案
With this kind of error, I typically use a subquery to do my dirty work. After playing around with this for a few minutes on SQL Fiddle[^][^], I came up with the SQL script below.
CREATE TABLE item ( itemid integer, item character varying(50) ); INSERT INTO item(itemid, item)VALUES (1, 'pizza'); INSERT INTO item(itemid, item)VALUES (2, 'cake'); INSERT INTO item(itemid, item)VALUES (3, 'sandwich'); select string_agg(itemid || '-' || item,' | ') FROM ((SELECT DISTINCT item, itemid FROM item ORDER BY itemid)) t;
这篇关于当两个列连接时,如何在字符串agg中添加顺序。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文