mysql - 将 sql master 链接到属性表，然后将 prop 值表示为列

Question

我有一个主对象和属性表，对象可能有 0...n 个“随机”属性。我想将大师和某些 Prop 链接为一个专栏。

你会怎么做？这是我当前的解决方案，它可以工作，但将来可能会出现性能问题(？)。

Select v.code 
 ,(select value from properties where server_id=v.server_id and code=v.code and name='propx') as 'propx'
 ,(select value from properties where server_id=v.server_id and code=v.code and name='propy') as 'propy'
From masterobj v
Where 
    v.server_id='001'
    and (v.starttime between '2019-11-01 00:00:00' and '2019-11-15 23:59:59')
Order by v.starttime asc;

数据 View 的模型

masterobj table
001  abc001  2019-11-01 13:01:00
001  abc002  2019-11-01 13:02:00
001  abc003  2019-11-01 13:03:00
001  abc004  2019-11-01 13:04:00
001  abc005  2019-11-01 13:05:00
001  abc901  2019-05-14 09:01:00
properties table
001  abc001  propx  128
001  abc001  propy  64
001  abc001  temp   1
001  abc002  temp   1
001  abc003  temp   0
001  abc003  propx  32
001  abc004  propy  20
Query Results
code    propx propy
abc001  128   64
abc002  
abc003  32
abc004        20
abc005

Answer 1

正如建议的那样，这是一个对同一个表具有多个左联接的查询。

Select v.code, p1.value as 'propx', p2.value as 'propy'
From masterobj v
  Left Join properties p1 On (v.server_id=p1.server_id and v.code=p1.code and p1.name='propx')
  Left Join properties p2 On (v.server_id=p2.server_id and v.code=p2.code and p2.name='propy')
Where 
  v.server_id='001'
  and (v.starttime between '2019-11-01 00:00:00' and '2019-11-15 23:59:59')
Order by v.starttime asc;