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

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

第一题:

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

评论

暂无评论