I'm trying to select assets from RT's database with the the values for a set of custom fields as tables. Relevant tables are as follows:
我正在尝试从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 | |
+-----------------+--------------+------+-----+---------+----------------+
This query successfully gets me a listing of all assets, but repeats the asset in a separate column for each custom field value:
此查询成功获取所有资产的列表,但在每个自定义字段值的单独列中重复该资产:
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;
So I did some reading, and learned how to pivot. Now I have this:
所以我做了一些阅读,并学会了如何转动。现在我有这个:
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;
Which works great, when I'm specifying a single asset. However, I would like this to run across every asset. Otherwise I need a Python script to run this hundreds of times manually incrementing the asset id, which is fairly inelegant. How do I go about getting a full list?
当我指定单个资产时,哪个很好用。但是,我希望这可以跨越每个资产。否则我需要一个Python脚本来运行这几百次手动递增资产ID,这是相当不优雅的。我如何获得完整列表?
2 个解决方案
#1
1
You learned everything about the pivot, except adding the group by.
除了添加组之外,您学习了有关数据透视的所有内容。
Add the following line after the where:
在where之后添加以下行:
group by at_assets.name, at_assets.description
To see more than one line, remove or adjust the where clause.
要查看多行,请删除或调整where子句。
What is happening with your query is the MySQL recognizes it as an aggregation query, because it is using MAX(). There is no group by, so it produces one row -- an aggregation of all the rows.
您的查询发生的是MySQL将其识别为聚合查询,因为它使用MAX()。没有group by,因此它产生一行 - 所有行的聚合。
What about the variables at_assets.name and at_assets.description in the select clause? you might ask. Well, most SQL engines would balk and produce an error. These variables are neither in the group by nor the argument to an aggregation function. MySQL has a (mis)feature called hidden columns that allows such references. However, the values come from arbitrary rows in the source data, so the value are not meaningful unless all values in the group are the same.
select子句中的变量at_assets.name和at_assets.description怎么样?你可能会问。好吧,大多数SQL引擎都会拒绝并产生错误。这些变量既不在聚合函数的group by中,也不在聚合函数的参数中。 MySQL有一个称为隐藏列的(错误)功能,允许这样的引用。但是,值来自源数据中的任意行,因此除非组中的所有值都相同,否则该值无意义。
#2
0
you just need group by here. and dont specify the asset name.
你只需要在这里组。并且不要指定资产名称。
change this
WHERE at_assets.id = 5
ORDER BY at_assets.description;
to
group by at_assets.name
ORDER BY at_assets.description;
#1
1
You learned everything about the pivot, except adding the group by.
除了添加组之外,您学习了有关数据透视的所有内容。
Add the following line after the where:
在where之后添加以下行:
group by at_assets.name, at_assets.description
To see more than one line, remove or adjust the where clause.
要查看多行,请删除或调整where子句。
What is happening with your query is the MySQL recognizes it as an aggregation query, because it is using MAX(). There is no group by, so it produces one row -- an aggregation of all the rows.
您的查询发生的是MySQL将其识别为聚合查询,因为它使用MAX()。没有group by,因此它产生一行 - 所有行的聚合。
What about the variables at_assets.name and at_assets.description in the select clause? you might ask. Well, most SQL engines would balk and produce an error. These variables are neither in the group by nor the argument to an aggregation function. MySQL has a (mis)feature called hidden columns that allows such references. However, the values come from arbitrary rows in the source data, so the value are not meaningful unless all values in the group are the same.
select子句中的变量at_assets.name和at_assets.description怎么样?你可能会问。好吧,大多数SQL引擎都会拒绝并产生错误。这些变量既不在聚合函数的group by中,也不在聚合函数的参数中。 MySQL有一个称为隐藏列的(错误)功能,允许这样的引用。但是,值来自源数据中的任意行,因此除非组中的所有值都相同,否则该值无意义。
#2
0
you just need group by here. and dont specify the asset name.
你只需要在这里组。并且不要指定资产名称。
change this
WHERE at_assets.id = 5
ORDER BY at_assets.description;
to
group by at_assets.name
ORDER BY at_assets.description;