支持php 键值型数据库,php – 使用键值对连接MySQL表

我有一个如下所述的表结构：

persons

+----+------+

| id | name |

+----+------+

| 1 | Bart |

| 2 | Lisa |

+----+------+

keys

+----+--------+

| id | key |

+----+--------+

| 1 | gender |

| 2 | age |

+----+--------+

values

+----+-----------+--------+--------+

| id | person_id | key_id | value |

+----+-----------+--------+--------+

| 1 | 1 | 1 | male |

| 2 | 1 | 2 | 10 |

| 3 | 2 | 1 | female |

| 4 | 2 | 2 | 8 |

+----+-----------+--------+--------+

我需要得到一个像这样的表：

+-----------+------+--------+-----+

| person_id | name | gender | age |

+-----------+------+--------+-----+

| 1 | Bart | male | 10 |

| 2 | Lisa | female | 8 |

+-----------+------+--------+-----+

我可以通过使用LEFT JOIN来实现这一点,但这不能动态地工作.

我可以创建一个生成SQL的PHP​​脚本,但必须有办法让查询动态起作用.

解决方法:

条件聚合加入

SELECT p.person_id,

p.NAME,

Max(CASE WHEN key_id = 1 THEN value END) AS Gender,

Max(CASE WHEN key_id = 2 THEN value END) AS Age

FROM VALUES v

JOIN person p

ON v.person_id = p.id

GROUP BY p.person_id,

p.NAME

摆脱Keys和Values表,并在Person表中添加两个名为Age and Gender的列

标签：php,mysql,sql,key,aggregate-functions

来源： https://codeday.me/bug/20190519/1133874.html