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，因此没有2006相关行(并且不匹配)的2005年数据仍将显示。

使用Galz的解决方案或正确建议的搜索来创建PIVOT查询。

---

创建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提供的pivot的链接，并感谢hypercube提供的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