MySQL 从视图中多维选择

Question

我想显示逐年重新排列的数据，可能的解决方案之一是使用视图并从中进行选择。数据矩阵类似于（当然这是一个虚构的演示数据集）：

USA  2005  22  156
CAN  2005  14  101
MEX  2005   5   32
USA  2006  24  160
CAN  2006  16  103
USA  2007  26  163
MEX  2007   8   35

创建和填充表的 SQL 代码是：

DROP TABLE IF EXISTS `tab1`;<br>
CREATE TABLE `tab1` ( <br>
  `id1` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `iso3` char(3) NOT NULL,
  `year` int(4) unsigned NOT NULL,
  `aaa` int(10) DEFAULT NULL, 
  `bbb` int(10) DEFAULT NULL, 
  PRIMARY KEY (`id1`) 
)


INSERT INTO `tab1` VALUES 
('1', 'USA', '2005', '22', '156'),
('2', 'CAN', '2005', '14', '101'), 
('3', 'MEX', '2005', '5', '32'),   
('4', 'USA', '2006', '24', '160'), 
('5', 'CAN', '2006', '16', '103'), 
('6', 'USA', '2007', '26', '163'), 
('7', 'MEX', '2007', '8', '35');   
COMMIT; 

现在我想为参数“aaa”获取一个像这样的 2D 表：

country 2005 2006 2007 
USA     22   24   26   
CAN     14   16        
MEX      5   8  

但是以下 SQL 代码正在忽略所有缺少数据的行，无论是一个值，我只得到一行

 USA    22  24  26

SQL 代码是：

 SELECT view2005.Country, view2005.2005, view2006.2006, view2007.2007 
 FROM   view2005, view2006, view2007 
 WHERE  view2005.country = view2006.country 
 AND    view2005.country = view2007.country   

知道如何做到这一点，包括缺少数据的行吗？提前致谢。

Answer 1

使用左联接和具有所有不同国家/地区的视图（或表或内部选择）：

SELECT c.country, view2005.2005, view2006.2006, view2007.2007 
FROM (SELECT DISTINCT country FROM tab1) as c
LEFT JOIN view2005 ON view2005.country = c.country
LEFT JOIN view2006 ON view2006.country = c.country
LEFT JOIN view2007 ON view2007.country = c.country
GROUP BY c.country

编辑：

在更一般的上下文中，您在这里要求的是创建一个该表的数据透视，这是一个有共同解决方案的常见问题。这是一个很好的“操作方法”：http://www. artfulsoftware.com/infotree/queries.php?&bw=1339#78

Answer 2

使用 JOIN 比使用 WHERE 隐式 JOIN 更好。额外的优点是您可以将其转换为 LEFT JOIN，这样 2005 年没有 2006 年相关行（并且不匹配）的数据仍将显示。

使用 Galz 的解决方案或按照正确建议进行搜索，了解如何创建 PIVOT 查询。

---

创建数据透视查询的逻辑之一是：

 SELECT iso3 AS Country
      , SUM(IF(year=2005, aaa, 0)) AS 2005
      , SUM(IF(year=2006, aaa, 0)) AS 2006
      , SUM(IF(year=2007, aaa, 0)) AS 2007 
 FROM  tab1 AS t
 GROUP BY iso3

如果多年来没有任何数据，您将在该列中得到 NULL。
如果您希望显示 0 而不是 NULL，则可以使用 COALESCE() 函数：

 SELECT iso3 AS Country
      , COALESCE( SUM( IF(year=2004, aaa, 0) ) , 0) AS "2004" 
      , COALESCE( SUM( IF(year=2005, aaa, 0) ) , 0) AS "2005"
      , COALESCE( SUM( IF(year=2006, aaa, 0) ) , 0) AS "2006"
      , COALESCE( SUM( IF(year=2007, aaa, 0) ) , 0) AS "2007" 
 FROM  tab1 AS t
 GROUP BY iso3

Answer 3

感谢 Galz 提供的枢轴链接，也感谢 ypercube 提供的 SQL。将年份括在引号中以使它们成为 CHAR 后，它就起作用了。

我对这个问题进一步感兴趣：如果我添加一行根本没有值或一行超出年份范围，会发生什么，所以我添加了

INSERT INTO `tab1` VALUES 
('7', 'ATA', '2004', '', '')

结果是我得到了 NULL 和 INT 零值的混合。这不好，因为零是有效数字和合法数据。所以我修改了查询以获得我需要的结果：

SELECT iso3 AS countryб
       SUM( IF(year=2004, aaa, NULL) ) AS "2004",
       SUM( IF(year=2005, aaa, NULL) ) AS "2005",
       SUM( IF(year=2006, aaa, NULL) ) AS "2006",
       SUM( IF(year=2007, aaa, NULL) ) AS "2007"
 FROM  tab1
 GROUP BY iso3