【每日一题】SQL 知识大测验 | 持续更新

知识大测验

34题，我没看懂题意，这个咋解

38题 select * , case when id%2 =1 and name_2 is not null then name_2 when id%2 =0 then name_4 else name_id end as name_3 from ( select *,Lead(name_id) over(order by id) as name_2 ,Lag(name_id) over(order by id) as name_4 from test )a

insert overwrite TABLE q11 values('1','monday','chinese','Y','Y'), ('2','tuesday','math',NULL,'Y'), ('3','wednesday','chinese','Y',NULL), ('4','wednesday','math',NULL,'Y'), ('5','wednesday','english',NULL,'Y'), ('6','friday','english',NULL,NULL);

with q11_temp as ( SELECT t,str_to_map(concat_ws('&',collect_set(concat_ws('=',day,subj))),'&','=') as mp from ( SELECT day,t,concat_ws(',',collect_set(subj)) subj from ( SELECT day,'morning' t,if(morning='Y',subject,morning) subj FROM q11 union ALL SELECT day,'afternoon' t,if(afternoon='Y',subject,afternoon) subj FROM q11 ) a WHERE subj IS NOT NULL GROUP BY day,t

) b GROUP BY t

)

select t,if(array_contains(map_keys(mp),'monday'),mp['monday'],'noclass'), if(array_contains(map_keys(mp),'tuesday'),mp['tuesday'],'noclass'), if(array_contains(map_keys(mp),'wednesday'),mp['wednesday'],'noclass'), if(array_contains(map_keys(mp),'thursday'),mp['thursday'],'noclass'), if(array_contains(map_keys(mp),'friday'),mp['friday'],'noclass'), if(array_contains(map_keys(mp),'saturday'),mp['saturday'],'noclass'), if(array_contains(map_keys(mp),'sunday'),mp['sunday'],'noclass') from q11_temp

20

11题答案可以给下嘛

38

---11. 如果是oracle，想到的就是pivot行专列，listagg合并多个到一个字段内

select aaa, nvl(周一, '无课') as 周一, nvl(周二, '无课') 周二, nvl(周三, '无课') 周三, nvl(周四, '无课') 周四, nvl(周五, '无课') 周五 from (select '上午' as aaa, a.week, a.class_name from lx_11 a where a.morning = '有课' union all select '下午' as aaa, a.week, a.class_name from lx_11 a where a.afternoon = '有课') pivot(listagg( class_name, ',') within group(order by class_name) for week in('周一' as 周一, '周二' AS 周二, '周三' AS 周三, '周四' AS 周四, '周五' AS 周五));

--12

select * from lx_12 a, lx_12 b where a.redate = b.redate -1 and a.temp < b.temp

--13
--如果是Oracle就是判断row_number()、rank()、dense_rank() 的区别

select a.score, dense_rank() over(partition by a.aa order by a.score desc) from (select '1' as aa, score from lx_13) a

第11题，求思路

33

SELECT shipid, paydate, payno
FROM (
         SELECT *,
                row_number() OVER (PARTITION BY shipid ORDER BY paydate) AS rn_dt,
                row_number() OVER (PARTITION BY shipid ORDER BY payno) AS rn_no
         FROM orders
     )
WHERE rn_dt = 1
  AND rn_no = 1

胜哥

作者出的题目建表的sql能提供下吗？

35题

select sum(count) as num,id from (
	select count(*) count,requester_id as id from request_accepted GROUP BY requester_id 
UNION
	select count(*) count ,accepter_id as id from request_accepted GROUP BY accepter_id 
)t
GROUP BY id
ORDER BY num desc
limit 1

33题

1.初始化:

CREATE TABLE `Orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `pay_date` datetime DEFAULT NULL,
  `pay_no` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (1, '1001', '2020-01-11 16:43:25', 5);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (2, '1001', '2020-01-10 16:43:57', 3);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (3, '1002', '2020-01-17 16:44:09', 4);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (4, '1002', '2020-01-11 16:44:24', 2);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (5, '1002', '2020-01-11 16:44:24', 1);
INSERT INTO `Orders`(`id`, `shop_id`, `pay_date`, `pay_no`) VALUES (6, '1002', '2020-01-12 16:44:24', 0);

2.SQL

方法1：GROUP_CONCAT实现(数据库版本[5.7.16])

select * from Orders 
where id in (
select SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY pay_date,pay_no),',',1) from Orders
GROUP BY shop_id
)

方法2：子查询实现(数据库版本[5.6.16])

select t.* from (
    select * from Orders ORDER BY pay_date ,pay_no 
)t
GROUP BY t.shop_id;

方法2有可能是不行的，不知道是数据库版本问题还是哪里设置没有

3.注意: GROUP_CONCAT有长度限制，记录过多需要注意设置以下配置 SET @@global.group_concat_max_len=数字

第32题：求距离最小值的

30题：select player_id,event_date,sum(games_palyed)over(partition by event_date)as games_palyed_so_far from Activity

32. SELECT year, sum(case when mounth = 1 then amount else '' end), sum(case when mounth = 2 then amount else '' end), sum(case when mounth = 3 then amount else '' end), sum(case when mounth = 4 then amount else '' end) FROM t32 GROUP BY year ;

31 select id,name,num from (select id,name,num,sum(num) over(partition by name order by id asc) as sum1,sum(num) over(partition by name) as sum2 from q31) as t1 where sum1/sum2>0.6 group by id,name,num;

30 这题完全没有思路 老板给个思路

29?

我要答案学习一波1-28题，谢谢

#hive

1.

• CREATE table IF NOT EXISTS q1( id STRING, num STRING );

• insert into q1 values(1,1),(2,1),(3,1),(4,2),(5,1),(6,2),(7,2);

• SELECT num,count(1) c from (SELECT id,num,row_number()over(PARTITION BY num ORDER BY id) r, id-row_number()over(PARTITION BY num ORDER BY id) bz FROM q1 ) a GROUP BY num,bz having c>1

2.

• CREATE table IF NOT EXISTS q2( id STRING, time STRING );

• insert overwrite table q2 values(1,'2019/12/25 11:01'),(2,'2019/12/25 11:03'),(3,'2019/12/25 11:05'),(4,'2019/12/25 11:09'),(5,'2019/12/25 11:17'),(6,'2019/12/25 11:19'),(7,'2019/12/25 11:29'),(8,'2019/12/25 11:37');

• SELECT from_unixtime(floor(unix_timestamp(time,'yyyy/MM/dd hh:mm')/60/15)*60*15,'yyyy/MM/dd hh:mm'),count(1) FROM q2 GROUP BY floor(unix_timestamp(time,'yyyy/MM/dd hh:mm')/60/15);

3.同第一题

4. 对于相同的A列，C列相同则取该值，不同则置为1？

• CREATE TABLE q3( a STRING, b STRING, c STRING );

• insert into q3 values('aaa','1','x'),('aaa','2','y'), ('bbb','3','x'),('bbb','4','x'), ('ccc','5','y'),('ccc','6','y');

• select t.a,t.b+t2.b,if(t.c=t2.c,t.c,1) from( SELECT a,b,c FROM q3 where b%2=1 ) t left join (select a,b,c from q3 ) t2 where t.a=t2.a and t.b+1=t2.b

5.

• rank(),dense_rank()

• select project_id,employee_id,dense_rank() over(partition by project_id,employee_id order by experience_years desc) rk from (select project_id,employee_id from project) p left join (select employee_id,experience_years from employee ) e on p.employee_id=e.employee_id where rk=1

6.

• select actor_id,director_id from actorDirector group by actor_id,director_id having count(1)>=3

7.

• SELECT num from q4 GROUP BY num HAVING count(1)=1 ORDER BY num DESC LIMIT 1

8.

• create table if not exists q8( id string, starttime string, endtiem string);

• insert overwrite table q8 values(1,'08:00','09:15'),(2,'13:20','15:20'),(3,'10:00','14:00'),(4,'13:55','16:25'),(5,'14:00','17:45'),(6,'14:05','17:45'),(7,'18:05','19:45');

• SELECT DISTINCT bid FROM (SELECT id aid,starttime ast,endtiem aet from q8) a LEFT JOIN (SELECT id bid,starttime bst,endtiem bet from q8) b where (aid<>bid and unix_timestamp(ast,'hh:mm')<unix_timestamp(bst,'hh:mm') and unix_timestamp(aet,'hh:mm')>unix_timestamp(bst,'hh:mm')) or (aid<>bid and unix_timestamp(ast,'hh:mm')<unix_timestamp(bet,'hh:mm') and unix_timestamp(aet,'hh:mm')>unix_timestamp(bst,'hh:mm'))

9.

• CREATE TABLE q5( id int, name STRING );

• INSERT INTO q5 VALUES(1,'张三'),(2,'李四'),(3,'王五'),(4,'马六'),(5,'赵七');

• SELECT * from (SELECT id,lag(name,1,name)over() FROM q5) a WHERE a.id%2=1 UNION ALL SELECT * from (SELECT id,lead(name,1,name)over() FROM q5) b WHERE b.id%2=0

10.

• select a.name,b.name,c.name from (select * from city where parentid='0') a left join (select * from city where parentid='0') b
  a.id=b.parentid left join (select * from city where parentid='0') c b.id=c.parentid

25题： SELECT * FROM students_score t1 WHERE NOT EXISTS (SELECT 1 FROM students_score t WHERE t1.NAME = t.NAME AND t.SCORE < 80 GROUP BY NAME )