这是我的表结构,
我想将同一个表与其键值组合在一起,例如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
但我没有得到令人满意的结果。
您不需要自我加入。
试试这个:
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