mysql 分组横向显示_MySQL：对列上的元素进行分组和展平

如果我有一个观点：

Movie Genre Actor

-------------------------------------------

Ocean's Twelve Crime George Clooney

Ocean's Twelve Crime Julia Roberts

Ocean's Twelve Crime Brad Pitt

Forrest Gump Drama Tom Hanks

我将如何按照电影标题进行分组,但是如此平整其他列：

Movie Genre Actor

-------------------------------------------

Ocean's Twelve Crime George Clooney, Julia Roberts, Brad Pitt

Forrest Gump Drama Tom Hanks

请注意,如果元素是等效的,则不会重复(例如Crime)

解决方法:

MySQL的

使用GROUP_CONCAT()函数：

SELECT movie, Genre, GROUP_CONCAT(Actor) AS Actor

FROM tableA

GROUP BY movie, Genre

SQL SERVER

SELECT A.movie, A.Genre, MAX(STUFF(B.ActorNames, 1, 1, '')) AS Actor

FROM tableA A

CROSS APPLY(SELECT ' ' + Actor + ',' FROM tableA B

WHERE A.movie = B.movie AND A.Genre = B.Genre

FOR XML PATH('')

) AS B (ActorNames)

GROUP BY A.movie, A.Genre

标签：sql,mysql,group-by,select,group-concat

来源： https://codeday.me/bug/20190825/1717188.html