mysql - 需要使用 WPDataTables 将行转置为列以查询 MySQL 数据库

Question

我正在使用一个 WordPress 插件，它允许我从我的 WordPress 数据库中提取数据。问题是数据库是由表单插件创建的，表结构是固定的:

+--------------+------------------+
|Column        |Type              |
+--------------+------------------+
|submit_time   |decimal(16,4)     |
+--------------+------------------+
|form_name     |varchar(127) NULL |
+--------------+------------------+
|field_name    |varchar(127) NULL |
+--------------+------------------+
|field_value   |longtext NULL     |
+--------------+------------------+
|field_order   |int(11) NULL      |
+--------------+------------------+
|file          |longblob NULL     |
+--------------+------------------+

因此，数据库中出现的值不适合阅读或编辑。

+------------+------------+-----------------+------------------+------------+-----+
|submit_time |form_name   |field_name       | field_value      |file_order  |file | 
+------------+------------+-----------------+------------------+------------+-----+
|15052703120 |Submissions |your-name        | Leonard Chia     |9999        |file | 
+------------+------------+-----------------+------------------+------------+-----+
|15052703120 |Submissions |your-email       | leonard@mail     |8           |file | 
+------------+------------+-----------------+------------------+------------+-----+
|15052703120 |Submissions |status           | Pending Start    |9           |file | 
+------------+------------+-----------------+------------------+------------+-----+
|15052703120 |Submissions |location         | DD1 Classroom    |7           |file | 
+------------+------------+-----------------+------------------+------------+-----+

我正在努力实现这一目标:

+-------------+-------------+---------------+---------------+
| Requestor   | Email       | Location      | Status        |
+-------------+-------------+---------------+---------------+
|Leonard Chia | leonard@mail| DD1 Classroom | Pending Start |
+-------------+-------------+---------------+---------------+
|Sng Yeekia   | yeekias@mail| DD2 Classroom | Pending Spare |
+-------------+-------------+---------------+---------------+
|Gabriel Lee  | gabriel@mail| SL1 Classroom | Completed     |
+-------------+-------------+---------------+---------------+

我尝试使用 CASE WHEN IF 转置或旋转表格，但它不起作用。还尝试使用 MAX 和 UNION ALL 但结果相同。

这里有人能指出我的编码有什么问题吗？

这是我的代码:

SELECT wpc5_cf7dbplugin_submits.submit_time
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'your-name', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Requestor,
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'campus', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Campus,
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'location', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Defect Location,
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'your-message', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Defect Description,
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'menu-priority', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Priority,
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'work-done', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Action Taken,
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'assigned-to', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Assigned To,
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'category', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Category,
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'file-photo', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Photo
   MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'status', wpc5_cf7dbplugin_submits.field_value,NULL)) AS Status
FROM wpc5_cf7dbplugin_submits
GROUP BY wpc5_cf7dbplugin_submits.submit_time

wpdatatables返回的错误:

No results found. Please check if this query is correct! Table Constructor needs a query that returns data to build a wpDataTable. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX (IF(wpc5_cf7dbplugin_submits.field_name = 'your-name', wpc5_cf7dbplugin_subm' at line 1

更新

修改代码如下

    SELECT wpc5_cf7dbplugin_submits.submit_time,
   CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'your-name' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Requestor,
   CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'campus' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Campus,
   CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'location' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Location,
   CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'your-message' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Description,
   CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'menu-priority' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Priority,
   CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'work-done' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Action,
   CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'assigned-to' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Assigned,
   CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'category' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Category,
  CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'file-photo' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Photo,
  CASE WHEN wpc5_cf7dbplugin_submits.field_name = 'status' THEN wpc5_cf7dbplugin_submits.field_value ELSE NULL END AS Status
FROM wpc5_cf7dbplugin_submits
GROUP BY wpc5_cf7dbplugin_submits.submit_time

该表的 NULL 比字段多，大多数填充的字段仅来自第一列，这很奇怪。

+-------------+----------+----------+----------+
| Requestor   | Campus   | Location | Status   |
+-------------+----------+----------+----------+
|Leonard Chia | NULL     | NULL     | NULL     |
+-------------+----------+----------+----------+
|NULL         | NULL     | NULL     | NULL     |
+-------------+----------+----------+----------+
|Gabriel Lee  | NULL     | NULL     | NULL     |
+-------------+----------+----------+----------+

出了什么问题？？

Answer 1

我认为您的原始查询是正确的，因此我决定在以下演示中对其进行测试:

Demo

我发现了两个导致编译器错误的问题:

1. 您使用了由两个单词组成的别名，但您没有对它们进行转义。不要使用 AS Defect Location，而是在包含别名的单词周围加上反引号:AS `Defect Location`

2. 您的查询在 MAX 和左括号之间放置了空格，例如MAX (IF(condition, ...)。令我惊讶的是，当我删除这个空格时，查询开始工作。

这是更正了这些错误的原始查询:

SELECT
    submit_time,
    MAX(IF(field_name = 'your-name',     field_value, NULL)) AS Requestor,
    MAX(IF(field_name = 'campus',        field_value, NULL)) AS Campus,
    MAX(IF(field_name = 'location',      field_value, NULL)) AS `Defect Location`,
    MAX(IF(field_name = 'your-message',  field_value, NULL)) AS `Defect Description`,
    MAX(IF(field_name = 'menu-priority', field_value, NULL)) AS Priority,
    MAX(IF(field_name = 'work-done',     field_value, NULL)) AS `Action Taken`,
    MAX(IF(field_name = 'assigned-to',   field_value, NULL)) AS `Assigned To`,
    MAX(IF(field_name = 'category',      field_value, NULL)) AS Category,
    MAX(IF(field_name = 'file-photo',    field_value, NULL)) AS Photo
    MAX(IF(field_name = 'status',        field_value, NULL)) AS Status
FROM
    wpc5_cf7dbplugin_submits
GROUP BY
    submit_time

注意单表查询不需要重复完整的表名。