您好!
我有以下三张表(功能、人员和数据(
表特点
---------------------------
| featureId | featureName |
---------------------------
| 1 | Weight |
| 2 | Age |
| 3 | Height |
---------------------------
表人员
---------------------------
| personId | personName |
---------------------------
| 1 | John |
| 2 | Doe |
| 3 | Mark |
---------------------------
表数据(pId=人员IDfId=featureId(
--------------------------
| id | pId | fId | value |
--------------------------
| 1 | 1 | 1 | 80 |
| 2 | 1 | 2 | 28 |
| 3 | 1 | 3 | 175 |
| 4 | 2 | 1 | 70 |
| 5 | 2 | 2 | 22 |
| 6 | 2 | 3 | 168 |
| 7 | 3 | 1 | 100 |
| 8 | 3 | 2 | 44 |
| 9 | 3 | 3 | 180 |
--------------------------
我知道第三张桌子是一场噩梦,但这就是我所拥有的。是否可以构建一个返回(包含PHP中使用的标题(的查询
--------------------------------
| Name | Weight | Age | Height |
--------------------------------
| John | 80 | 28 | 175 |
| Doe | 70 | 22 | 168 |
| Mark | 100 | 44 | 180 |
--------------------------------
看起来像是三个内部连接,但标题让它更复杂,我无法理解谢谢你的帮助!
您可以在MAX
函数中连接表和CASE
SELECT
p.personName
,MAX(CASE WHEN f.featureName='Weight' THEN d.value END) Weight
,MAX(CASE WHEN f.featureName='Age' THEN d.value END) Age
,MAX(CASE WHEN f.featureName='Height' THEN d.value END) Height
FROM people p
LEFT JOIN data d ON(p.personId=d.pId)
LEFT JOIN features f ON(f.featureId=d.fId)
GROUP BY d.pId