在 Oracle 中添加逗号 (,)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4157295/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
add a comma (,) in Oracle
提问by joe
Given this query:
鉴于此查询：
select distinct subject_key
from mytable
Result:
结果：
subject_key
-----------
90896959
90895823
90690171
90669265
90671321
How do i write a query in Oracle (using Aqua Data Studio backend Oracle 8i) result:
我如何在 Oracle 中编写查询（使用 Aqua Data Studio 后端 Oracle 8i）结果：
subject_key
-----------
90896959,
90895823,
90690171,
90669265,
90671321
THANKS ALL! Should I wish to change the output across instead of down like below. How do I write it, same platform. Thanks.
谢谢大家！我是否应该像下面那样改变输出而不是向下。我怎么写，同一个平台。谢谢。
subject_key
90896959, 90895823, 90690171, 90669265, 90671321
回答by OMG Ponies
Oracle doesn't have a function like MySQL's GROUP_CONCAT, which is exactly the functionality you're asking for. Various options for such string aggregation are provided on this page- one is to use a custom function:
Oracle 没有像 MySQL 的 GROUP_CONCAT 这样的功能，这正是您要求的功能。 此页面上提供了用于此类字符串聚合的各种选项- 一个是使用自定义函数：
CREATE OR REPLACE FUNCTION get_subjectkey (IN_PK IN MYTABLE.PRIMARY_KEY%TYPE)
RETURN VARCHAR2
IS
l_text VARCHAR2(32767) := NULL;
BEGIN
FOR cur_rec IN (SELECT subject_key
FROM MYTABLE
WHERE primary_key = IN_PK) LOOP
l_text := l_text || ',' || cur_rec.ename;
END LOOP;
RETURN LTRIM(l_text, ',');
END;
Then you'd use it like:
然后你会像这样使用它：
SELECT get_subjectkey(?) AS subject_key
FROM DUAL
...replacing the "?" with the primary key value.
...替换“？” 与主键值。
Previously
之前
Assuming you just want to add a comma to the end of the column value, use:
假设您只想在列值的末尾添加一个逗号，请使用：
SELECT DISTINCT TO_CHAR(subject_key) || ','
FROM MYTABLE
The double pipe -- "||" -- is the Oracle [,PostgreSQL and now ANSI] means of concatenating strings in SQL. I used TO_CHAR to explicitly convert the data type, but you could use:
双管——“||” -- 是 Oracle [,PostgreSQL and now ANSI] 在 SQL 中连接字符串的方法。我使用 TO_CHAR 显式转换数据类型，但您可以使用：
SELECT DISTINCT subject_key || ','
FROM MYTABLE
...if that's not necessary.
……如果没有必要的话。
回答by Boo
most likely: SELECT subject_key + ',' AS subject_key FROM mytable
最有可能：SELECT subject_key + ',' AS subject_key FROM mytable
At least that's how in T-SQL. PL-SQL may be slightly different.
至少在 T-SQL 中是这样。PL-SQL 可能略有不同。
