mysql - mysql的多个案例值

sqlfiddle

SELECT id,
(
CASE
    WHEN keyName="disable_id" THEN "disableId"

END ) AS name1,

(CASE
    WHEN keyName="disable_id" THEN keyValue

END )
AS value1

FROM  mytbl

UNION

SELECT id,
(
CASE
    WHEN keyName="disable_id" THEN "is_disabled"
END ) AS name1,

(CASE
    WHEN keyName="disable_id" THEN "yes"
END )
AS value1

FROM  mytbl

;

我需要将单个键转换为多个键值。
有没有比这更好的方法了？

感谢您的建议。

最佳答案

使用IF或GROUP_CONCAT尝试使用更紧凑的表格：

SELECT id,
     max(if(`keyName`='disable_id',`keyValue`,null)) disable_id,
     max(if(`keyName`='disable_xx',`keyValue`,null)) disable_xx,
     max(if(`keyName`='disable_yy',`keyValue`,null)) disable_yy,
     max(if(`keyName`='disable_zz',`keyValue`,null)) disable_zz
FROM mytbl
GROUP BY id
;

SELECT id,
       GROUP_CONCAT( `keyName`,'=',`keyValue`) as val
FROM mytbl
GROUP BY id
;

演示：http://sqlfiddle.com/#!2/d7a27/11