当前位置：网站首页>Byte warehouse intern interview SQL questions

Byte warehouse intern interview SQL questions

2022-04-23 12:57:00 【Look at the data at the top of the mountain】

Subject requirements

id cnt url
1 12 a
2 21 f
1 32 e

Related instructions ：

cnt Equivalent to date

Here, you need to split the table into the following types

id Maximum date url Minimum date url
1 32 e 12 a

 The brain suddenly became hot , A little nervous in front of the interviewer , This problem has not been worked out , After the interview, I reviewed the question again , Concrete sql The statement is as follows 

select 
t3.id,t3.cnt,t3.url,t4.cnt,t4.url
from
(select 
t1.*
from
(select 
id,cnt,url,row_number() over(partition by id order by cnt) temp
from info) t1
where t1.temp <2 ) t3
join
(select 
t2.*
from
(select 
id,cnt,url,row_number() over(partition by id order by cnt desc) temp
from info) t2
where t2.temp <2) t4
on t3.id = t4.id

 The main point of this problem is to eliminate the redundant columns , Only the largest and smallest columns are reserved in the table .

版权声明
本文为[Look at the data at the top of the mountain]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204230615025321.html

边栏推荐

猜你喜欢

随机推荐