SQL 如何获取 GROUP_BY 子句中的值列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/491566/
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
How to get list of values in GROUP_BY clause?
提问by Jagmal
If I have data like this in a table
如果我在表中有这样的数据
id data
-- ----
1 1
1 2
1 3
2 4
2 5
3 6
3 4
How do I get results like this in a query (on sybase server)?
如何在查询中（在 sybase 服务器上）获得这样的结果？
id data
-- ----
1 1, 2, 3
2 4, 5
3 6, 4
回答by lpfavreau
I know that in MySQL there is GROUP_CONCATand in Sybase I think it's LISTas stated in another answer:
我知道在 MySQL 中有GROUP_CONCAT，而在 Sybase 中我认为它是另一个答案中所述的LIST：
SELECT id, LIST(data||', ')
FROM yourtable
GROUP BY id
回答by Eduardo Molteni
In MsSQL you can use a function (Don't know if there is somenthing similar in SyBase)
在 MsSQL 中你可以使用一个函数（不知道 SyBase 中是否有类似的东西）
CREATE FUNCTION [dbo].[GetDataForID]
(
@ID int
)
RETURNS varchar(max)
AS
BEGIN
declare @output varchar(max)
select @output = COALESCE(@output + ', ', '') + data
from table
where ID = @ID
return @output
END
GO
And then:
进而：
SELECT ID, dbo.GetDataForID(ID) as Data
FROM Table
GROUP BY ID
回答by Dave Markle
You can't do this in a straight GROUP BY in plain-vanilla SQL. You have to use a cursor (or a similar construct) to concatenate the values in each group manually.
您不能在普通 SQL 中直接使用 GROUP BY 执行此操作。您必须使用游标（或类似结构）手动连接每个组中的值。
- Oracle lets you define a custom aggregator which would do this concatenation in PL/SQL.
- SQL Server lets you define a custom aggregator in .NET which would do this, as well.
- I'm not sure about what options Sybase has for defining custom aggregators, though.
- Oracle 允许您定义一个自定义聚合器，它将在 PL/SQL 中执行此连接。
- SQL Server 允许您在 .NET 中定义自定义聚合器，它也可以执行此操作。
- 不过，我不确定 Sybase 有哪些用于定义自定义聚合器的选项。
回答by Ringtail
For PostgreSQL, using a similar function string_agg.
对于 PostgreSQL，使用类似的函数string_agg。
SELECT id, string_agg(data, ',')
FROM yourtable
GROUP BY id
回答by Yan Pak
In PL/SQL you can do it by:
在 PL/SQL 中，您可以通过以下方式实现：
SELECT id, LISTAGG(data, ',') WITHIN GROUP(ORDER BY 0) "data"
FROM yourtable
GROUP BY id
回答by puppylpg
In mysql, use
在 mysql 中，使用
SELECT id, GROUP_CONCAT(data)
FROM yourtable
GROUP BY id
or use your custom separator:
或使用您的自定义分隔符：
SELECT id, GROUP_CONCAT(data SEPARATOR ', ')
FROM yourtable
GROUP BY id
see GROUP_CONCAT.
回答by eitan
In SQL Server:
在 SQL Server 中：
select distinct b.id, data=STUFF((select ',' +convert(varchar,id)
from yourtable a
where a.id=b.id
for xml path ('')),1,1,'')
from yourtable b
回答by Dan Williams
I think you're going to have to use a cursor (http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/50501;pt=50305)
我认为你将不得不使用光标（http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/50501;pt=50305）
回答by Aziz
Try this one:
试试这个：
SELECT id,
GROUP_CONCAT(data)
FROM table
GROUP BY id
回答by KDrewiske
It's been a few years since I've tried using the syntax and I no longer have access to an iAnywhere instance, but there was an aggregate function (list) to do such a task. I cannot confirm if LIST() is still supported.
自从我尝试使用该语法已经有几年了，我不再能够访问 iAnywhere 实例，但是有一个聚合函数（列表）可以完成这样的任务。我无法确认是否仍然支持 LIST()。
SELECT id,
LIST(data)
FROM table
GROUP BY id
