問題描述
我嘗試搜索帖子，但我只找到了 SQL Server/Access 的解決方案.我需要 MySQL (5.X) 中的解決方案.
I tried to search posts, but I only found solutions for SQL Server/Access. I need a solution in MySQL (5.X).
我有一個包含 3 列的表(稱為歷史記錄):hostid、itemname、itemvalue.
如果我做了一個選擇(select * from history)，它會返回
I have a table (called history) with 3 columns: hostid, itemname, itemvalue.
If I do a select (select * from history), it will return
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
+--------+----------+-----------+
| 1 | B | 3 |
+--------+----------+-----------+
| 2 | A | 9 |
+--------+----------+-----------+
| 2 | c | 40 |
+--------+----------+-----------+
如何查詢數據庫以返回類似的內容
How do I query the database to return something like
+--------+------+-----+-----+
| hostid | A | B | C |
+--------+------+-----+-----+
| 1 | 10 | 3 | 0 |
+--------+------+-----+-----+
| 2 | 9 | 0 | 40 |
+--------+------+-----+-----+
推薦答案
我將添加更長更詳細的說明，說明解決此問題的步驟.如果太長，我深表歉意.
I'm going to add a somewhat longer and more detailed explanation of the steps to take to solve this problem. I apologize if it's too long.
我將從您提供的基礎開始，并使用它來定義我將在本文的其余部分使用的幾個術語.這將是基表:
I'll start out with the base you've given and use it to define a couple of terms that I'll use for the rest of this post. This will be the base table:
select * from history;
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
| 1 | B | 3 |
| 2 | A | 9 |
| 2 | C | 40 |
+--------+----------+-----------+
這將是我們的目標，漂亮的數據透視表:
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
history.hostid 列中的值將成為數據透視表中的 y 值.history.itemname 列中的值將變為 x 值(原因很明顯).
Values in the history.hostid column will become y-values in the pivot table. Values in the history.itemname column will become x-values (for obvious reasons).
當我必須解決創建數據透視表的問題時，我會使用三步流程(可選的第四步)來解決它:
When I have to solve the problem of creating a pivot table, I tackle it using a three-step process (with an optional fourth step):
- 選擇感興趣的列，即y值和x值
- 使用額外的列擴展基表 - 每個 x 值
- 對擴展表進行分組和聚合 -- 每個 y 值
- (可選)美化聚合表
讓我們將這些步驟應用于您的問題，看看我們得到了什么:
Let's apply these steps to your problem and see what we get:
第 1 步:選擇感興趣的列.在所需的結果中，hostid 提供 y 值，itemname 提供 x 值.
Step 1: select columns of interest. In the desired result, hostid provides the y-values and itemname provides the x-values.
第 2 步:使用額外的列擴展基表.我們通常每個 x 值需要一列.回想一下我們的 x 值列是 itemname:
Step 2: extend the base table with extra columns. We typically need one column per x-value. Recall that our x-value column is itemname:
create view history_extended as (
select
history.*,
case when itemname = "A" then itemvalue end as A,
case when itemname = "B" then itemvalue end as B,
case when itemname = "C" then itemvalue end as C
from history
);
select * from history_extended;
+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A | B | C |
+--------+----------+-----------+------+------+------+
| 1 | A | 10 | 10 | NULL | NULL |
| 1 | B | 3 | NULL | 3 | NULL |
| 2 | A | 9 | 9 | NULL | NULL |
| 2 | C | 40 | NULL | NULL | 40 |
+--------+----------+-----------+------+------+------+
請注意，我們沒有更改行數——我們只是添加了額外的列.還要注意 NULLs 的模式——itemname = "A" 的行對于新列 A 具有非空值，并且其他新列的空值.
Note that we didn't change the number of rows -- we just added extra columns. Also note the pattern of NULLs -- a row with itemname = "A" has a non-null value for new column A, and null values for the other new columns.
步驟 3:對擴展表進行分組和聚合.我們需要group by hostid，因為它提供了y值:
Step 3: group and aggregate the extended table. We need to group by hostid, since it provides the y-values:
create view history_itemvalue_pivot as (
select
hostid,
sum(A) as A,
sum(B) as B,
sum(C) as C
from history_extended
group by hostid
);
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | NULL |
| 2 | 9 | NULL | 40 |
+--------+------+------+------+
(請注意，我們現在每個 y 值有一行.) 好的，我們快到了！我們只需要擺脫那些丑陋的NULLs.
(Note that we now have one row per y-value.) Okay, we're almost there! We just need to get rid of those ugly NULLs.
第 4 步:美化.我們將用零替換任何空值，以便結果集更好看:
Step 4: prettify. We're just going to replace any null values with zeroes so the result set is nicer to look at:
create view history_itemvalue_pivot_pretty as (
select
hostid,
coalesce(A, 0) as A,
coalesce(B, 0) as B,
coalesce(C, 0) as C
from history_itemvalue_pivot
);
select * from history_itemvalue_pivot_pretty;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
我們已經完成了——我們已經使用 MySQL 構建了一個漂亮、漂亮的數據透視表.
And we're done -- we've built a nice, pretty pivot table using MySQL.
應用此程序時的注意事項:
Considerations when applying this procedure:
- 在額外的列中使用什么值.我在這個例子中使用了
itemvalue - 在額外的列中使用什么中性"值.我使用了
NULL，但也可以是0或""，具體取決于您的具體情況 - 分組時使用什么聚合函數.我用的是
sum，但是count和max也經常用到(max常用于構建單行分散在多行中的對象") - 對 y 值使用多列.此解決方案不限于對 y 值使用單列——只需將額外的列插入
group by子句(不要忘記select他們)
- what value to use in the extra columns. I used
itemvaluein this example - what "neutral" value to use in the extra columns. I used
NULL, but it could also be0or"", depending on your exact situation - what aggregate function to use when grouping. I used
sum, butcountandmaxare also often used (maxis often used when building one-row "objects" that had been spread across many rows) - using multiple columns for y-values. This solution isn't limited to using a single column for the y-values -- just plug the extra columns into the
group byclause (and don't forget toselectthem)
已知限制:
- 該解決方案不允許數據透視表中有 n 列——在擴展基表時需要手動添加每個數據透視列.所以對于 5 或?? 10 個 x 值，這個解決方案很好.100，不太好.有一些存儲過程生成查詢的解決方案，但它們很丑陋且難以正確處理.當數據透視表需要有很多列時，我目前不知道有什么好的方法可以解決這個問題.
這篇關于MySQL - 行到列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！