美团数据开发面试题sql

1，a表先内连接筛选出同id下最新的数据 2，new_a表全连接b表，如果是MySQL，你需要a左连b union b左连a 3，查id，name，code 4，order by id；

这很简单吧 把文件a和文件b进行一个全连接，按id分组取最新时间的数据最后再展示出id，name，code不就可以了么

SELECT COALESCE(New_a.id,New_b.id) id ,New_a.name,New_b.code FROM (SELECT id,name FROM a a_1 WHERE NOT EXISTS(SELECT * FROM a a_2 WHERE a_1.id=a_2.id AND a_1.dt<a_2.dt)) New_a FULL JOIN (SELECT id,code FROM b b_1 WHERE NOT EXISTS(SELECT * FROM b b_2 WHERE b_1.id=b_2.id AND b_1.dt<b_2.dt)) New_b ON New_a.id=New_b.id ORDER BY id

select * FROM(SELECT a.id,a.name,t2.code from (SELECT id,max(name) as name,MAX(dt) as dt from t1 GROUP BY id) a left join t2 on a.id=t2.id ) x UNION SELECT * FROM (SELECT t2.id,a.name,t2.code from t2 left join (SELECT id,max(name) as name,MAX(dt) as dt from t1 GROUP BY id) a on t2.id=a.id) y;

with new_a as ( select * from a where (id,dt) in ( select id ,max(dt) from a group by id) ) select new_a.id, new_a.name, code from new_a full join b on new_a.id = b.id order by new_a.id 这样可以吗？

select *,row_number() over(partition by id order by dt ) as r from file_a; select * from () as a where r =1 ; select coalesce(a.id,b.id) as id ,a.name,b.code from table1 as a full join table2 as b on a.id = b.id order by id ;

python 我会 pandas 用 merge ，sql 怎么做我还真不会

请问这个平台上可以测试代码吗

t2表应该用窗口函数取吧

我靠今天面试碰到了😂估计面的一个人

with T1 AS (select id,max(dt) as newdt from a group by id), T2 AS (select id,name from a where dt in (select newdt from T1)), T3 AS (select id,null code,name from T2), T4 AS (select id,code,null name from b), T5 AS (select * from T3 union all select * from T4) select id,max(name) name,max(code) code from T5 group by id order by id

这是一面吗