mysql - SQL更新以透视表并计算新列

我想将包含两个变量的单个列旋转/重组为具有相同索引的两个列，并计算这些变量之间的差。

下表定义显示了两个不同的dataType通过类别，countryID和year进行标识。

CREATE  TABLE IF NOT EXISTS Data (
  dataID INT PRIMARY KEY AUTO_INCREMENT ,
  category INT NOT NULL REFERENCES Categories (Category),
  dataType INT NOT NULL,
  countryID INT NOT NULL REFERENCES Countries (countryID),
  year INT NOT NULL,
  values DEC(20,2) NULL);

目标是达到下表：

CREATE  TABLE IF NOT EXISTS Data (
  dataID INT PRIMARY KEY AUTO_INCREMENT ,
  category INT NOT NULL REFERENCES Categories (Category),
  countryID INT NOT NULL REFERENCES Countries (countryID),
  year INT NOT NULL,
  dataType1values DEC(20,2) NULL),
  dataType2values DEC(20,2) NULL),
  type1-type2values DEC(20,2) NULL);

有大约100多个国家/地区，25年和5000+个类别。尽管进行了大量研究，但我仍在努力寻找一种有效的方法来实现（显然）必需的自联接。请让我知道，如果你有任何问题。谢谢！

最佳答案

假定每个类别/县/年都具有类型1和类型2值。如果您需要处理没有两者的行，它将变得更加复杂（尤其是因为MySQL没有CROSS JOIN）。

create table NewData (<column definitions>)
select d1.category, d1.countryID, d1.year,
       d1.values as dataType1values, d2.values as dataType2values,
       d1.values-d2.values as values_diff,
       NULL dataID /* to allow auto-increment */
from Data d1
join Data d2 USING (category, countryID, year)
where d1.dataType = 1
and d2.dataType = 2