题一：查询每个商店的当月累计金额和累计的总销售额

数据源

输出结果

解答

create table sales
(shop string, month string, money int)
ROW FORMAT DELIMITED
fields terminated by ',';
load data local inpath '/home/hadoop/store.txt'
into table sales;
SELECT s.shop, s.month, s.month_sum, 
sum(s.month_sum) over(partition by s.shop order by s.month)
from(
    SELECT shop, month, sum(money) AS month_sum
    from sales group by shop, month
) AS s;

题二：查询每位同学选课结果

数据源

输出结果

解答

select 
  c.id sid,
  if(array_contains(user_courses,courses[0]),1,0) a,
  if(array_contains(user_courses,courses[1]),1,0) b,
  if(array_contains(user_courses,courses[2]),1,0) c,
  if(array_contains(user_courses,courses[3]),1,0) e,
  if(array_contains(user_courses,courses[4]),1,0) d,
  if(array_contains(user_courses,courses[5]),1,0) f
from 
(
    select a.id , a.user_courses , b.courses from 
  (
    select id , collect_set(trim(course)) as  user_courses
    from courses_tb group by id 
  ) a
  join 
  (
    select collect_set(trim(course)) as courses
    from courses_tb
  ) b
) c ;

题三：查询每一年最高气温出现的日期

数据源

输出结果

解答

-- 1
select t.year, t.maxtemp, t.datetime
from(
    select year, datetime, temp,
                max(temp) over(partition by year order by temp desc) as maxtemp
    from temp
) as t
where t.temp = t.maxtemp;
-- 2
select 
    a.year year,a.maxtemp maxtemp,substr(b.line,1,8) datetime 
from
(
  select 
      substr(line,1,4) year,max(cast(substr(line,-2,2) as int)) maxtemp 
  from temp 
  group by substr(line,1,4)
) as a 
inner join temp as b
on a.year=substr(b.line,1,4) 
and a.maxtemp=cast(substr(b.line,-2) as int);

题四：查询数学成绩大于语文成绩的学生的信息

数据源

输出结果

解答

-- 建表
-- 字段解释：id, 学号， 课程， 成绩
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sid` int(11) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
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);

-- 查询
-- 1
SELECT
    yw.sid,
    yw.yw_score yuwen,
    sx.sx_score shuxue 
FROM
    ( SELECT sid, score AS yw_score FROM course WHERE course = 'yuwen' ) AS yw
    JOIN ( SELECT sid, score AS sx_score FROM course WHERE course = 'shuxue' ) AS sx
  ON yw.sid = sx.sid 
WHERE
    yw.yw_score < sx.sx_score;
-- 2
select * from 
    (select 
    a.sid sid,sum(a.yuwen) yuwen,sum(a.shuxue) shuxue 
    from (
    select 
      sid,
      case course when "yuwen" then score else 0 end yuwen,
      case course when "shuxue" then score else 0 end shuxue 
    from course) a 
   group by a.sid) b 
where yuwen < shuxue;