MySQL，将数据从长格式/高格式转换为宽格式

Question

我在mysql表中有长格式（如下所述）的数据，并希望将其转换为宽格式。我能否仅使用SQL完成此操作？

最好通过示例来解释。假设您具有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'

在SQL中是否有一种方法可以创建一个新的表格，并将数据以宽格式存储？

select distinct key from table;

// 这将获取所有的键。

1）那么如何使用这些关键元素创建表格？

2）然后如何填充表格值？

我相信我可以用任何脚本语言（我喜欢Python）来做到这一点，但想知道是否有一种在MySQL中轻松完成此操作的方法。许多统计软件包（如R和STATA）都内置了此命令，因为它经常被使用。

======

为了更清楚，这是一个简单情况下所需的输入输出：

输入：

country    attrName    attrValue     key  (these are column names)
US         President   Obama         2
US         Currency    Dollar        3
China      President   Hu            4
China      Currency    Yuan          5

输出

country    President    Currency    newPkey
US         Obama        Dollar      1
China      Hu           Yuan        2

- chongman

Answer 1

交叉表或数据透视表是答案。您可以从中选择，插入或创建一个视图。

例如：

SELECT country, 
       MAX( IF( key='President', value, NULL ) ) AS President,
       MAX( IF( key='Currency', value, NULL ) ) AS Currency,
       ...

FROM table 
GROUP BY country;

- mluebke

---

我的方式可行。你的方式更好。我爱你或谢谢。选择一种你喜欢的方式来表达感激之情。 - chongman

IF 操作符是 MySQL 的专有操作符。为了符合标准 SQL，应使用 CASE 代替。此处有更多细节：SQLite 长到宽的格式？ - SuperAce99

@mluebke：你的链接挂了 :( - Monica Heddneck

为什么你需要在每一行中使用“max()”，而不是在country字段中使用？ - alwaysaskingquestions

因为你正在按国家分组，所以对于该组，它始终是一个一致的值。 - mluebke

Answer 2

如果您使用的是SQL Server，那么使用UNPIVOT很容易。据我所知，MySQL没有实现这个功能，所以如果您想这样做（我建议不要这样做），您可能需要动态生成SQL语句，而这会变得混乱。

- Mark Byers

Answer 3

我认为我找到了解决方案，它使用VIEWS和INSERT INTO（正如e4c5建议的那样）。

你需要自己获取AttrNames/Keys列表，但MYSQL会完成其他繁重的工作。

对于上面的简单测试用例，请创建具有适当列的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;

如果您有更多的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;

更多提示

• 使用NATURAL LEFT JOIN，您无需指定ON子句

- chongman