学生表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