mysql - 使用键和值连接同一个表以获取一行数据

Question

这是我的表结构，

我想将同一个表与其键值结合起来，例如 prop_key ='color'

id    journal_id     property     prop_key       old_value                        value
405   252             attr         color         1                                 0
372   252             attr       updated_at      2017-03-18 03:43:34 UTC     2017-03-18 03:43:34 UTC
402   252             attr       sprint_id       5                                 0

我想要这种形式的结果。

id           color       sprint_id           start_date
372           2           5               2017-03-18 03:43:34 UTC  

我尝试以下操作

select t.id,
       max(case when a.prop_key='color' then a.value end) attr_val1,
       max(case when a.prop_key='sprint_id' then a.value end) attr_val2,
       max(case when a.prop_key='updated_at' then a.value end) attr_val3
from journal_details t
left join journal_details a on t.id = a.id
where t.journal_id=242 
group by t.id

Select 
    journal_details.id,  
    a1.value as color,
    a2.value as sprint_id,
    a3.value as start_date
from journal_details
    left join (select * from journal_details where prop_key='color') a1 on journal_details.id=a1.id
    left join (select * from journal_details where prop_key='sprint_id') a2 on journal_details.id=a2.id
    left join (select * from journal_details where prop_key='start_date') a3 on journal_details.id=a3.id
    where journal_details.journal_id=242

但是我没有得到满意的结果。

Answer 1

您不需要自加入。

试试这个:

select min(id),
    max(case when prop_key = 'color' then value end) attr_val1,
    max(case when prop_key = 'sprint_id' then value end) attr_val2,
    max(case when prop_key = 'updated_at' then value end) attr_val3
from journal_details
where journal_id = 242