这是一个系列文章,总共61题,分6期,有答案以及解题思路,并附上解题的一个思考过程。
具体题目可参考牛客网的SQL实战模块:
https://www.nowcoder.com/ta/sql?page=0
第五十一题:查找字符串'10,A,B' 中逗号','出现的次数cnt select (length("10,A,B") - length(replace("10,A,B", ",", ""))) as cnt;解题思路:①巧用length函数和replace,length函数计算字符串的长度,length("10,A,B")算出整个字符串的长度。②使用replace将 , 替换为空,那么整个字符串减少的长度等于 , 的长度,两者相减就是 , 出现的次数。 第五十二题:获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列 select first_namefrom employeesorder by substring(first_name, (length(first_name)-1), 2) asc;解题思路:使用字符串截取函数subtring(对象字符串,截取的起始位置,截取的字符数),截取出最后两位字符,然后使用order by …asc进行升序排序。 第五十三题:按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees,输出为:
dept_no | employees |
d001 | 10001,10002 |
d002 | 10006 |
d003 | 10005 |
d004 | 10003,10004 |
d005 | 10007,10008,10010 |
d006 | 10009,10010 |
select dept_no,group_concat(emp_no separator ',') as employeesfrom dept_empgroup by dept_no; 解题思路:①连接同一列字段:group_concat( [distinct] <要连接的字段> [order by 排序字段 asc/desc ] [separator '分隔符'] ) 。分隔符可以选择省略,省略时默认为逗号,这里还是写出来了。另外还有一点需要注意,group_concat函数中的各个参数之间用空格隔开,不能用逗号隔开,不然会出错。②按照dept_no进行汇总,所以要对dept_no进行分组。 第五十四题:查找排除当前最大、最小salary之后的员工的平均工资avg_salaryselect avg(salary) as avg_salaryfrom salarieswhere salary not in (select max(salary) from salaries where to_date = '9999-01-01')and salary not in (select min(salary) from salaries where to_date = '9999-01-01')and to_date = '9999-01-01';解题思路:用两个子查询找出最大工资和最小工资,再用not in去除最大工资和最小工资,最后记得限定时间为当前。 第五十五题:分页查询employees表,每5行一页,返回第2页的数据在SQLite中:select * from employees limit (2-1)*5,5;解题思路:limit分页公式:curPage是当前第几页;pageSize是一页多少条记录 limit (curPage-1)*pageSize,pageSize 解题思路:(在Mysql中)每行5页,返回第2页的数据,即返回第6~10条记录,以下有两种方法可以解决:方法一:利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录,OFFSET 后的数字代表从第几条记录开始返回(第一条记录序号为0),也可理解为跳过多少条记录后开始返回。select * from employees limit 5 offset 5; 方法二:只利用 LIMIT 关键字。注意:在 LIMIT X,Y 中,Y代表返回几条记录,X代表从第几条记录开始返回(第一条记录序号为0),切勿记反。select * from employees limit 5,5; 第五十六题:获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ,没有分配具体的员工不显示 select de.emp_no,de.dept_no,eb.btype,eb.receviedfrom dept_emp de left join emp_bonus eb on de.emp_no = eb.emp_nogroup by de.emp_no,de.dept_no; 解题思路:①将dept_emp表和emp_bonus表进行左联结,左联结原因:要获取所有员工的员工编号信息,dept_emp表有全部员工的员工编号和部门编号信息。②本题的关键在于要将员工编号和部门编号进行分组。因为emp_bonus表里的员工编号不是唯一的,dept_emp表里的员工编号不是唯一的,比如有两个员工编号为10010,但是他们分别属于两个不同的部门,所以要通过两个分组进行一对一确定。 第五十七题:使用含有关键字exists查找未分配具体部门的员工的所有信息,输出格式:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
select e.* from employees ewhere not exists(select * from dept_emp de where e.emp_no = de.emp_no); 解题思路:①重点理解谓词exists,谓词exists的作用是“判断是否存在满足某些条件的记录”,如果存在这样满足条件的记录,返回真,不存在,返回假。题目要查找未分配具体部门的员工的所有信息,那么,如果employees表的员工编号=dept_emp的员工编号,则表明该员工已经分配了部门。我们要的是没有分配部门的员工,所以在exists之前加个not。②输出格式为employees表的所有信息,所以employees表的所有列都要选,select e.* 第五十八题:存在视图create view emp_v as select * from employees where emp_no >10005;获取employees中的行数据,且这些行也存在于emp_v中。注意不能使用intersect关键字。输出格式:
emp_no | birth_date | first_name | last_name | gender | hire_date |
10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
10011 | 1953-11-07 | Mary | Sluis | F | 1990-01-22 |
emp_no | first_name | last_name | btype | salary | bonus |
10001 | Georgi | Facello | 1 | 88958 | 8895.8 |
10002 | Bezalel | Simmel | 2 | 72527 | 14505.4 |
10003 | Parto | Bamford | 3 | 43311 | 12993.3 |
10004 | Chirstian | Koblick | 1 | 74057 | 7405.7 |
--end--