我的 MySQL 数据库中有三个表-company和：extra_fieldsextra_fields_info
mysql> describe company;
+-----------------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+------------------+------+-----+---------+-------+
| company_id | varchar(64) | NO | PRI | NULL | |
| company_name | varchar(80) | YES | | NULL | |
| other_stuff | varchar(1) | YES | | n | |
+-----------------------------+------------------+------+-----+---------+-------+
mysql> describe extra_fields;
+-----------------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+------------------+------+-----+---------+-------+
| extra_field_id | varchar(64) | NO | PRI | NULL | |
| field_name | varchar(80) | YES | | NULL | |
| field_required | varchar(1) | YES | | n | |
| field_comment | varchar(255) | YES | | NULL | |
+-----------------------------+------------------+------+-----+---------+-------+
mysql> describe extra_fields_info;
+--------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-------------+------+-----+---------+-------+
| extra_info_id | varchar(64) | NO | PRI | NULL | |
| company_id | varchar(64) | YES | MUL | NULL | |
| extra_field_id | varchar(64) | YES | | NULL | |
| field_value | text | YES | | NULL | |
+--------------------------+-------------+------+-----+---------+-------+
以下是来自这些字段的一些数据的示例：
mysql> select efi.extra_field_id, ef.field_name, efi.field_value, efi.company_id from extra_fields_info left join extra_fields ef on efi.extra_field_id = ef.extra_field_id;
+----------------+-------------------+--------------------------------+------------+
| extra_field_id | field_name | field_value | company_id |
+----------------+-------------------+--------------------------------+------------+
| 1 | is_affiliate_user | y | 1 |
| 2 | contact_email | [email protected] | 1 |
| 3 | default_currency | MYR | 1 |
| 4 | vertical | 33 | 1 |
| 5 | contact_locale | en | 1 |
| 6 | operating_country | MY | 1 |
| 7 | company_name | SomeCompanyName1 | 1 |
| 1 | is_affiliate_user | y | 2 |
| 2 | contact_email | [email protected] | 2 |
| 3 | default_currency | EUR | 2 |
| 4 | vertical | 3 | 2 |
| 5 | contact_locale | en | 2 |
| 7 | contact_name | ContactName | 2 |
| 6 | operating_country | FR | 2 |
+----------------+-------------------+--------------------------------+------------+
我想要做的是将上面输出中的一些行用作 SELECT 中的字段。我想像这样可视化我的数据：
+------------+------------------+------------------+-------------------+----------+
| company_id | company_name | contact_email | operating_country | vertical |
+------------+------------------+------------------+-------------------+----------+
| 2 | SomeCompanyName1 | [email protected] | EUR | 3 |
| 1 | | [email protected] | MY | 33 |
+------------+------------------+------------------+-------------------+----------+
如您所见，我使用了一些extra_fieldsas 字段名称。我只需要选择上面描述的这些字段中的几个——我不需要所有的。
我什至不确定从哪里开始，所以任何指导将不胜感激。
如果额外字段类型列表是静态的，则
如果不是，则仅动态 SQL。