Select
entry_id,
(case when meta_key = "name-1" then meta_value end) as `name-1`,
max(case when meta_key = "name-2" then meta_value end) as `name-2`,
max(case when meta_key = "select-1" then meta_value end) as `select-1`,
max(case when meta_key = "select-2" then meta_value end) as `select-2`,
min(case when meta_key = "text-1" then meta_value end) as `text-1`
from runningtimes
group by entry_id , "name-1", "text-1"
然后在select语句中使用where子句只从伦敦过滤数据:
Select * from runningtimes_Pivot where `select-2` = "London"
Select
entry_id,
`name-1`,
`name-2`,
`select-1`,
`select-2`,
`text-1`
from
(
Select
entry_id,
(case when meta_key = "name-1" then meta_value end) as `name-1`,
max(case when meta_key = "name-2" then meta_value end) as `name-2`,
max(case when meta_key = "select-1" then meta_value end) as `select-1`,
max(case when meta_key = "select-2" then meta_value end) as `select-2`,
min(case when meta_key = "text-1" then meta_value end) as `text-1`
from runningtimes
group by entry_id , "name-1", "text-1"
)ref1
group by `name-1`
1条答案
按热度按时间uqjltbpv1#
由于你没有发布你得到的错误或你到现在为止尝试的代码示例,我只能假设你必须面对列名中的特殊字符的问题。
我可以帮你转动table。在这种情况下,您需要在列名中使用(`)反引号。
所以pivot table代码看起来像这样:
然后在select语句中使用where子句只从伦敦过滤数据:
带有过滤器数据的代码:https://dbfiddle.uk/cOn9XHyA
根据您的评论编辑的答案: