1. 有一张学生成绩表(Grade),含有Student,Subject,Grade1三个字段,其中Subject共有五门,Grade1 >= 60 为及格,其中存在着缺考情况,若缺考则表里会少一条数据,比如小明五门课都考了,则表里他有五条数据,小华缺考了两门,则表里只有他三条数据,缺考则该科目不及格,所有科目全部及格的算优秀,用sql查出优秀的有多少人
-- 子查询先查出来五门课全部及格的都有谁,然后得出人数,这里having是关键
-- 注意having 后面不能跟别名
select count(1)
from (select student, count(1) as c1
from Grade a
where a.grade1 >= 60
group by student
having count(1) = 5);
2. 在大数据平台里数据需要汇总整合,最大的需求就是找出最新数据,平台表虽无主键属性,但有主键数据。有这么一张表jour,字段分别是person,xl,sj。person+sj是唯一的,xl是序列是递增的,越新的数据xl越大,请选出每个人的最新数据
-- 要选出最新数据,就需要按主键划分组,给每一组的数据按新旧排序,最新的为1,所以数据选rn=1的就是最新
-- concat是拼接,复合主键常用,cast是类型强转,转成数字型方便排序
-- 关键是row_number函数,要学会组合使用
select t.person, t.xl, t.sj
from (select person,
xl,
sj,
row_number() over(partition by concat(person, sj) order by cast(xl as decimal) desc) as rn
from jour
) t
where t.rn = 1
3. 表A结构如下,要求编写SQL语句查询20061231对应的上一个工作日日期
字段名称 字段类型 中文描述 说明 bgdate int 日期 主键 year int 年 month int 月 day int 日 flag int 工作日标记 1表示工作日,0表示非工作日
-- 由于都是int,可用max函数
select max(bgdate)
from A
where flag = 1
and bgdate < 20061231
4. 表Table里有两个字段日期(date)和当天的金额(je),要求求出来20180801-20180831期间的每一天的当月累计金额
-- 这个sql很有技术含量,需要求每一天的当月累计金额,所以可以采取内外嵌套join
-- 也就是子查询与外查询进行join关联,来查询,放到语言里就是两个嵌套for循环
-- 当然如果一时想不起来,也可以笨方法,用union一个个的sum加下去
select date1,
je,
(select sum(je) from Table a where a.date1 <= t.date1) sumje
from Table t
where t.date1 <= 20180831
and t.date1 >= 20180801
5. 表F的字段有id(订单号,主键),ef(有效期始,主键),et(有效期止),要求编写sql语句查询同一订单的有效期始与有效期止存在交叉的订单号,举例说明:
id ef et 001 20180110 20180131 001 20180201 20180303 002 20180301 20180331 002 20180331 20180422 那么这个002就是要选取的订单号
-- 内连接与between是关键
select id
from F f1
inner join F f2
on f1.orderno = f2.orderno
where f1.ef <> f2.ef
and f1.ef between f2.ef and f2.et
6. 经典行转列:有一张表T,字段有name,subject,score,数据例如 小明,语文,30;小明,数学,30;小明,英语,30; 转换成为 name,语文,数学,英语, 即 小明,30,30,30
-- 遍历值,这里max是关键
select name,
max(case subject
when '语文' then
score
else
0
end) as 语文,
max(case subject
when '数学' then
score
else
0
end) as 数学,
max(case subject
when '英语' then
score
else
0
end) as 英语
from T
group by name;