大厂SQL面试常考知识点总结,附真题!

-- 从table_1中选择a这一列select a from table_1

-- table_1中有id,age; table_2中有id，sex。想取出id,age,sex 三列信息-- 将table_1,table_2 根据主键id连接起来select a.id,a.age,b.sex from(select id,age from table_1) a --将select之后的内容存为临时表ajoin(select id, sex from table_2) b --将select之后的内容存为临时表bon a.id =b.id

-- 不去重，合并两张表的数据select * from(select id from table_1UNION ALLselect id from table_2)t;

-- 罗列不同的idselect distinct id from table_1
-- 统计不同的id的个数select count(distinct id) from  table_1-- 优化版本的count distinctselect count(*) from(select distinct id from table_1) tb

-- 统计不同性别（F、M）中，不同的id个数select count(distinct id) from table_1group by sex-- 其它的聚合函数例如：max/min/avg/sum
-- 统计最大/最小/平均年龄select  max(age), min(age),avg(age) fromtable_1group by id

-- 统计A公司的男女人数select count(distinct id) from table_1where company = 'A'group by sex
-- 统计各公司的男性平均年龄，并且仅保留平均年龄30岁以上的公司select company, avg(age) from table_1where sex = 'M'group by companyhaving avg(age)>30;

-- 按年龄全局倒序排序取最年迈的10个人select id,age from table_1 order by age DESClimit 10

-- 收入区间分组select id,(case when CAST(salary as float)<50000 Then '0-5万'when CAST(salary as float)>=50000 and CAST(salary as float)<100000 then '5-10万'when CAST(salary as float) >=100000 and CAST(salary as float)<200000 then '10-20万'when CAST(salary as float)>200000 then '20万以上'else NULL endfrom table_1;

select concat('www','.iteblog','.com') fromiteblog;

-- 以","为分隔符分割字符串，并转化为arraySelect split("1,2,3",",")as value_array from table_1;
-- 结合array index,将原始字符串分割为3列select value_array[0],value_array[1],value_array[2] from(select  split("1,2,3",",")as value_array from table_1 )t

select substr('abcde',3,2) fromiteblog;
-- 得到cd

-- 按照字段salary倒序编号select *, row_number() over (order by salary desc) as row_num from table_1;
-- 按照字段deptid分组后再按照salary倒序编号select *, row_number() over (partition by deptid order by salary desc) as rank from table_1;

-- 获取income字段的top10%的阈值select percentile(CAST (salary AS int),0.9)) as income_top10p_threshold from table_1;
-- 获取income字段的10个百分位点select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentilesfrom table_1;

-- 转换为时间数据的格式select to_date("1970-01-01 00:00:00") as start_time from table_1;
-- 计算数据到当前时间的天数差select datediff('2016-12-30','2016-12-29');-- 得到 "1"