数据库行列转换经典面试题

第一题：

1.1 在数据库中创建表

[SQL] 纯文本查看 复制代码

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `year` varchar(50) DEFAULT NULL,
  `month` int(50) DEFAULT NULL,
  `amount` int(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2 准备数据

[SQL] 纯文本查看 复制代码

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('2011', '1', '1');
INSERT INTO `test` VALUES ('2011', '2', '1');
INSERT INTO `test` VALUES ('2011', '3', '1');
INSERT INTO `test` VALUES ('2011', '4', '1');
INSERT INTO `test` VALUES ('2012', '1', '2');
INSERT INTO `test` VALUES ('2012', '2', '2');
INSERT INTO `test` VALUES ('2012', '3', '2');
INSERT INTO `test` VALUES ('2012', '4', '2');

1.3 答案一

[SQL] 纯文本查看 复制代码

SELECT YEAR,
   SUM(CASE WHEN MONTH='1' THEN amount ELSE 0  END) m1,
   SUM(CASE WHEN MONTH='2' THEN amount ELSE 0  END) m2,
   SUM(CASE WHEN MONTH='3' THEN amount ELSE 0  END) m3,
   SUM(CASE WHEN MONTH='4' THEN amount ELSE 0  END) m4
   FROM abc GROUP BY YEAR

1.4 答案二

[SQL] 纯文本查看 复制代码

SELECT
        year as 'year',
        MAX(CASE `month` WHEN '1' THEN amount ELSE 0 END) AS 'm1',
        MAX(CASE `month` WHEN '2' THEN amount ELSE 0 END) as 'm2',
        MAX(CASE `month` WHEN '3' THEN amount ELSE 0 END) as 'm3',
        max(case `month` when '4' then amount else 0 end ) as 'm4'
FROM
        test
GROUP BY
        year
ORDER BY year;

1.5 答案三

[SQL] 纯文本查看 复制代码

select  a.year year,a.amount m1,b.amount m2,c.amount m3,d.amount m4 from
test a,test b,test c,test d
where a.month=1 and b.month=2 and c.month=3 and d.month=4 and a.year=b.year
and b.year=c.year and c.year=d.year;

=====================================================================


第二题：

2.1 在数据库中创建表

[SQL] 纯文本查看 复制代码

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `week` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `hasCourse` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2.2 准备数据

[SQL] 纯文本查看 复制代码

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '李彦宏', '星期二', '有');
INSERT INTO `teacher` VALUES ('2', '李彦宏', '星期三', '有');
INSERT INTO `teacher` VALUES ('3', '马云', '星期一', '有');
INSERT INTO `teacher` VALUES ('4', '马化腾', '星期二', '有');
INSERT INTO `teacher` VALUES ('5', '李彦宏', '星期二', '有');

2.3 答案一

[SQL] 纯文本查看 复制代码

mysql> SELECT NAME 教师姓名,
SUM(CASE WHEN WEEK='星期一' AND hasCourse='有' THEN 1 ELSE NULL END) 星期一,
SUM(CASE WHEN WEEK='星期二' AND hasCourse='有' THEN 1 ELSE NULL END) 星期二,
SUM(CASE WHEN WEEK='星期三' AND hasCourse='有' THEN 1 ELSE NULL END) 星期三
FROM teacher
GROUP BY 教师姓名;
+----------+--------+--------+--------+
| 教师姓名 | 星期一 | 星期二 | 星期三 |
+----------+--------+--------+--------+
| 李彦宏   | NULL   | 2      | 1      |
| 马云     | 1      | NULL   | NULL   |
| 马化腾   | NULL   | 1      | NULL   |
+----------+--------+--------+--------+
3 rows in set

2.4 答案二

[SQL] 纯文本查看 复制代码

mysql> SELECT a.name AS '姓名',
(SELECT COUNT(1) FROM teacher b WHERE b.week = '星期一' AND b.hasCourse='有' AND b.name=a.name)AS '星期一',
(SELECT COUNT(1) FROM teacher c WHERE c.week='星期二' AND c.hasCourse='有'AND c.name=a.name)AS '星期二',
(SELECT COUNT(1) FROM teacher d WHERE d.week='星期三' AND d.hasCourse='有'AND d.name=a.name)AS '星期三' 
FROM teacher a GROUP BY NAME;
+--------+--------+--------+--------+
| 姓名   | 星期一 | 星期二 | 星期三 |
+--------+--------+--------+--------+
| 李彦宏 |      0 |      2 |      1 |
| 马云   |      1 |      0 |      0 |
| 马化腾 |      0 |      1 |      0 |
+--------+--------+--------+--------+
3 rows in set

2.5 答案三

[SQL] 纯文本查看 复制代码

select  temp.name as 姓名, (temp.星期一) ,sum(temp.星期二)as 星期二,
sum(temp.星期三)as 星期三 from 
(select name,
ifnull(week like '星期一',null)as 星期一 ,
ifnull(week like '星期二',null) as 星期二,
ifnull(week like '星期三',null) as 星期三 from teacher) 
temp group by temp.name;

+--------+--------+--------+--------+
| 姓名   | 星期一 | 星期二 | 星期三 |
+--------+--------+--------+--------+
| 李彦宏 |      0 | 2      | 1      |
| 马云   |      1 | 0      | 0      |
| 马化腾 |      0 | 1      | 0      |
+--------+--------+--------+--------+
3 rows in set