1112周末作业

学生表stu和成绩表score
使用sql查询每个学生的平均成绩并从高到低排序，若存在学生在表stu中无其考试记录的情况，其平均成绩默认为零

学生表stu

sid	name
001	张三
002	李四
003	lisa
004	jerry
005	tom

成绩表score

sid	gid	grade
001	a	90
002	a	80
003	b	95
002	c	60
004	a	70
002	b	100

期望结果

sid	name	avg_grade
001	张三	90
002	李四	80
003	lisa	95
004	jerry	70
005	tom	0

建表语句

create table stu(
                 sid   varchar2(10) --学号
                 ,name varchar2(10) --姓名
                );

insert into stu values('001','张三' );
insert into stu values('002','李四' );
insert into stu values('003','lisa' );
insert into stu values('004','jerry' );
insert into stu values('005','tom' );
commit;

create table score(
                   sid    varchar2(10) --学号
                   ,gid   varchar2(4)  --科目
                   ,grade number(4)    --成绩
                  );
 
insert into score values('001','a',90); 
insert into score values('002','a',80);
insert into score values('003','b',95);
insert into score values('002','c',60);
insert into score values('004','a',70);
insert into score values('002','b',100); 
commit;
              
select * from stu;
select * from score;

/*
左连接
以学生表stu为主表
分组
注意：空值转换
*/
select t1.sid
      ,t1.name  
      ,nvl(avg(t2.grade), 0) as avg_grade  
from stu t1
left join score t2
       on t1.sid = t2.sid
group by t1.sid, t1.name  
order by  nvl(avg(t2.grade), 0) desc;

根据余额拉链历史表acct，使用sql计算每个账户在[2020/1/1, 2020/3/31]期间的利息=bal×rate×days÷360

acct_id	bal	rate	st	et
账号	余额	利率	开始日期	结束日期
001	500	0.012	20190101	20191215
001	1000	0.015	20191215	20200116
001	2000	0.015	20200116	30001231
002	1500	0.015	20191231	20200126
002	10000	0.015	20200126	20200306
002	900	0.015	20200306	30001231

建表语句

create table acct(
                  acctid varchar2(10) --账号
                  ,bal   number(20)   --余额
                  ,rate  number(7,4)  --利率
                  ,st    date         --开始日期
                  ,ed    date         --结束日期  
                 );

insert into acct values('001','500','0.012',to_date('20190101','yyyymmdd'),to_date('20191215','yyyymmdd'));
insert into acct values('001','1000','0.015',to_date('20191215','yyyymmdd'),to_date('20200116','yyyymmdd'));
insert into acct values('001','2000','0.015',to_date('20200116','yyyymmdd'),to_date('30001231','yyyymmdd'));
insert into acct values('002','1500','0.015',to_date('20191231','yyyymmdd'),to_date('20200126','yyyymmdd'));
insert into acct values('002','10000','0.015',to_date('20200126','yyyymmdd'),to_date('20200306','yyyymmdd'));
insert into acct values('002','900','0.015',to_date('20200306','yyyymmdd'),to_date('30001231','yyyymmdd'));
commit;

select * from acct;

/*
先计算出在 20200101 到 20200331 期间的数据
再重新定义 每条数据的开始 结束日期 
最后套计算公式
*/
select  t1.acctid
      ,round(sum(t1.bal * t1.rate *(t1.end_date - t1.start_date)/360),4) as sum_rate_amt
from (
      select t.acctid,
             t.bal,
             t.rate,
             t.st,
             t.et
             ,case when t.st < to_date(20200101,'yyyymmdd') then  to_date(20200101,'yyyymmdd')
                   else t.st  end as start_date
             ,case when t.et > to_date(20200331,'yyyymmdd') then to_date(20200331,'yyyymmdd')+1 --【注意】结束日期要+1，最后这天算在内
                  else t.et end end_date    
      from acct t 
      where t.st <= to_date(20200331,'yyyymmdd')
      and t.et >=  to_date(20200101,'yyyymmdd')
     ) t1 
group by t1.acctid;

查询出互为好友的数据

user_id	friend_id
用户编号	好友编号
1	2
2	1
3	4
4	3
4	1

/*
自联结
t1.friend_id = t2.user_id
t1.user_id = t2.friend_id
调整数据 case when 
去重数据 group by 
*/
with tmp as 
(
select 1 user_id,2 friend_id from dual 
union all
select 2 user_id,1 friend_id from dual 
union all
select 3 user_id,4 friend_id from dual 
union all
select 4 user_id,3 friend_id from dual 
union all
select 4 user_id,1 friend_id from dual 
)
--select t1.user_id
       --,t1.friend_id
select case when t1.user_id < t1.friend_id then t1.user_id 
                 else t1.friend_id end as u_id1
      ,case when t1.friend_id < t1.user_id then t1.user_id 
             else t1.friend_id  end as u_id2
from tmp t1---本人的数据
inner join tmp t2---好友的数据
        on t1.friend_id = t2.user_id
       and t1.user_id = t2.friend_id ---本人的id = 好友的好友id 
group by case when t1.user_id < t1.friend_id then t1.user_id 
                 else t1.friend_id end 
      ,case when t1.friend_id < t1.user_id then t1.user_id 
             else t1.friend_id  end