Mysql 输出从双行到按键分组的单个原始数据

Question

我有这个查询:

select key, propkey, propvalue 
from openidm.managedobjectproperties 
where key in (
    select key 
    from openidm.managedobjectproperties 
    where propvalue in ('97539510','97939103')) 
and propkey in ('/CRSMID','/partyID');

它基本上返回这个输出:

key     | propkey  | propvalue
------------------------------
1482195 | /CRSMID  | 814427
1482195 | /partyID | 97539510
1488492 | /CRSMID  | 1415615
1488492 | /partyID | 97939103

对于每个键 - 相同 - 我想以这种方式输出一个带有属性值的两列表(第一列/CRSMID，第二列/partyID):

CRSMID  | partyID
-------------------
814427  | 97539510
1415615 | 97939103

但我不确定这一点，因为按键分组不起作用。

Answer 1

使用条件聚合

select max(case when propkey='/CRSMID' then  propvalue end) CRSMID,
max(case when propkey='/partyID' then  propvalue end) partyID
from openidm.managedobjectproperties 
where key in (
    select key 
    from openidm.managedobjectproperties 
    where propvalue in ('97539510','97939103')) 
and propkey in ('/CRSMID','/partyID') group by key