MySQL中的数据透视-一次只能处理一行

Question

我正试图从RT的数据库中选择资产，并将一组自定义字段的值作为表。相关表格如下：

mysql> describe AT_Assets;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| Type          | int(11)      | NO   | MUL | 0       |                |
| Name          | varchar(200) | NO   | MUL | NULL    |                |
| Description   | varchar(255) | YES  |     | NULL    |                |
| Status        | varchar(20)  | YES  |     | NULL    |                |
| URI           | varchar(255) | YES  |     | NULL    |                |
| LastUpdatedBy | int(11)      | NO   |     | 0       |                |
| LastUpdated   | datetime     | YES  |     | NULL    |                |
| Creator       | int(11)      | NO   |     | 0       |                |
| Created       | datetime     | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> describe CustomFields;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name          | varchar(200) | YES  |     | NULL    |                |
| Type          | varchar(200) | YES  |     | NULL    |                |
| RenderType    | varchar(64)  | YES  |     | NULL    |                |
| MaxValues     | int(11)      | YES  |     | NULL    |                |
| Pattern       | text         | YES  |     | NULL    |                |
| Repeated      | smallint(6)  | NO   |     | 0       |                |
| BasedOn       | int(11)      | YES  |     | NULL    |                |
| ValuesClass   | varchar(64)  | YES  |     | NULL    |                |
| Description   | varchar(255) | YES  |     | NULL    |                |
| SortOrder     | int(11)      | NO   |     | 0       |                |
| LookupType    | varchar(255) | NO   |     | NULL    |                |
| Creator       | int(11)      | NO   |     | 0       |                |
| Created       | datetime     | YES  |     | NULL    |                |
| LastUpdatedBy | int(11)      | NO   |     | 0       |                |
| LastUpdated   | datetime     | YES  |     | NULL    |                |
| Disabled      | smallint(6)  | NO   |     | 0       |                |
+---------------+-------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)
mysql> describe ObjectCustomFieldValues;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| CustomField     | int(11)      | NO   | MUL | NULL    |                |
| ObjectType      | varchar(255) | NO   |     | NULL    |                |
| ObjectId        | int(11)      | NO   |     | NULL    |                |
| SortOrder       | int(11)      | NO   |     | 0       |                |
| Content         | varchar(255) | YES  | MUL | NULL    |                |
| LargeContent    | longblob     | YES  |     | NULL    |                |
| ContentType     | varchar(80)  | YES  |     | NULL    |                |
| ContentEncoding | varchar(80)  | YES  |     | NULL    |                |
| Creator         | int(11)      | NO   |     | 0       |                |
| Created         | datetime     | YES  |     | NULL    |                |
| LastUpdatedBy   | int(11)      | NO   |     | 0       |                |
| LastUpdated     | datetime     | YES  |     | NULL    |                |
| Disabled        | smallint(6)  | NO   |     | 0       |                |
+-----------------+--------------+------+-----+---------+----------------+

这个查询成功地为我获得了所有资产的列表，但在每个自定义字段值的单独列中重复该资产：

SELECT AT_Assets.Name, AT_Assets.description, CustomFields.Name, ObjectCustomFieldValues.content FROM ObjectCustomFieldValues inner join CustomFields on ObjectCustomFieldValues.CustomField = CustomFields.id inner join AT_Assets on AT_Assets.id = ObjectCustomFieldValues.ObjectID order by AT_Assets.description;

所以我读了一些书，学会了如何转向。现在我有了这个：

SELECT at_assets.name, 
   at_assets.description AS "Asset Tag", 
   Max(CASE 
         WHEN customfields.name = "make" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Make", 
   Max(CASE 
         WHEN customfields.name = "model" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Model", 
   Max(CASE 
         WHEN customfields.name = "primary user" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Primary User", 
   Max(CASE 
         WHEN customfields.name = "hostname" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Hostname", 
   Max(CASE 
         WHEN customfields.name = "os" THEN objectcustomfieldvalues.content 
       END)              AS "OS", 
   Max(CASE 
         WHEN customfields.name = "purchase date (if known)" THEN 
         objectcustomfieldvalues.content 
       END)              AS "Purchase Date" 
FROM   objectcustomfieldvalues 
   INNER JOIN customfields 
           ON objectcustomfieldvalues.customfield = customfields.id 
   INNER JOIN at_assets 
           ON at_assets.id = objectcustomfieldvalues.objectid 
WHERE  at_assets.id = 5 
ORDER  BY at_assets.description; 

当我指定单个资产时，这非常有效。但是，我希望这能在每个资产上运行。否则，我需要一个Python脚本来手动递增资产id运行数百次，这相当不雅。如何获取完整的列表？

Answer 1

除了添加group by之外，您了解了有关枢轴的所有内容。

在where:之后添加以下行

group by at_assets.name, at_assets.description

若要查看多行，请删除或调整where子句。

您的查询发生的情况是MySQL将其识别为聚合查询，因为它使用的是MAX()。没有group by，所以它生成一行——所有行的聚合。

select子句中的变量at_assets.name和at_assets.description呢？你可能会问。大多数SQL引擎都会出现故障并产生错误。这些变量既不在group by中，也不在聚合函数的参数中。MySQL有一个叫做隐藏列的（mis）功能，它允许这样的引用。但是，这些值来自源数据中的任意行，因此除非组中的所有值都相同，否则这些值没有意义。

Answer 2

这里只需要group by。并且不要指定资产名称。

更改此

  WHERE  at_assets.id = 5 
  ORDER  BY at_assets.description; 

至

   group by at_assets.name
   ORDER  BY at_assets.description;