牛客网SQL实战

谁践踏了优雅 2021-11-10 15:58 378阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70][]

select * from employees
    where hire_date = (
    select hire_date from employees 
        order by hire_date desc limit 2,1    
    )

LIMIT m,n : 表示从第m+1条开始，取n条数据；

LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。

（1）首先需要加distinct去重。

假设 5-23（入职最晚日期）入职的有a,b,c 3人；

5-22（入职第二晚日期）入职的有d,e 2人；

5-21(入职倒数第三晚)入职的有f,g,h 3人；

5-21前入职的若干...

若 不加distinct去重，那么按照日期倒序，limit 2,1（从倒数第2行开始，取一条数据）的查询结果为 5-23

加了distinct去重，会按入职日期进行分组，多个相同入职日期会分为一组，这样limit 2,1的结果即为 5-21。

（2）外层的where条件中根据子查询查出的倒数第三晚入职的日期，就能查询出符合条件的员工信息。

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 1][]

select salaries.*, dept_manager.dept_no from salaries, dept_manager
    where dept_manager.emp_no = salaries.emp_no 
    and dept_manager.to_date ='9999-01-01' 
    and salaries.to_date ='9999-01-01'

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 2][]

select employees.last_name, employees.first_name, dept_emp.dept_no
    from dept_emp, employees
    where dept_emp.emp_no = employees.emp_no

select e.last_name, e.first_name, d.dept_no
    from dept_emp as d, employees as e
    where d.emp_no = e.emp_no

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 3][]

SELECT ep.last_name, ep.first_name, dp.dept_no 
    FROM employees ep 
    LEFT JOIN dept_emp dp
    ON ep.emp_no = dp.emp_no

注意：  
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。  
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。  
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

注意on与where有什么区别，两个表连接时用on，在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。  
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 5][]

select employees.emp_no, salaries.salary 
    from salaries, employees
    where salaries.emp_no=employees.emp_no
    and employees.hire_date = salaries.from_date
    order by employees.emp_no DESC

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 6][]

此题应注意以下四点：

1、用COUNT()函数和GROUP BY语句可以统计同一emp\_no值的记录条数

2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp\_no)的值转换为t

3、由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件

4、最后存在一个理解误区，涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可

*/\*\* 注意：* *严格来说，下一条salary高于本条才算涨幅，但本题只要出现了一条记录就算一次涨幅，salary相同可以理解为涨幅为0，salary变少理解为涨幅为负 \*\*/*

select emp_no, COUNT(emp_no) as t
    from salaries
    GROUP by emp_no
    HAVING COUNT(emp_no)>15

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 7][]

也可以用distinct，但是大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。

select salary from salaries
    where to_date='9999-01-01'
    group by salary
    order by salary DESC

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 8][]

select d.dept_no, d.emp_no, s.salary
    from dept_manager d
    Join salaries s
    ON d.to_date='9999-01-01'
    and s.to_date='9999-01-01'
    and d.emp_no=s.emp_no

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 9][]

select e.emp_no
    from employees e left join dept_manager d
    on e.emp_no = d.emp_no
    where d.emp_no is null

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 10][]

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 11][]

SELECT de.emp_no,dm.emp_no AS manager_no
    FROM dept_manager AS dm,dept_emp AS de
    WHERE de.emp_no != dm.emp_no 
    AND de.dept_no = dm.dept_no
    AND dm.to_date='9999-01-01'

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 12][]

此题思路如下：

1、先用INNER JOIN连接两张表，限制条件是两 张表的emp\_no相同，即d.emp\_no = s.emp\_no；

2、选取每个员工当前的工资水平，用d.to\_date = '9999-01-01' AND s.to\_date = '9999-01-01'作条件限制，因为此表中每条最新记录的 to\_date 都用 9999-01-01 表示；

3、用GROUP BY d.dept\_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；

4、将salaries用s代替，dept\_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary</code> <code>FROM salaries AS s INNER JOIN dept_emp As d</code> <code>ON d.emp_no = s.emp_no&nbsp;</code> <code>WHERE d.to_date =&nbsp;</code><code>'9999-01-01'</code> <code>AND s.to_date =&nbsp;</code><code>'9999-01-01'</code> <code>GROUP BY d.dept_no</code> </td> 
 </tr> 
 </tbody> 
</table>

\------------------------------------------分割线：若存在多条最大记录----------------------------------------

有同学提出疑问，如果存在多条最大记录怎么办？而 MAX 函数根据不同数据库只选择最前一条或最后一条最大记录，其余记录均被忽略。此时解法如下：

1、创建两张表，一张为maxsalary，用于存放当前每个部门薪水的最大值；另一张为currentsalary，用于存放当前每个部门所有员工的编号和薪水；

2、限定条件为两张表的 dept\_no 和 salary 相等，这样就可以找出当前每个部门所有薪水等于最大值的员工的相关信息了；

3、最后记得根据 currentsalary.dept\_no 升序排列，输出与参考答案相同的记录表。

4、以下代码虽然很长，仔细一看都是基于上面的基础解法变化而来的，中心思想就是绕开 MAX 的特性限制，运用比较的方法选出多个相同的最大值。

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 </td> 
 <td> <code>SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary</code> <code>FROM&nbsp;</code> <code>//创建maxsalary表用于存放当前每个部门薪水的最大值</code> <code>(SELECT d.dept_no, MAX(s.salary) AS salary</code> <code>FROM salaries AS s INNER JOIN dept_emp As d</code> <code>ON d.emp_no = s.emp_no&nbsp;</code> <code>WHERE d.to_date =&nbsp;</code><code>'9999-01-01'</code> <code>AND s.to_date =&nbsp;</code><code>'9999-01-01'</code> <code>GROUP BY d.dept_no) AS maxsalary,&nbsp;</code> <code>//创建currentsalary表用于存放当前每个部门所有员工的编号和薪水</code> <code>(SELECT d.dept_no, s.emp_no, s.salary&nbsp;</code> <code>FROM salaries AS s INNER JOIN dept_emp As d</code> <code>ON d.emp_no = s.emp_no&nbsp;</code> <code>WHERE d.to_date =&nbsp;</code><code>'9999-01-01'</code> <code>AND s.to_date =&nbsp;</code><code>'9999-01-01'</code> <code>) AS currentsalary</code> <code>//限定条件为两表的dept_no和salary均相等</code> <code>WHERE currentsalary.dept_no = maxsalary.dept_no</code> <code>AND currentsalary.salary = maxsalary.salary</code> <code>//最后以currentsalary.dept_no排序输出符合要求的记录表</code> <code>ORDER BY currentsalary.dept_no</code> </td> 
 </tr> 
 </tbody> 
</table>

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 13][]

//先找出emp\_no唯一的表格，再在其中查找

select title, COUNT(title) as t
    from 
    (
        select distinct emp_no,title,from_date,to_date
        from titles
    )
    group by title
    Having COUNT(title)>=2

还有其他解法

1、先用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp\_no)可以统计同一title值且不包含重复emp\_no值的记录条数

2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(DISTINCT emp\_no)的值转换为t

3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>SELECT title, COUNT(DISTINCT emp_no) AS t FROM titles</code> <code>GROUP BY title HAVING t &gt;=&nbsp;</code><code>2</code> </td> 
 </tr> 
 </tbody> 
</table>

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 14][]

select * from employees
    where emp_no % 2==1
    and last_name!='Mary'
    ORDER BY hire_date DESC

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 15][]

select title, avg(salary) as avg
    from titles t INNER Join salaries s
    On t.emp_no=s.emp_no
    where t.to_date='9999-01-01'
    and s.to_date='9999-01-01'
    group by t.title

还有其他解法

“Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

select title,avg(salary) as avg from salaries,titles where salaries.emp\_no = titles.emp\_no and salaries.to\_date='9999-01-01'  
and titles.to\_date='9999-01-01'  
group by title ;**正确**

select title,avg(salary) as avg from salaries,titles where salaries.to\_date='9999-01-01'  
and titles.to\_date='9999-01-01'  
group by title having salaries.emp\_no = titles.emp\_no ;**不正确,因为group by之后的表中只有 title，avg字段可以继续进行，having无法继续处理。其实可以理解为title为唯一ID,其他列就是一堆数，你只能对他进行统计呗。**我是这样理解的，可能有不足之处。

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 16][]

select emp_no, salary
    from salaries
    where salaries.to_date='9999-01-01'
    ORDER by salary DESC limit 1,1

上面的代码其实并不完整，你需要改进一下（**哪里不完整，比如说并列第一的，显然就需要一个消除多余salary的方法**）

select emp\_no, salary  
from salaries  
where salaries.to\_date='9999-01-01'  
and salary =  
(  
select **distinct** salary from salaries  
order by salary DESC limit 1,1  
)

避免了2个问题：

(1) 首先这样可以解决多个人工资相同的问题；

(2) 另外，筛选出第二多的工资时要注意distinct salary，否则不能选出第二多的工资。

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 17][]

本题做法很多，主要思想为多层SELECT嵌套与MAX()函数结合

1、先利用MAX()函数找出salaries中当前薪水最高者，即SELECT MAX(salary) FROM salaries WHERE to\_date = '9999-01-01'

2、再利用INNER JOIN连接employees与salaries表，限定条件为【同一员工】e.emp\_no = s.emp\_no、【当前】s.to\_date = '9999-01-01'与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to\_date = '9999-01-01')

3、在以上限制条件下找薪水最高者，即为所有员工薪水的次高者

select E.emp_no, Max(S.salary) as salary, E.last_name, E.first_name
    from employees E
    inner join salaries S
    ON S.emp_no=E.emp_no
    where to_date='9999-01-01'
    and S.salary Not in 
    (
        select Max(salary) from salaries where to_date = '9999-01-01'
    )

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 18][]

SELECT e.last_name, e.first_name, dp.dept_name FROM employees AS e
    LEFT JOIN dept_emp AS d ON e.emp_no = d.emp_no
    LEFT JOIN departments AS dp ON d.dept_no = dp.dept_no;

**为什么要用left join，而不是inner join？是因为inner join会删除空的一行，对于该题由于那些没有安排岗位的人，dept\_name必然是null，所以只能用left join。**

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 19][]

本题严谨的思路如下：

1、先分别找到emp\_no=10001的员工的第一次工资记录与最后一次工资记录

2、再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅，最后用别名growth代替

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; &nbsp; &nbsp; </td> 
 <td> <code>SELECT (&nbsp;</code> <code>(SELECT salary FROM salaries WHERE emp_no =&nbsp;</code><code>10001</code> <code>ORDER BY to_date DESC LIMIT&nbsp;</code><code>1</code><code>) -</code> <code>(SELECT salary FROM salaries WHERE emp_no =&nbsp;</code><code>10001</code> <code>ORDER BY to_date ASC LIMIT&nbsp;</code><code>1</code><code>)</code> <code>) AS growth</code> </td> 
 </tr> 
 </tbody> 
</table>

本题的另一种解法也能通过测试，但实际上不严谨，只有在员工最后一条工资记录为最大值时成立，如果最后一次的工资调整为降薪，则此思路通不过。具体思路如下：

1、直接找到emp\_no=10001的员工的工资记录，将其最大工资减去最小工资得到涨幅

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>SELECT (MAX(salary)-MIN(salary)) AS growth&nbsp;</code> <code>FROM salaries WHERE emp_no =&nbsp;</code><code>'10001'</code> </td> 
 </tr> 
 </tbody> 
</table>

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 20][]

本题思路是先分别用两次LEFT JOIN左连接employees与salaries，建立两张表，分别存放员工当前工资（sCurrent）与员工入职时的工资（sStart），再用INNER JOIN连接sCurrent与sStart，最后限定在同一员工下用当前工资减去入职工资。

**方法一：内层用LEFT JOIN，外层用INNER JOIN（内层也可以改用 INNER JOIN）**

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </td> 
 <td> <code>SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth</code> <code>FROM (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.to_date =&nbsp;</code><code>'9999-01-01'</code><code>) AS sCurrent</code> <code>INNER JOIN (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.from_date = e.hire_date) AS sStart</code> <code>ON sCurrent.emp_no = sStart.emp_no</code> <code>ORDER BY growth</code> </td> 
 </tr> 
 </tbody> 
</table>

**方法二：内外都层用FROM并列查询**

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth</code> <code>FROM (SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date =&nbsp;</code><code>'9999-01-01'</code><code>) AS sCurrent,</code> <code>(SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart</code> <code>WHERE sCurrent.emp_no = sStart.emp_no</code> <code>ORDER BY growth</code> </td> 
 </tr> 
 </tbody> 
</table>

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 21][]

select d.dept_no ,dept.dept_name, count(salary) 
    from salaries s,dept_emp d,departments dept
    where s.emp_no = d.emp_no 
    and d.dept_no=dept.dept_no 
    group by dept.dept_no

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 22][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 23][]

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 24][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 25][]

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 26][]

![20190804135344765.png][]

SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
    FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de
    ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem,
    (SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm
    ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
    WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 27][]

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 28][]

SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
    from dept_emp de INNER JOIN titles t
    ON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
    
    INNER JOIN departments dp
    ON dp.dept_no = de.dept_no 
    
    group by de.dept_no, t.title

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 29][]

这种涉及到列中变量求差之类的，最好从同一张表中，定义两个表变量

select s1.emp_no, s2.from_date, (s2.salary-s1.salary) as salary_growth
    FROM salaries AS s1, salaries AS s2
    WHERE s1.emp_no = s2.emp_no
    and s2.salary - s1.salary > 5000
    AND strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
    ORDER BY salary_growth DESC;

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 30][]

SELECT last\_name||" "||first\_name AS Name FROM employees

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 31][]

create table actor
    (
    actor_id smallint(5) NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')) -- ,
    -- PRIMARY KEY(actor_id)
    )

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 32][]

INSERT INTO actor
    values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33')

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 33][]

insert IGNORE into actor
    values(3,'ED','CHASE','2006-02-15 12:34:33');

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 34][]

create table actor_name
    (
        first_name varchar(45) not null,
        last_name varchar(45) not null
    );
    insert into actor_name select first_name,last_name from actor

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 35][]

create unique index uniq_idx_firstname on actor(first_name);
    create index idx_lastname on actor(last_name);

先用 CREATE UNIQUE INDEX ... ON ... 对first\_name创建唯一索引值，再用 CREATE INDEX ... ON ... 对last\_name创建普通索引值

PS:create unique index uniq\_idx\_firstname  
on actor(first\_name);  
create index idx\_lastname  
on actor(last\_name);

这么写是不对的，不能换行

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 36][]

create view actor_name_view (first_name_v,last_name_v) as
    select first_name,last_name from actor

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 37][]

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 38][]

alter table actor add column create_date datetime not null default '0000-00-00 00:00:00';

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 39][]

update titles_test set to_date=null,from_date='2001-01-01' where to_date='9999-01-01';

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 40][]

delete from titles_test
    where id != 
    (
        select min(id) from titles_test
    )

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 41][]

replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 42][]

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 43][]

alter table titles\_test RENAME TO titles\_2017

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 44][]

update salaries set salary=salary*1.1
    where emp_no in (
        select emp_no from emp_bonus
    )

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 45][]

![20190810203122254.png][]

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 46][]

select dept_no, group_concat(emp_no) AS employees
    FROM dept_emp GROUP BY dept_no

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 47][]

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)

**————————————————————————————————————————————————**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 48][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 49][]

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

**————————————————————————————————————————————————**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70]: /images/20211109/d43bcce61879420c96040de1f4bf730d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 1]: /images/20211109/506c42154001439b8614ca36e7f4f61e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 2]: /images/20211109/7ae4787aa70448c098a6f5cfa1c2d890.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 3]: /images/20211109/6c8ce94024c7426bb28c58dde8713ef2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 4]: /images/20211109/427a4413efb94384904fe480dc949a15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 5]: /images/20211109/b9ba60ac79fe4d4c91fb2a6fd18b12f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 6]: /images/20211109/29bde7a3059b4f098f017ccd3ca52b3d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 7]: /images/20211109/1a709f0271884a05942f5914777aff9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 8]: /images/20211109/6c2111d1989d4b78aac4a1ef946b023c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 9]: /images/20211109/920f2ddbb1b34f7f91daceee17f6cf02.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 10]: /images/20211109/ce170231f5614c27bfcdb5567437139a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 11]: /images/20211109/45678bad6e864abc97d79cb96d02702e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 12]: /images/20211109/2938f6b055ec417393251b3efc9af723.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 13]: /images/20211109/52cbf715b3bb49928f1bb9023687fe20.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 14]: /images/20211109/2548e50962134f12bf1a2c0bf84b9776.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 15]: /images/20211109/af0ebcff65124bc89c594c9927d1f1c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 16]: /images/20211109/2e734ad9c4424ea2ab61142a97da952f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 17]: /images/20211109/aebd3a762b404ab4b25b4ea9ef8391de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 18]: /images/20211109/6cb5f68b7d1e4b0f8a490b5c9a30780a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 19]: /images/20211109/665848d7d1dc4d799f3dc7a3fb295205.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 20]: /images/20211109/119f70909e324076b15bcc1f94db24cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 21]: /images/20211109/e7d43634744740daa0aad3b0e04e80b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 22]: /images/20211109/0460bc85052e48be9b69e7b74fe2090e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 23]: /images/20211109/3696d659a70f494b9f87b837e80c3cd3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 24]: /images/20211109/4b102ac24683435c89dcab4a138f07a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 25]: /images/20211109/13c3c5d34b6e4eee89274a658075b6f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 26]: /images/20211109/d9c5d41045954e56aadccac73bdace6e.png
[20190804135344765.png]: /images/20211109/5df69344d19944d0bb21d51ce05a1049.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 27]: /images/20211109/77ad9e27389e4325a5fdb1f148e005f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 28]: /images/20211109/87fe79b9afda46f6bead5b1b5262da87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 29]: /images/20211109/88f4cfb51cd047e7905994fab4a76528.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 30]: /images/20211109/9f67f6034dd44e16acc4e87f4785a86e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 31]: /images/20211109/c9da81c48c154efe99fabd8dd4fc6a30.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 32]: /images/20211109/274d48e3de744df5bc1b00af0635d289.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 33]: /images/20211109/3005c10c35514ef495e5dc25a7e283af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 34]: /images/20211109/873f8c0054114e24b2cd059ec6692c75.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 35]: /images/20211109/47e76de83f6142119ae11630c4c80fa7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 36]: /images/20211109/b6d7e5220951452a827275fef2ae50e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 37]: /images/20211109/a86bee7a402f4e5ea97ac3a2f8e485e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 38]: /images/20211109/453e2e321f9b48458f88858fa1247e5c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 39]: /images/20211109/d8247d03a0d24601a654ea4ab7374093.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 40]: /images/20211109/2274f0cd0e324a41b65bdd86f189ddf4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 41]: /images/20211109/873aa4b4cbf849ff91894860778dae75.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 42]: /images/20211109/86f73cfaf7b642cba2b13618f0609c85.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 43]: /images/20211109/f8cb2a3f9ba14bc5b4fb9349cd67e569.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 44]: /images/20211109/c66c72c57faa4db8b49744b3f065076c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 45]: /images/20211109/cc6e8e9ad1954944943d44e7f35fdab1.png
[20190810203122254.png]: /images/20211109/023fbc306dd84dbaa8079db4e264b3d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 46]: /images/20211109/7bf14562ce2e4e47add4f878443b1854.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 47]: /images/20211109/b3680f89aa2e455c8ab255b2644befe5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 48]: /images/20211109/647ed5a444b94391b1c13b4eecd0f954.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xhbXVzaXF1ZQ_size_16_color_FFFFFF_t_70 49]: /images/20211109/db2780434cd749ffad363c795ad53fd3.png