学生课程成绩
use myhive;
CREATE TABLE `course` (
`id` int,
`sid` int ,
`course` string,
`score` int
) ;
// 插入数据
// 字段解释:id, 学号, 课程, 成绩
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);
需求 :所有数学课程成绩 大于 语文课程成绩的学生的学号
//所有数学课程成绩 大于 语文课程成绩的学生的学号
//思路一 用join 把语文和数学放到一行里面
select sid ,course,score
from course t1
select sid ,course,score
from course t2
select
*
from
(
select sid ,course,score
from course
)t1
inner join
(
select sid ,course,score
from course
)t2
on
t1.sid = t2.sid
and t1.course = "yuwen"
and t2.course = "shuxue"
where t1.score < t2.score
//思路二
用case when 然后处理下 把数学和语文放到一行
select
sid,
course,
(case course when "shuxue" then score else 0 end) as shuxue,
(case course when "yuwen" then score else 0 end) as yuwen
from course ;t1
sid course shuxue yuwen
1 yuwen 0 43
1 shuxue 55 0
2 yuwen 0 77
2 shuxue 88 0
3 yuwen 0 98
3 shuxue 65 0
select
sid,
max(shuxue) s,
max(yuwen) y
from (
select
sid,
course,
(case course when "shuxue" then score else 0 end) as shuxue,
(case course when "yuwen" then score else 0 end) as yuwen
from course
)t1
group by sid
having s > y
计算每一年出现过的最大气温的日期+温度。
2014010216
2014010410
2012010609
2012010812
2012011023
2001010212
2001010411
2013010619
2013010812
2013011023
2008010216
2008010414
2007010619
2007010812
2007011023
2010010216
2010010410
2015010649
2015010812
2015011023
select
substr(data,1,8) date1,
substr(data,1,4) year1,
substr (data,9) degree
from weather ;t1
select
year1,
max(degree) max_degree
from t1
group by year1 ; t2
select
t1.date1,
t1.year1,
t1.degree
from
t1
inner join
t2
on t1.year1 = t2.year1
and t1.degree = t2.max_degree
注意 : substr(s,start,len) 起始使从1开始,后面并不是跟结束的,是长度