Mysql查询获取一个表的行作为另一个表的列，列名来自第三个表

Question

我要解决的真正问题是如何从 WHMCS 数据库中获取所有客户及其所有自定义字段。我想我可以在 phpmyadmin 中使用查询，以便最终在 csv 中获得结果。

数据库方案结构简述:WHMCS，有两个额外的表(tblCustomFields 和 tblCustomFieldsValues)以方便应用程序用户创建自定义字段。对于创建的每个新自定义字段，名称及其 ID 都存储在 tblCustomFields 中。当填写客户配置文件中的自定义字段时，该值将与客户的 ID 和字段名称的 ID 一起存储在 tblCustomFieldsValues 的一行中。

问题的简化/通用版本

我将尝试描述一个可能适用于许多类似情况的问题的简化通用版本。

假设

我有三个表:

+----------------------------------------------------+
|                     tblClients                     |
+----------------------------------------------------+
| id | company   | standard_field1 | standard_field2 |
+----+-----------+-----------------+-----------------+
| 1  | A company | abc             | yz              |
+----+-----------+-----------------+-----------------+
| 2  | B company | xyz             | foo             |
+----+-----------+-----------------+-----------------+
| 3  | C company | zyx             | bar             |
+----+-----------+-----------------+-----------------+


+--------------------------+
|      tblCustomFields     |
+--------------------------+
| id | fieldname           |
+----+---------------------+
| 1  | Custom Field Name 1 |
+----+---------------------+
| 2  | Custom Field Name 2 |
+----+---------------------+
| 3  | Custom Field Name 3 |
+----+---------------------+

+----------------------------------------------------+
|                tblCustomFieldsValues               |
+----------------------------------------------------+
|      relid      |      fieldid       |    value    |
| (corresponds to |   (corresponds to  |             |
|   a client id)  | a custom field id) |             |
+-----------------+--------------------+-------------+
| 1               | 1                  | any value   |
+-----------------+--------------------+-------------+
| 1               | 2                  | some value  |
+-----------------+--------------------+-------------+
| 1               | 3                  | field value |
+-----------------+--------------------+-------------+
| 2               | 1                  | data        |
+-----------------+--------------------+-------------+
| 2               | 2                  | whatever    |
+-----------------+--------------------+-------------+
| 2               | 3                  | anything    |
+-----------------+--------------------+-------------+
| 3               | 1                  | and so on   |
+-----------------+--------------------+-------------+
| 3               | 2                  | and on      |
+-----------------+--------------------+-------------+

目标

我需要所有客户的数据，从数据库导出到 csv。 重要提示:每个客户都应该只出现在一行中。预期结果应与此类似:

+-----------------------------------------------------------------------------+
|       query result, clients with their custom fields as extra columns       |
+-----------------------------------------------------------------------------+
| id |  company  | standard | standard |  Custom   |   Custom   |   Custom    |
|    |           |  field1  |  field2  |   Field   |    Field   |    Field    |
|    |           |          |          |   Name 1  |   Name 2   |    Name 3   |
+----+-----------+----------+----------+-----------+------------+-------------+
| 1  | A company | abc      | yz       | any value | some value | field value |
+----+-----------+----------+----------+-----------+------------+-------------+
| 2  | B company | xyz      | foo      | data      | whatever   | anything    |
+----+-----------+----------+----------+-----------+------------+-------------+
| 3  | C company | zyx      | bar      | and so on | and on     |             |
+----+-----------+----------+----------+-----------+------------+-------------+

想法

我认为解决方案是在 phpmyadmin 中输入查询，然后轻松地在 csv 中获取结果。

最后一个问题

将所有客户的自定义字段放在单独的列中的 mysql 查询是什么？是否有更好的方法来实现上述目标？

如果您对如何改写标题或问题的任何部分以使其更清楚有任何想法，请发表评论!

Answer 1

您可以使用 Pivot 来获得预期的输出，但由于 Mysql 不支持 pivot，您必须使用 case when 子句。 SQL查询如下:静态 SQL 查询:

Select TB.*, TC.`standard_field1`, TC.`standard_field2`
from tblClients TC
Inner Join(
SELECT TBD.Id, TBD.company,
  Max(CASE WHEN (TBD.fieldname ='Custom Field Name 1') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 1',
  Max(CASE WHEN (TBD.fieldname ='Custom Field Name 2') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 2',
  Max(CASE WHEN (TBD.fieldname ='Custom Field Name 3') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 3'
FROM(
Select TC.Id, TC.Company,
  TCF.fieldname, TCFV.Value
  from tblCustomFieldsValues TCFV
  Inner Join tblClients TC
  ON TCFV.relid = TC.ID
  Inner Join tblCustomFields TCF
  ON TCFV.fieldid = TCF.Id) TBD

 Group BY TBD.Id, TBD.company) TB
 On TB.Id = TC.Id

我还提供了 SQL fiddle链接将帮助您获得完整的数据和输出。

动态 SQL 查询:

SET @sql = NULL;
SELECT 
  GROUP_CONCAT(DISTINCT
               CONCAT('Max(CASE WHEN (TBD.fieldname =''', UN.fieldname,
                      ''') THEN TBD.value ELSE NULL END) AS ''',UN.fieldname,''''))
    INTO @sql
    FROM (
      Select * from tblCustomFields) UN;

SET @sql =  CONCAT('Select TB.*, TC.`standard_field1`, TC.`standard_field2`
                    from tblClients TC
                    Inner Join(SELECT TBD.Id, TBD.company, ', @sql, ' 
                      FROM(
                        Select TC.Id, TC.Company,
                          TCF.fieldname, TCFV.Value
                          from tblCustomFieldsValues TCFV
                          Inner Join tblClients TC
                          ON TCFV.relid = TC.ID
                          Inner Join tblCustomFields TCF
                          ON TCFV.fieldid = TCF.Id) TBD

                         Group BY TBD.Id, TBD.company) TB On TB.Id = TC.Id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Fiddle链接

希望对您有所帮助。