牛客數(shù)據(jù)庫SQL實戰(zhàn)題(51-61題)
51闷旧、查找字符串'10,A,B' 中逗號','出現(xiàn)的次數(shù)cnt
參考：帕子迹客討論區(qū)
由于 SQLite 中沒有直接統(tǒng)計字符串中子串出現(xiàn)次數(shù)的函數(shù)算撮，因此本題用length()函數(shù)與replace()函數(shù)的結(jié)合靈活地解決了統(tǒng)計子串出現(xiàn)次數(shù)的問題，屬于技巧題纹磺，即先用replace函數(shù)將原串中出現(xiàn)的子串用空串替換帖烘，再用原串長度減去替換后字符串的長度，最后除以子串的長度（本題中此步可省略橄杨，若子串長度大于1則不可拭刂ⅰ）。
select (length("10,A,B")-length(replace("10,A,B",",","")))/length(",") AS cnt;
52式矫、獲取Employees中的first_name
查詢按照first_name最后兩個字母乡摹，按照升序進(jìn)行排列。
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 |
答案
參考：挪勺客討論區(qū)
本題考查 substr(X,Y,Z) 或 substr(X,Y) 函數(shù)的使用聪廉。其中X是要截取的字符串。Y是字符串的起始位置（注意第一個字符的位置為1故慈，而不為0）板熊，取值范圍是±(1~length(X))。
當(dāng)Y等于length(X)時察绷，則截取最后一個字符干签；
當(dāng)Y等于負(fù)整數(shù)-n時，則從倒數(shù)第n個字符處截取拆撼。
Z是要截取字符串的長度容劳，取值范圍是正整數(shù)喘沿，若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進(jìn)行匯總
屬于同一個部門的emp_no按照逗號進(jìn)行連接窄赋，結(jié)果給出dept_no以及連接出的結(jié)果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的聚合函數(shù)group_concat(X,Y)楼熄，其中X是要連接的字段寝凌，Y是連接時用的符號，可省略孝赫，默認(rèn)為逗號。此函數(shù)必須與 GROUP BY 配合使用红符。
select dept_no, group_concat(emp_no) as employees
from dept_emp
group by dept_no;
54青柄、查找排除當(dāng)前最大、最小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 |
答案
本題有點問題预侯，本題能通過的答案在挑選當(dāng)前最大致开、最小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頁的數(shù)據(jù)
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以及對應(yīng)的bonus類型btype和recevied皆撩，沒有分配具體的員工不顯示
此題的原題給的表多了兩個不相關(guān)的表扣墩，少了一個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扛吞、使用含有關(guān)鍵字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中的行數(shù)據(jù)，且這些行也存在于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中的行數(shù)據(jù)滥比，且這些行也存在于emp_v中亚脆。注意不能使用intersect關(guān)鍵字。
select em.*
from employees as em, emp_v as ev
where em.emp_no = ev.emp_no
59守呜、獲取有獎金的員工相關(guān)信息
給出emp_no型酥、first_name山憨、last_name、獎金類型btype弥喉、對應(yīng)的當(dāng)前薪水情況salary以及獎金金額bonus郁竟。 bonus類型btype為1其獎金為薪水salary的10%，btype為2其獎金為薪水的20%由境，其他類型均為薪水的30%棚亩。 當(dāng)前薪水表示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、統(tǒng)計salary的累計和running_total
按照salary的累計和running_total虏杰，其中running_total為前兩個員工的salary累計和讥蟆，其他以此類推。 具體結(jié)果如下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 |
答案
復(fù)用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表中，給出奇數(shù)行的first_name
本題表述有問題笛钝，本題的意思是將firstname進(jìn)行排序质况，選擇排序后行號為奇數(shù)的行，輸出的時候是原表的相對順序玻靡。
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
結(jié)尾
如果您發(fā)現(xiàn)我的文章有任何錯誤结榄，或?qū)ξ业奈恼掠惺裁春玫慕ㄗh，請聯(lián)系我囤捻！如果您喜歡我的文章臼朗，請點喜歡~*我是藍(lán)白絳，感謝你的閱讀蝎土！
