SQL - 使用 GROUP BY 时以逗号分隔的多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/234532/
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
SQL - Multiple Values comma separated when using GROUP BY
提问by Roy Rico
I have data that looks like
我有看起来像的数据
CUSTOMER, CUSTOMER_ID, PRODUCT ABC INC 1 XYX ABC INC 1 ZZZ DEF CO 2 XYX DEF CO 2 ZZZ DEF CO 2 WWW GHI LLC 3 ZYX
CUSTOMER, CUSTOMER_ID, PRODUCT ABC INC 1 XYX ABC INC 1 ZZZ DEF CO 2 XYX DEF CO 2 ZZZ DEF CO 2 WWW GHI LLC 3 ZYX
I'd like to write a query that'd make the data look like this:
我想编写一个查询，使数据看起来像这样：
CUSTOMER, CUSTOMER_ID, PRODUCTS ABC INC 1 XYX, ZZZ DEF CO 2 XYX, ZZZ, WWW GHI LLC 3 ZYX
CUSTOMER, CUSTOMER_ID, PRODUCTS ABC INC 1 XYX, ZZZ DEF CO 2 XYX, ZZZ, WWW GHI LLC 3 ZYX
Using Oracle 10g if helps. I saw something that would work using MYSQL, but I need a plain SQL or ORACLE equivalent. I've also seen examples of stored procs that could be made, however, I cannot use a stored proc with the product i'm using.
如果有帮助，请使用 Oracle 10g。我看到一些可以使用 MYSQL 工作的东西，但我需要一个普通的 SQL 或 ORACLE 等价物。我还看到了可以制作的存储过程的示例，但是，我无法将存储过程与我正在使用的产品一起使用。
Here's how'd it work in MySQL if I were using it
如果我使用它，它是如何在 MySQL 中工作的
SELECT CUSTOMER,
CUSTOMER_ID,
GROUP_CONCAT( PRODUCT )
FROM MAGIC_TABLE
GROUP BY CUSTOMER, CUSTOMER_ID
Thank you.
谢谢你。
采纳答案by ConcernedOfTunbridgeWells
回答by ScrappyDev
I think LISTAGG is the best aggregate group by function to use in this situation:
我认为 LISTAGG 是在这种情况下按功能使用的最佳聚合组：
SELECT CUSTOMER, CUSTOMER_ID,
LISTAGG(PRODUCT, ', ') WITHIN GROUP (ORDER BY PRODUCT)
FROM SOME_TABLE
GROUP BY CUSTOMER, CUSTOMER_ID
ORDER BY 1, 2
回答by Chris Gurley
The oracle user function 'wm_concat' works the same way as LISTAGG except you cannot specify a delimiter ',' by default or a sort order. It is however compatible with 10g.
oracle 用户函数 'wm_concat' 的工作方式与 LISTAGG 相同，除了您不能指定默认分隔符 ',' 或排序顺序。但是它与 10g 兼容。
回答by Roy Rico
Thanks Nigel,
谢谢奈杰尔，
My SQL is not as elegant as could be, but I needed a solution that required SQL only, not PLSQL or TSQL, so it ended up looking like this:
我的 SQL 不够优雅，但我需要一个只需要 SQL 而不是 PLSQL 或 TSQL 的解决方案，所以它最终看起来像这样：
SELECT CUSTOMER, CUSTOMER_ID, COUNT(PRODUCT) PROD_COUNT,
RTRIM(
XMLAGG( XMLELEMENT (C, PRODUCT || ',') ORDER BY PRODUCT
).EXTRACT ('//text()'), ','
) AS PRODUCTS FROM (
SELECT DISTINCT CUSTOMER, CUSTOMER_ID, PRODUCT
FROM MAGIC_TABLE
) GROUP BY CUSTOMER, CUSTOMER_ID ORDER BY 1 , 2
Still not exactly sure what the XML functions do exactly, but I'll dig in when the need arrises.
仍然不完全确定 XML 函数究竟做了什么，但我会在需要时深入研究。
