SQL面试题（四）

问题：

显示下表年度为季度,算出总计

+----+------+---------+-----------+

| id | year | quarter | amount    |

+----+------+---------+-----------+

|  1 | 2004 |       1 |   2328.00 |

|  2 | 2004 |       2 |   3822.00 |

|  3 | 2004 |       3 |   7071.00 |

|  4 | 2004 |       4 |   8931.00 |

|  5 | 2005 |       1 |   2633.00 |

|  6 | 2005 |       2 |   3910.00 |

|  7 | 2005 |       3 | 237193.00 |

|  8 | 2005 |       4 | 567444.00 |

|  9 | 2006 |       1 |  12313.00 |

+----+------+---------+-----------+

结果:

+------+----------+---------+-----------+-----------+

| year | 1d       | 2d      | 3d        | 4d        |SUM

+------+----------+---------+-----------+-----------+

| 2004 |  2328.00 | 3822.00 |   7071.00 |   8931.00 |

| 2005 |  2633.00 | 3910.00 | 237193.00 | 567444.00 |

| 2006 | 12313.00 |    NULL |      NULL |      NULL |

+------+----------+---------+-----------+-----------+

 

建表Sql：

DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale` (
    `id` int(10) unsigned NOT NULL auto_increment,
     `year` int(11) NOT NULL,
      `quarter` int(11) NOT NULL,
    `amount` decimal(15,2) NOT NULL,
    PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

INSERT INTO `sale` VALUES (1,2004,1,2328);
INSERT INTO `sale` VALUES (2,2004,2,3822);
INSERT INTO `sale` VALUES (3,2004,3,7071);
INSERT INTO `sale` VALUES (4,2004,4,8931);
INSERT INTO `sale` VALUES (5,2005,1,2633);
INSERT INTO `sale` VALUES (6,2005,2,3910);
INSERT INTO `sale` VALUES (7,2005,3,237193);
INSERT INTO `sale` VALUES (8,2005,4,567444);
INSERT INTO `sale` VALUES (9,2006,1,12313);

 

方法1：

select a.year, 1d, 2d, 3d, 4d, sum from  
     (select distinct year from sale) a left join
     (select year, amount 1d from sale where quarter=1 group by year) a1d on a.year = a1d.year 
     left join (select year, amount 2d from sale where quarter=2 group by year) a2d on a2d.year=a.year
     left join (select year, amount 3d from sale where quarter=3 group by year) a3d on a3d.year=a.year
     left join (select year, amount 4d from sale where quarter=4 group by year) a4d on a4d.year=a.year
     left join (select year, amount, sum(amount) as sum from sale group by year ) a5d 

on a5d.year=a.year ;

 

方法2：

SELECT year,  
Sum(CASE WHEN quarter = 1 then amount  else 0 end) as 1d,
Sum(CASE WHEN quarter = 2 then amount  else 0 end) as 2d,
Sum(CASE WHEN quarter = 3 then amount  else 0 end) as 3d,
Sum(CASE WHEN quarter = 4 then amount  else 0 end) as 4d,
Sum(amount ) as sum
FROM sale GROUP BY year

 

结果：

+------+----------+---------+-----------+-----------+-----------+

| year | 1d       | 2d      | 3d        | 4d        | sum       |

+------+----------+---------+-----------+-----------+-----------+

| 2004 |  2328.00 | 3822.00 |   7071.00 |   8931.00 |  22152.00 |

| 2005 |  2633.00 | 3910.00 | 237193.00 | 567444.00 | 811180.00 |

| 2006 | 12313.00 |    NULL |      NULL |      NULL |  12313.00 |

+------+----------+---------+-----------+-----------+-----------+