Mysql，从长/高到宽重塑数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2255640/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql, reshape data from long / tall to wide
提问by chongman
I have data in a mysql table in long / tall format (described below) and want to convert it to wide format. Can I do this using just sql?
我在 mysql 表中有长/高格式（如下所述）的数据，并希望将其转换为宽格式。我可以只使用 sql 来做到这一点吗？
Easiest to explain with an example. Suppose you have information on (country, key, value) for M countries, N keys (e.g. keys can be income, political leader, area, continent, etc.)
用一个例子来解释最简单。假设你有 M 个国家的（国家、键、值）信息，N 个键（例如键可以是收入、领袖、地区、大陆等）
Long format has 3 columns: country, key, value
- M*N rows.
e.g.
'USA', 'President', 'Obama'
...
'USA', 'Currency', 'Dollar'
Wide format has N=16 columns: county, key1, ..., keyN
- M rows
example:
country, President, ... , Currency
'USA', 'Obama', ... , 'Dollar'
Is there a way in SQL to create a new table with the data in the wide format?
有没有办法在 SQL 中创建一个包含宽格式数据的新表？
select distinct key from table;
// this will get me all the keys.
// 这会让我得到所有的钥匙。
1) How do I then create the table using these key elements?
1) 然后我如何使用这些关键元素创建表格？
2) How do I then fill in the table values?
2）然后我如何填写表格值？
I'm pretty sure I can do this with any scripting language (I like python), but wanted to know if there is an easy way to do this in mysql. Many statistical packages like R and STATA have this command built in because it is often used.
我很确定我可以用任何脚本语言（我喜欢 python）来做到这一点，但想知道在 mysql 中是否有一种简单的方法来做到这一点。许多像 R 和 STATA 这样的统计包都内置了这个命令，因为它经常被使用。
======
======
To be more clear, here is the desired input output for a simple case:
更清楚地说，这是一个简单案例所需的输入输出：
Input:
输入：
country attrName attrValue key (these are column names)
US President Obama 2
US Currency Dollar 3
China President Hu 4
China Currency Yuan 5
Output
输出
country President Currency newPkey
US Obama Dollar 1
China Hu Yuan 2
采纳答案by mluebke
Cross-tabs or pivot tables is the answer. From there you can SELECT FROM ... INSERT INTO ... or create a VIEW from the single SELECT.
交叉表或数据透视表就是答案。从那里你可以 SELECT FROM ... INSERT INTO ... 或从单个 SELECT 创建一个视图。
Something like:
就像是：
SELECT country,
MAX( IF( key='President', value, NULL ) ) AS President,
MAX( IF( key='Currency', value, NULL ) ) AS Currency,
...
FROM table
GROUP BY country;
For more info: http://dev.mysql.com/tech-resources/articles/wizard/index.html
更多信息：http: //dev.mysql.com/tech-resources/articles/wizard/index.html
回答by chongman
I think I found the solution, which uses VIEWS and INSERT INTO (as suggested by e4c5).
我想我找到了使用 VIEWS 和 INSERT INTO 的解决方案（如 e4c5 所建议的）。
You have to get your list of AttrNames/Keys yourself, but MYSQL does the other heavy lifting.
您必须自己获取 AttrNames/Keys 列表，但 MYSQL 会完成其他繁重的工作。
For the simple test case above, create the new_table with the appropriate columns (don't forget to have an auto-increment primary key as well). Then
对于上面的简单测试用例，使用适当的列创建 new_table（不要忘记还有一个自动递增的主键）。然后
CREATE VIEW a
AS SELECT country, attrValue
WHERE attrName="President";
CREATE VIEW b
AS SELECT country, attrValue
WHERE attrName="Currency";
INSERT INTO newtable(country, President, Currency)
SELECT a.country, a.attrValue, b.attrValue
FROM a
INNER JOIN b ON a.country=b.country;
If you have more attrNames, then create one view for each one and then adjust the last statement accordingly.
如果您有更多 attrNames，则为每个视图创建一个视图，然后相应地调整最后一个语句。
INSERT INTO newtable(country, President, Currency, Capital, Population)
SELECT a.country, a.attrValue, b.attrValue, c.attrValue, d.attrValue
FROM a
INNER JOIN b ON a.country=b.country
INNER JOIN c ON a.country=c.country
INNER JOIN d ON a.country=d.country;
Some more tips
还有一些提示
- use NATURAL LEFT JOIN and you don't have to specify the ON clause
- 使用 NATURAL LEFT JOIN 并且您不必指定 ON 子句
回答by Mark Byers
If you were using SQL Server, this would be easy using UNPIVOT. As far as I am aware, this is not implemented in MySQL, so if you want to do this (and I'd advise against it) you'll probably have to generate the SQL dynamically, and that's messy.
如果您使用的是 SQL Server，那么使用UNPIVOT会很容易。据我所知，这不是在 MySQL 中实现的，所以如果你想这样做（我建议不要这样做），你可能必须动态生成 SQL，这很麻烦。
