@(跃迁之路)专栏
叨叨两句
- 技术的精进不能只是简单的刷题,而应该是不断的“刻意”练习
- 该系列改版后正式纳入【跃迁之路】专栏,持续更新
刻意练习——MySQL
2018.04.02
题目描述
DROP TABLE IF EXISTS test1
;
CREATE TABLE test1
(id
int(11) NOT NULL AUTO_INCREMENT,username
varchar(20) NOT NULL,course
varchar(20) NOT NULL,score
bigint(20) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO test1
VALUES ('1', '张三', '数学', '34');
INSERT INTO test1
VALUES ('2', '张三', '语文', '44');
INSERT INTO test1
VALUES ('3', '张三', '英语', '54');
INSERT INTO test1
VALUES ('4', '李四', '数学', '134');
INSERT INTO test1
VALUES ('5', '李四', '语文', '144');
INSERT INTO test1
VALUES ('6', '李四', '英语', '154');
INSERT INTO test1
VALUES ('7', '王五', '数学', '234');
INSERT INTO test1
VALUES ('8', '王五', '语文', '244');
INSERT INTO test1
VALUES ('9', '王五', '英语', '254');
查出以下结果
法1
SELECT
A.username,A.score as '数学',B.score as '语文',C.score as '英语'
FROM
(select username,course,score from test1 where course = '数学') A,
(select username,course,score from test1 where course = '语文') B,
(select username,course,score from test1 where course = '英语') C
WHERE
A.username = B.username
and B.username = C.username
法2【推荐】
select
username,sum(case course when '数学' then score else 0 end ) as '数学',
sum(case course when '语文' then score else 0 end ) as '语文',
sum(case course when '英语' then score else 0 end ) as '英语'
FROM
test1
group by username
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。