SQL 要连接的 Sum 的等效字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15633373/
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
string equivalent of Sum to concatenate
提问by Stu Harper
I would like a query to output on 1 line the Id from the left table and the descriptions from a joined table.
我想要一个查询在 1 行输出左表中的 Id 和连接表中的描述。
Schema:
架构：
person
---------
id (int)
role
-------------
id (int)
description (varchar(100))
personrole
-------------
personid (int)
roleid (int)
Example data:
示例数据：
person
------------
id
1
2
role
------------
id description
1 user
2 admininstrator
3 tester
personrole
-------------
personid roleid
1 1
2 1
2 2
2 3
So, I'd like the output to be:
所以，我希望输出是：
PersonId Roles
1 user
2 user;administrator;tester
回答by John Woo
SELECT
p.ID PersonID,
STUFF(
(SELECT ';' + b.description
FROM personrole a
INNER JOIN role b
ON a.roleid = b.id
WHERE a.personid = p.id
FOR XML PATH (''))
, 1, 1, '') AS DescriptionList
FROM person AS p
GROUP BY p.ID
OUTPUT
输出
╔══════════╦════════════════════════════╗
║ PERSONID ║ DESCRIPTIONLIST ║
╠══════════╬════════════════════════════╣
║ 1 ║ user ║
║ 2 ║ user;admininstrator;tester ║
╚══════════╩════════════════════════════╝
回答by AlbanMar31
An other SQL example: using GROUP_CONCAT on a single table to group every first name client of a town.
另一个 SQL 示例：在单个表上使用 GROUP_CONCAT 对城镇的每个名字客户进行分组。
script Sqlite :
脚本Sqlite：
Table:
桌子：
CREATE TABLE IF NOT EXISTS 'user'(
prenom STRING,
age INTEGER,
ville STRING);
Data:
数据：
INSERT INTO 'user' ('prenom', 'age', 'ville') VALUES
('Anthony', 20, 'Toulouse'),
('Clarisse', 18, 'Paris'),
('Madeleine', 58, 'Paris'),
('Jacques', 45, 'Toulouse'),
('Henry', 26, 'Toulouse'),
('Lili', 14, 'Nice'),
('Victtheitroade', 38, 'Paris');
Normal select :
正常选择：
SELECT * FROM 'user';
OUTPUT :
输出 ：
prenom age ville
-------- -- ---------
Anthony 20 Toulouse
Clarisse 18 Paris
Madeleine 58 Paris
Jacques 45 Toulouse
Henry 26 Toulouse
Lili 14 Nice
Victtheitroade 38 Paris
All prenom group by ville :
ville 的所有 prenom 组：
SELECT ville, GROUP_CONCAT(prenom, ',') FROM user GROUP BY ville;
SELECT ville, GROUP_CONCAT(prenom, ',') FROM user GROUP BY ville;
OUTPUT :
输出 ：
ville liste
-------- ---------
Nice Lili
Paris Clarisse,Madeleine,Victtheitroade
Toulouse Anthony,Jacques,Henry
回答by Anton Shelin
SQL Server (starting with 2017) supports OOTB STRING_AGG function
SQL Server（从 2017 开始）支持 OOTB STRING_AGG 函数
select p.id, STRING_AGG(pr.description, ',') as roles
from person p
inner join personrole pr ON p.id = pr.personid
inner join roles r ON r.id = pr.roleid
group by p.id
