我有一个表,有 3 列 A、B、C - 其中 A 不是主键。我们需要为每个不同的 A(按 A 分组)选择 B、C 对,并将结果附加到最终结果集的末尾。这在sql中可能吗?
A | B | C
a1| b1| c1
a1| b2| c2
a1| b3| c3
a2| b1| c2
a2| b2| c5
我需要得到
a1 | (c1,b1) ; (c2,b2);(c3;b3)
a2 | (c2,b1) ; (c5,b2)
作为末尾附加的行。
我通常通过 sqlalchemy 执行此操作,然后最终在 Python 中转换数据,有没有一种方法可以直接在 SQL 中执行此操作?
编辑并开放问题:红移中 string_agg() 的替代方案是什么(Postgres 8.0.2) - 有关上述用例的更多信息。
使用 string_agg 我得到ERROR: function string_agg(text, "unknown") does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts
Edit 2:使用自定义聚合函数添加错误
An error occurred when executing the SQL command:
CREATE FUNCTION cut_semicolon(text) RETURNS text AS $$
BEGIN
RETURN SUBSTRING($1 FROM 4)
ERROR: unterminated dollar-quoted string at or near "$$
BEGIN
RETURN SUBSTRING($1 FROM 4)"
Position: 53
CREATE FUNCTION cut_semicolon(text) RETURNS text AS $$
^
Execution time: 0.24s
(Statement 1 of 7 finished)
0 rows affected
END executed successfully
Execution time: 0.22s
(Statement 2 of 7 finished)
An error occurred when executing the SQL command:
$$ LANGUAGE 'plpgsql' IMMUTABLE
ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE 'plpgsql' IMMUTABLE"
Position: 1
$$ LANGUAGE 'plpgsql' IMMUTABLE
^
Execution time: 0.22s
(Statement 3 of 7 finished)
An error occurred when executing the SQL command:
CREATE FUNCTION concat_semicolon(text, text) RETURNS text AS $$
BEGIN
RETURN $1 || ' ; ' || $2
ERROR: unterminated dollar-quoted string at or near "$$
BEGIN
RETURN $1 || ' ; ' || $2"
Position: 62
CREATE FUNCTION concat_semicolon(text, text) RETURNS text AS $$
^
Execution time: 0.22s
(Statement 4 of 7 finished)
0 rows affected
END executed successfully
Execution time: 0.22s
(Statement 5 of 7 finished)
An error occurred when executing the SQL command:
$$ LANGUAGE 'plpgsql' IMMUTABLE
ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE 'plpgsql' IMMUTABLE"
Position: 1
$$ LANGUAGE 'plpgsql' IMMUTABLE
^
Execution time: 0.22s
(Statement 6 of 7 finished)
An error occurred when executing the SQL command:
CREATE AGGREGATE concat_semicolon(
BASETYPE=text,
SFUNC=concat_semicolon,
STYPE=text,
FINALFUNC=cut_semicolon,
INITCOND=''
)
ERROR: SQL command "CREATE AGGREGATE concat_semicolon(
BASETYPE=text,
SFUNC=concat_semicolon,
STYPE=text,
FINALFUNC=cut_semicolon,
INITCOND=''
)" not supported.
Execution time: 0.23s
(Statement 7 of 7 finished)
5 statements failed.
Script execution finished
Total script execution time: 1.55s
还查看了 Google 群组中的相关答案,&看起来像是替换分隔符“;”可能有帮助?- 虽然我不确定,哪个;在此函数定义中替换。
参考 :https://groups.google.com/forum/#!topic/sql-workbench/5LHVUXTm3BI https://groups.google.com/forum/#!topic/sql-workbench/5LHVUXTm3BI
Edit 3:也许 Redshift 不支持 create 函数本身? “错误:不支持创建函数”2013 年的帖子这样说 forums.aws.amazon.com/thread.jspa?threadID=121137
Edit 4 :
select A, concat(concat(concat(C, ',' ) , cast(B as varchar)), ',')
from my_table
group by A,B,C
-- Is it ok to group by all A,B, C - since I can't group by A alone, which removes the related "C" columns--
gives -:
a1 c1b1b2b3
a2 c2b1b2
但不是 C 的所有条目(并且带有分号)
a1 c1,b1;c2,b2;c2,b3
a2 c2,b1;c5,b2
但我想要 & 之间的逗号还需要知道 A、B、C 分组是否可以?