selecttt.user_id,
sum(snd_amt) as total_snd_amt,
sum(rcv_amt) as total_rcv_amt,
sum(rcv_amt) -sum(snd_amt) as net_amt
from (
select 
snd_usr_id as user_id,
sum(pkt_amt) as snd_amt,
0 as rcv_amt
from tx_red_pkt_rcd
group by snd_usr_id
 union all 
 select 
rcv_usr_id as user_id,
0 as snd_amt,
sum(pkt_amt) as rcv_amt
from tx_red_pkt_rcd
	group by rcv_usr_id
) tt
group by tt.user_id
;

with tmp as (
select 
s1.student_id,
s1.name,
s2.score,
rank() over(order by s2.score desc) as rnk
from students s1 join scores s2 
	on s1.student_id = s2.student_id
where s1.grade_code = 'S1'
and s2.subject = '物理'
)
select
student_id,
name,
score,
rnk
from tmp 
where rnk <10
order by rnk;

with tmp as (
select 
s1.student_id,
s1.name,
s2.score,
rank() over(order by s2.score desc) as rnk
from students s1 join scores s2 
	on s1.student_id = s2.student_id
where s1.grade_code = 'S1'
and s2.subject = '物理'
)
select
student_id,
name,
score,
rnk
from tmp 
limit 10
;

with tmp_rank as(
select 
s1.student_id
,s1.name
,s2.score 
,row_number()over(order by s2.score desc) as rnk
from 
	students s1 
join 
	scores s2 on s1.student_id = s2.student_id
	where s1.grade_code = 'S1' 
	and s2.subject= '物理'
)
select 
	t.student_id,
t.name,
t.score,
t.rnk
from tmp_rank t
where t.rnk <=10
order by rnk,student_id;

with tmp_rank as(
select 
s1.student_id
,s1.name
,s2.score 
,row_number()over(order by s2.score desc) as rank_num
from 
	students s1 
join 
	scores s2 on s1.student_id = s2.student_id
	where s1.grade_code = 'S1' 
	and s2.subject= '物理'
)
select 
	t.student_id,
t.name,
t.score,
t.rank_num
from tmp_rank t
where t.rank_num <=10
order by rank_num,student_id;

with tmp_rank as(
select 
s1.student_id
,s1.name
,s2.score 
,row_number()over(order by s2.score desc) as rank_num
from 
	students s1 
join 
	scores s2 on s1.student_id = s2.student_id
	where s1.grade_code = 'S1' 
	and s2.subject= '物理'
)
select 
	t.student_id,
t.name,
t.score,
t.rank_num
from tmp_rank t
where t.rank_num <=10;

with tmp_rank as(
select 
s1.student_id
,s1.name
,s2.score 
,row_number()over(order by s2.score desc) as rank_num
from 
	students s1 
join 
	scores s2 on s1.student_id = s2.student_id
	and s1.grade_code = 'S1' 
	and s2.subject= '物理'
)
select 
	t.student_id,
t.name,
t.score,
t.rank_num
from tmp_rank t
where t.rank_num <=10;

select 
s1.student_id
,s1.name
,s2.score
,row_number()over(order by s2.score desc) as rank_num
from students s1 join scores s2 on s1.student_id = s2.student_id
and s1.grade_code = 'S1' and s2.subject= '物理'
limit 10;

select 
s1.student_id
,s1.name
,s2.score
,row_number()over(order by s2.score desc) as rank_num
from students s1 left join scores s2 on s1.student_id = s2.student_id
and s1.grade_code = 'S1' and s2.subject= '物理'
limit 10;

select 
left(s.name,1) as first_name ,count(*) as num
from students s
group by first_name
order by num desc
limit 5;

select 
left(s.name,1) as first_name ,count(*) as num
from students s
group by first_name
order by num
limit 5;

select 
s.name,s.class_code,s.grade_code,t.name as head_teacher_name
from students s leftjoin teachers t on s.class_code = t.head_teacher
order by s.student_id;

select 
s.name,s.class_code,s.grade_code,t.name as head_teacher_name
from students s join teachers t on s.class_code = t.head_teacher
order by s.student_id;

select 
s.name,s.class_code,s.grade_code,t.head_teacher
from students s join teachers t on s.class_code = t.head_teacher
order by s.student_id;

select 
s.name,s.class_code,s.grade_code,t.head_teacher
from students s left join teachers t on s.class_code = t.head_teacher
order by s.student_id;

select 
s.student_id,s.name,s.class_code,s.grade_code,t.head_teacher
from students s join teachers t on s.class_code = t.head_teacher
order by s.student_id;

select 
t1.student_id,
t1.class_code,
t1.grade_code,
t2.name
from students t1join teachers t2 on t1.class_code= t2.head_teacher 
order by t1.student_id;

select 
t1.student_id,
t1.class_code,
t1.grade_code,
t2.name
from students t1 left join teachers t2 on t1.class_code= t2.head_teacher 
order by t1.student_id;