美文网首页数据库SQL实战
数据库SQL实战｜SQL答案集合及解析(51-61)

数据库SQL实战｜SQL答案集合及解析(51-61)

作者: 蓝白绛 | 来源:发表于2019-04-14 01:02 被阅读78次

    牛客数据库SQL实战题(51-61题)

    51、查找字符串'10,A,B' 中逗号','出现的次数cnt

    参考：牛客讨论区
    由于 SQLite 中没有直接统计字符串中子串出现次数的函数，因此本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度，最后除以子串的长度（本题中此步可省略，若子串长度大于1则不可省）。

    select (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt;

    52、获取Employees中的first_name

    查询按照first_name最后两个字母，按照升序进行排列。

    CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

    输出格式：

    first_name
    Chirstaion
    Tzvetan
    Bezalel

    答案

    参考：牛客讨论区
    本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))。
    当Y等于length(X)时，则截取最后一个字符；
    当Y等于负整数-n时，则从倒数第n个字符处截取。
    Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；
    若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

    select first_name from employees 
order by substr(first_name, length(first_name)-1)

    或

    select first_name from employees 
order by substr(first_name, -2)

    53、按照dept_no进行汇总

    属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees。

    CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

    输出格式：

    dept_no employees
    d001 10001,10002
    d002 10006

    答案

    此题要用SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。

    select dept_no, group_concat(emp_no) as employees
from dept_emp
group by dept_no;

    54、查找排除当前最大、最小salary之后的员工的平均工资avg_salary

    CREATE TABLE salaries ( 
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

    输出格式：

    avg_salary
    69462.5555555556

    答案

    本题有点问题，本题能通过的答案在挑选当前最大、最小salary时没加to_date = '9999-01-01'作条件限制，所以挑选出来的是全表最大、最小salary，然后对除去这两个salary再作条件限制to_date='9999-01-01'，求平均薪水。

    select avg(salary) as avg_salary
from salaries
where to_date='9999-01-01'
and salary not in (select min(salary) from salaries)
and salary not in (select max(salary) from salaries)

    55、分页查询employees表，每5行一页，返回第2页的数据

    CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

    答案

    用limit：

    select *
from employees
limit 5,5

    用limit和offset（offset表示跳过多少条记录）：

    select *
from employees
limit 5
offset 5

    56、获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示

    此题的原题给的表多了两个不相关的表，少了一个emp_bonus表。下面是更换过的表：

    CREATE TABLE dept_emp ( 
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

    输出格式：

    e.emp_no dept_no btype received
    10001 d001 1 2010-01-01
    10002 d001 2 2010-10-01
    10003 d004 3 2011-12-03
    10004 d004 1 2010-01-01
    10005 d003
    10006 d002
    10007 d005
    10008 d005
    10009 d006
    10010 d005

    答案

    select em.emp_no, de.dept_no, eb.btype, eb.recevied
from employees as em
join dept_emp as de
on em.emp_no=de.emp_no
left join emp_bonus as eb
on de.emp_no=eb.emp_no

    57、使用含有关键字exists查找未分配具体部门的员工的所有信息

    CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

    答案

    select *
from employees
where not exists (select emp_no 
                  from dept_emp 
                  where emp_no = employees.emp_no)

    58、获取employees中的行数据，且这些行也存在于emp_v中

    存在如下的视图：

    create view emp_v as select * from employees where emp_no >10005;

CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

    获取employees中的行数据，且这些行也存在于emp_v中。注意不能使用intersect关键字。

    select em.* 
from employees as em, emp_v as ev 
where em.emp_no = ev.emp_no

    59、获取有奖金的员工相关信息

    给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date='9999-01-01'

    CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);

CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL, 
PRIMARY KEY (emp_no,from_date));

    输出格式：

    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

    答案

    使用case语句：

    select em.emp_no, em.first_name, em.last_name, eb.btype, sa.salary,
(case eb.btype 
 when 1 then sa.salary*0.1
 when 2 then sa.salary*0.2
 else sa.salary*0.3
 end) as bonus
from employees as em
join salaries as sa
on em.emp_no=sa.emp_no and sa.to_date='9999-01-01'
join emp_bonus as eb
on em.emp_no=eb.emp_no

    60、统计salary的累计和running_total

    按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

    CREATE TABLE salaries ( 
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

    输出格式：

    emp_no salary running_total
    10001 88958 88958
    10002 72527 161485
    10003 43311 204796

    答案

    复用salaries表

    select sa1.emp_no, sa1.salary, sum(sa2.salary)
from salaries as sa1 
join salaries as sa2 
on sa2.emp_no<=sa1.emp_no
and sa1.to_date='9999-01-01' 
and sa2.to_date='9999-01-01' 
group by sa1.emp_no

    61、对于employees表中，给出奇数行的first_name

    本题表述有问题，本题的意思是将firstname进行排序，选择排序后行号为奇数的行，输出的时候是原表的相对顺序。

    CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

    输出格式：

    first_name
    Georgi
    Chirstian
    Anneke

    答案

    select e1.first_name 
from employees e1
where (select count(*) 
       from employees e2
       where e1.first_name <=e2.first_name)%2=1;

    或

    select e1.first_name
from (select e2.first_name, 
             (select count(e3.first_name)
              from employees as e3
              where e2.first_name>=e3.first_name) as rowid
      from employees as e2) as e1
where rowid%2=1

    结尾

    如果您发现我的文章有任何错误，或对我的文章有什么好的建议，请联系我！如果您喜欢我的文章，请点喜欢~*我是蓝白绛，感谢你的阅读！

    相关文章

      网友评论

        本文标题：数据库SQL实战｜SQL答案集合及解析(51-61)

        本文链接：https://www.haomeiwen.com/subject/gmfdwqtx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        数据库SQL实战

        关于我们|服务条款|联系我们|数据库SQL实战｜SQL答案集合及解析(51-61)|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！