SQL每日一题1.14

SQL每日一题1.14

SQL中的每日一题

元数据

 

问题一：按a分组取字段b最小时对应的c字段

输出结果：

 

--步骤一 按照从小到大的顺序排序 
select a,b,c,row_number() over(partition by a order by b) rn 
from test_1 
--步骤二 排序后找到第一个，第一个就是最小的 
select a,c from (select a,b,c,row_number() over(partition by a order by b) rn from test_1) t where t.rn = 1

 

 

问题二：按照a分组取b字段排第二时对应的c字段

输出的结果

#问题二 按照a分组取b字段排第二时对应的c字段 
--步骤一 窗口函数进行排序 
select a,b,c,
row_number() over(partition by a order by b) rn 
from test_1 
--步骤二 排序完后，找到排名为第二的 
select a,c 
from (select a,b,c,row_number() over(partition by a order by b) rn
from test_1) t
where t.rn = 2

 

 

问题三：按a分组取b字段最小和最大时对应的c字段

要求：

#问题三 按a分组取b字段最小和最大时对应的c字段 
--步骤一 
select a,b,c,
row_number() over(partition by a order by b) rn,
row_number() over(partition by a order by b) nn
from test_1 
--步骤二 
select a,
min(if(t.rn=1,c,null)) min_c,
max(if(t.nn=1,c,null)) max_c 
from (select a,b,c,row_number() over(partition by a order by b) rn,
row_number() over(partition by a order by b desc) nn from test_1 ) t 
where t.rn = 1 or t.nn = 1 

问题四：按a分组取b字段第二小和第二大时对应的c字段

--步骤一 
select a,b,c,
row_number() over(partition by a order by b) rn,
row_number() over(partition by a order by b desc) nn 
from test_1 
--步骤二 
select t.a,
min(case when t.rn = 2 then c else null end) min_c,
max(case when t.nn = 2 then c else null end) max_c 
from (select a,b,c,row_number() over(partition by a order by b) rn,
row_number() over(partition by a order by b desc) nn 
from test_1) t 
where t.rn = 2 or t.nn = 2
group by t.a