--源表
name,subjectid,age,score
张三,0001,22,45
张三,0002,22,56
李四,0002,18,88
赵五,0002,24,66
--目标表 (科目0001没有成绩的学生name和age)
name,age
李四,18
赵五,24
create table score_t(
name string,
subjectid string,
age string,
score string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
;
--计算
select
name,
max(age) as age
from score_t
group by name
having min(subjectid)!='0001'
;
+-------+------+--+
| name | age |
+-------+------+--+
| 李四 | 18 |
| 赵五 | 24 |
+-------+------+--+
--假如科目的值是语文和数学,求没有语文成绩的学生姓名和年龄
select
name,
max(age) as age
from
(
select
name,
age,
case when subject='语文' then 1 else 2 end as num
from score_t
) t1
group by name
having min(num)!=1
;
+-------+------+--+
| name | age |
+-------+------+--+
| 李四 | 18 |
| 赵五 | 24 |
+-------+------+--+
hivesql 练习3
最新推荐文章于 2024-04-22 13:33:52 发布