您能帮忙找到解决方案吗?
我有这个查询
SELECT P.id, P.url, PT.name, PT.lang
FROM cms_pages P
INNER JOIN cms_pages_translations PT
ON PT.page_id = P.id
ORDER BY P.id DESC LIMIT 10;
返回如下内容:
ID | URL | NAME | LANGUAGE
---------------------------------------------------
1 | 'hello-word' | 'Hello world' | 1
---------------------------------------------------
1 | 'hello-word' | 'Hola mundo' | 2
---------------------------------------------------
1 | 'hello-word' | 'Olá mundo' | 3
---------------------------------------------------
2 | 'now-online' | 'We're online' | 1
---------------------------------------------------
2 | 'now-online' | 'Estamos online' | 2
---------------------------------------------------
2 | 'now-online' | 'Estamos online' | 3
我如何才能实现如下所示的效果:
ID | URL | NAME_1 | NAME_2 | NAME_3
-----------------------------------------------------------------------------
1 | 'hello-word' | 'Hello world' | 'Hola mundo' | 'Olá mundo'
-----------------------------------------------------------------------------
2 | 'now-online' | 'We're online' | 'Estamos online' | 'Estamos online'
-----------------------------------------------------------------------------
最佳答案
如果您知道潜在语言的数量(这将使您知道额外的列的数量),则可以使用conditional aggregation
。这是一个基于样本数据的示例:
select id, url,
max(case when language = 1 then name end) name_1,
max(case when language = 2 then name end) name_2,
max(case when language = 3 then name end) name_3
from <yourquery>
group by id, url
如果您不知道语言的数量,则必须使用
dynamic sql
快速构建列。关于php - MySQL,将特定的行列合并到另一行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35730347/