SQL 练习LeetCode SQL 基础该表没有主键，可能存在重复项。该表包含 from_id 与 to_id 间的

知识点

group 跟 select

如果在有group by操作中，select后面接的结果集字段只有两种：要么就只有group by后出现的字段，要么就是group by后出现的字段＋聚合函数的组合（常用的五种聚合函数：min()求列中最小数值，max()求列中最大数值，avg()求平均值，sum()求列中字段对应数值的总和，count()求列的总条数）

在mysql当中怎么计算两个日期的“年差”，差了多少年？

TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

timestampdiff(YEAR, hiredate, now())
  间隔类型：
	SECOND   秒，
	MINUTE   分钟，
	HOUR   小时，
	DAY   天，
	WEEK   星期
	MONTH   月，
	QUARTER   季度，
	YEAR   年

字串

instr(conditions, ' DIAB1')

union和union all的用法

将两个select的结果作为一个整体显示出来。

满足条件： 1、两个select查询的列的数量必须相同； 2、每个列的数据类型需要相似；

select * from user1    |       select * from user1
	union 	       |	       union all
select * from user2    |       select * from user2

union all是将两个select语句的结果求并集。 union是将union all的结果下再去除重复数据

limit 1 offset 3

没用值会返回null
判断年份在某一年 DATE_FORMAT(sale_date, "%Y") = 2020)

LeetCode

模板

题目：

题解：

LC 1. GROUP_CONCAT

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。每个日期的销售产品名称应按词典序排列。返回按 sell_date 排序的结果表。

输入：
Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+

输出：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+

select  sell_date, 
	count(distinct product ) as num_sold, 
	GROUP_CONCAT(distinct product) as products
from 
    Activities 
group by sell_date
order by sell_date ;

LC 2. 行转列（横表转竖表）

一列一列处理：把“列名”做为新列的value（如本题的store），把原来的value也作为新列（如本题的price），这是一个查询，其他列不要
用union all拼接每一列的结果
注意本题如果这一产品在商店里没有出售，则不输出这一行，所以要原列 is not null的筛选条件

输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
解释：
产品0在store1，store2,store3的价格分别为95,100,105。
产品1在store1，store3的价格分别为70,80。在store2无法买到。

select product_id, 'store1' as store, store1 as price
from Products where store1 is not null

union all

select product_id, 'store2' as store, store2 as price
from Products where store2 is not null

union all

select product_id, 'store3' as store, store3 as price
from Products where store3 is not null

LC 3. 树

+----+------+      
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
      1
    /   \
   2     3
 /   \
4     5

SELECT  id,
        case 
          WHEN p_id is null then 'Root'
          WHEN id in (select distinct p_id from tree) then 'Inner' 
          else 'Leaf' 
        end as type
from tree

1699. 两人之间的通话次数

题目：

编写 SQL 语句，查询每一对用户 (person1, person2) 之间的通话次数和通话总时长，其中 person1 < person2 。
以 任意顺序 返回结果表。

输入：
Calls 表：
+---------+-------+----------+
| from_id | to_id | duration |
+---------+-------+----------+
| 1       | 2     | 59       |
| 2       | 1     | 11       |
| 1       | 3     | 20       |
| 3       | 4     | 100      |
| 3       | 4     | 200      |
| 3       | 4     | 200      |
| 4       | 3     | 499      |
+---------+-------+----------+
输出：
+---------+---------+------------+----------------+
| person1 | person2 | call_count | total_duration |
+---------+---------+------------+----------------+
| 1       | 2       | 2          | 70             |
| 1       | 3       | 1          | 20             |
| 3       | 4       | 4          | 999            |
+---------+---------+------------+----------------+
解释：
用户 1 和 2 打过 2 次电话，总时长为 70 (59 + 11)。
用户 1 和 3 打过 1 次电话，总时长为 20。
用户 3 和 4 打过 4 次电话，总时长为 999 (100 + 200 + 200 + 499)。

解题：

select 
  c.person1,
  c.person2,
  count(1) call_count,
  sum(duration)  total_duration 
from (
  select  
    if(from_id > to_id, to_id, from_id) person1,
    if(from_id > to_id, from_id, to_id) person2,
    duration 
  from 
    Calls 
) c
group by person1, person2

1251. 平均售价

题目：

Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
 
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+

Result table:
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96

编写SQL查询以查找每种产品的平均售价。
`average_price` 应该四舍五入到小数点后两位。

解题：

本题需要计算每个产品的平均售价，平均售价 = 销售总额 / 总数量 因此我们只需要计算除每个产品的销售总额和总数量即可。总数量可以直接使用 UnitsSold 计算得出，使用 GROUP BY 和 SUM 函数即可： SELECT product_id, SUM(units) FROM UnitsSold GROUP BY product_id

因为每个产品不同时期的售价不同，因此在计算销售总额之前要先分别计算每个价格的销售总额。每个价格的销售总额为 对应时间内的价格 * 对应时间内的数量。因为价格和时间在 Prices 表中，数量在 UnitsSold 表中，这两个表通过 product_id 关联，那么我们就可以使用 JOIN 将两个表连接，通过 WHERE 查询对应时间内每个产品的价格和数量，并计算出对应的销售总额。

    select
        p.product_id AS product_id,
        p.price * u.units AS sales,
        u.units AS units
    from
        UnitsSold u
    join 
        Prices p on p.product_id = u.product_id
    where
        u.purchase_date BETWEEN p.start_date AND p.end_date

计算出产品每个价格的销售总额后，同样的使用 SUM 函数计算出产品所有时间的销售总额，然后除以总数量并使用 ROUND 函数保留两位小数即可。

select 
    product_id,
    Round(SUM(sales) / SUM(units), 2) AS average_price
from (
    select
        p.product_id AS product_id,
        p.price * u.units AS sales,
        u.units AS units
    from
        UnitsSold u
    join 
        Prices p on p.product_id = u.product_id
    where
        u.purchase_date BETWEEN p.start_date AND p.end_date
) t
group by product_id

1445. 苹果和桔子

题目：

写一个 SQL 查询, 报告每一天苹果和桔子销售的数目的差异. 返回的结果表, 按照格式为 ('YYYY-MM-DD') 的 sale_date 排序.

Sales 表:
+------------+------------+-------------+
| sale_date  | fruit      | sold_num    |
+------------+------------+-------------+
| 2020-05-01 | apples     | 10          |
| 2020-05-01 | oranges    | 8           |
| 2020-05-02 | apples     | 15          |
| 2020-05-02 | oranges    | 15          |
| 2020-05-03 | apples     | 20          |
| 2020-05-03 | oranges    | 0           |
| 2020-05-04 | apples     | 15          |
| 2020-05-04 | oranges    | 16          |
+------------+------------+-------------+

Result 表:
+------------+--------------+
| sale_date  | diff         |
+------------+--------------+
| 2020-05-01 | 2            |
| 2020-05-02 | 0            |
| 2020-05-03 | 20           |
| 2020-05-04 | -1           |
+------------+--------------+

在 2020-05-01, 卖了 10 个苹果 和 8 个桔子 (差异为 10 - 8 = 2).
在 2020-05-02, 卖了 15 个苹果 和 15 个桔子 (差异为 15 - 15 = 0).
在 2020-05-03, 卖了 20 个苹果 和 0 个桔子 (差异为 20 - 0 = 20).
在 2020-05-04, 卖了 15 个苹果 和 16 个桔子 (差异为 15 - 16 = -1).

题解：

select
    sale_date,
    sum(
      case 
        when fruit = 'apples' then sold_num
        else -sold_num
      end
     ) as diff
from
    Sales 
group by sale_date
order by sale_date

1193. 每月交易 I

题目：

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
以 任意顺序 返回结果表。

输入：
'Transactions' table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
输出：
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000  
+----------+---------+-------------+----------------+--------------------+-----------------------+

题解：

预备知识

DATE_FORMAT(date, format) ：用于以不同的格式显示日期/时间数据。date 参数是合法的日期，format 规定日期/时间的输出格式。

LEFT()函数是一个[字符串]函数，它返回具有指定长度的字符串的左边部分。 LEFT(str,length);

DATE_FORMAT() 函数、GROUP BY

本题要求查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额，我们可以将这句话拆分成几个子任务：

查找每个月和每个国家/地区。数据表中的 trans_date 是精确到日，我们可以使用 DATE_FORMAT() 函数将日期按照年月 %Y-%m 输出。比如将 2019-01-02 转换成 2019-01 。

DATE_FORMAT(trans_date, '%Y-%m') 获取到所有的月份后，使用 GROUP BY 聚合每个月和每个国家的记录就完成了第一步。

查找总的事务数。第一步已经将数据按月和国家聚合，只需要使用 COUNT 函数就能获取到总的事务数。

COUNT(*) AS trans_count

查找总金额。使用 SUM 函数计算总金额。

SUM(amount) AS trans_total_amount

查找已批准的事物数。已批准的事物的 state 标记为 approved。首先使用 IF 函数将 state = 'approved' 的记录标记为 1，否则为 NULL。再使用 COUNT 计算总量。

sum(state = 'approved') as approved_count,
COUNT(IF(state = 'approved', 1, NULL)) AS approved_count

查找已批准的事物的总金额。和第四步一样，先使用 IF 函数，再使用 SUM 函数。

SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount

代码

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    COUNT(IF(state = 'approved', 1, NULL)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY month, country

SELECT 
    left(trans_date, 7) as month,
    country,
    count(*) as trans_count,
    sum(state = 'approved') as approved_count,
    sum(amount) as trans_total_amount,
    sum(if(state = 'approved', amount, 0)) as approved_total_amount 
FROM
    transactions 
GROUP BY
    country, month

1633. 各赛事的用户注册率

输出：
+------------+------------+
| contest_id | percentage |
+------------+------------+
| 208        | 100.0      |
| 209        | 100.0      |
| 210        | 100.0      |
| 215        | 66.67      |
| 207        | 33.33      |
+------------+------------+
解释：
所有用户都注册了 208、209 和 210 赛事，因此这些赛事的注册率为 100% ，我们按 contest_id 的降序排序加入结果表中。
Alice 和 Alex 注册了 215 赛事，注册率为 ((2/3) * 100) = 66.67%
Bob 注册了 207 赛事，注册率为 ((1/3) * 100) = 33.33%

知识点

SUM()：返回某列的和。
AVG()：返回某列的平均值。AVG(rating/position)
COUNT() ：返回某列的行数。
MAX() ：返回某列的最大值。
MIN() ：返回某列的最小值。

select
    contest_id,
    round(count(user_id) / (select count(1) from Users) * 100, 2) as percentage
from 
    Register
group by 
    contest_id
order by 
    percentage desc, contest_id

1112. 每位学生的最高成绩

题目：

编写一个 SQL 查询，查询每位学生获得的最高成绩和它所对应的科目
若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

输入：
Enrollments 表：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2          | 2         | 95    |
| 2          | 3         | 95    |
| 1          | 1         | 90    |
| 1          | 2         | 99    |
| 3          | 1         | 80    |
| 3          | 2         | 75    |
| 3          | 3         | 82    |
+------------+-----------+-------+
输出：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1          | 2         | 99    |
| 2          | 2         | 95    |
| 3          | 3         | 82    |
+------------+-----------+-------+

题解：

学号跟成绩两个条件都要满足才可以 (student_id, grade) IN ( select )

获取每id最高成绩 select student_id,max(grade) from Enrollments group by student_id
（id,成绩）作为数组对应上述结果，可得对应的课程id where (student_id,grade) in (up code)
打平的情况，通过select min(course_id)解决即可

select 
     student_id, 
     min(course_id) as course_id ,
     grade 
from 
    Enrollments 
WHERE 
(student_id, grade) in (
    select
        student_id,
        max(grade) as grade 
    from
        Enrollments 
    group by student_id ) 
group by student_id
order by student_id

1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

题目：

请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名（ customer_id 和 customer_name ）
我们将基于此结果为他们推荐产品 C 您返回的查询结果需要按照 customer_id 排序。

Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elizabeth     |
| 4           | Jhon          |
+-------------+---------------+

Orders table:
+------------+--------------+---------------+
| order_id   | customer_id  | product_name  |
+------------+--------------+---------------+
| 10         |     1        |     A         |
| 20         |     1        |     B         |
| 30         |     1        |     D         |
| 40         |     1        |     C         |
| 50         |     2        |     A         |
| 60         |     3        |     A         |
| 70         |     3        |     B         |
| 80         |     3        |     D         |
| 90         |     4        |     C         |
+------------+--------------+---------------+

Result table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+
只有 customer_id 为 3 的顾客购买了产品 A 和产品 B ，却没有购买产品 C

题解：

GROUP BY + HAVING过滤

SELECT
    c.customer_id, c.customer_name
FROM
    Orders o 
JOIN 
    Customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING
    sum(product_name = 'A') * sum(product_name = 'B') > 0
    AND sum(product_name = 'C') = 0
ORDER BY c.customer_id

WHERE过滤，IN & NOT IN区分

SELECT
    customer_id, customer_name
FROM
    Customers
WHERE
    customer_id NOT IN (
        SELECT customer_id
        FROM Orders
        WHERE product_name = 'C'
    ) AND Customer_id IN (
        SELECT customer_id
        FROM Orders
        WHERE product_name = 'A'
    ) AND Customer_id IN (
        SELECT customer_id
        FROM Orders
        WHERE product_name = 'B'
    )
ORDER BY customer_id

1440. 计算布尔表达式的值

题目：

写一个 SQL 查询,  以计算表 `Expressions` 中的布尔表达式.
返回的结果表没有顺序要求.

Variables 表:
+------+-------+
| name | value |
+------+-------+
| x    | 66    |
| y    | 77    |
+------+-------+

Expressions 表:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x            | >        | y             |
| x            | <        | y             |
| x            | =        | y             |
| y            | >        | x             |
| y            | <        | x             |
| x            | =        | x             |
+--------------+----------+---------------+

Result 表:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x            | >        | y             | false |
| x            | <        | y             | true  |
| x            | =        | y             | false |
| y            | >        | x             | true  |
| y            | <        | x             | false |
| x            | =        | x             | true  |
+--------------+----------+---------------+-------+
如上所示, 你需要通过使用 Variables 表来找到 Expressions 表中的每一个布尔表达式的值.

case when then... 判断比较符号

case 比较符号
    when '>' then if (结果)
    when '<' then if (结果)
    else          if (结果)
end

select 
    e.left_operand,
    e.operator,
    e.right_operand,
    case e.operator
        when '>' then if (v1.value > v2.value, 'true', 'false')
        when '<' then if (v1.value < v2.value, 'true', 'false')
        else if (v1.value = v2.value, 'true', 'false')
    end as value
from 
    Expressions e
left join Variables v1 on v1.name = e.left_operand 
left join Variables v2 on v2.name = e.right_operand

1264. 页面推荐

题目：

写一段 SQL  向`user_id` = 1 的用户，推荐其朋友们喜欢的页面。
不要推荐该用户已经喜欢的页面。你返回的结果中不应当包含重复项。

输入：
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
| 1        | 3        |
| 1        | 4        |
| 2        | 3        |
| 2        | 4        |
| 2        | 5        |
| 6        | 1        |
+----------+----------+
 
Likes table:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1       | 88      |
| 2       | 23      |
| 3       | 24      |
| 4       | 56      |
| 5       | 11      |
| 6       | 33      |
| 2       | 77      |
| 3       | 77      |
| 6       | 88      |
+---------+---------+

输出：
+------------------+
| recommended_page |
+------------------+
| 23               |
| 24               |
| 56               |
| 33               |
| 77               |
+------------------+
解释：
用户1 同 用户2, 3, 4, 6 是朋友关系。
推荐页面为： 页面23 来自于 用户2, 页面24 来自于 用户3, 页面56 来自于 用户3 以及 页面33 来自于 用户6。
页面77 同时被 用户2 和 用户3 推荐。
页面88 没有被推荐，因为 用户1 已经喜欢了它。

题解：

方法一： UNION ALL

本题最直观的思路就是找到所有 user_id = 1 的朋友，找到他们喜欢的 page_id，再去掉 user_id = 1 喜欢的 page_id。

伪代码如下：

SELECT page_id 
FROM Likes WHERE
user_id IN (friends) AND page_id NOT IN (user_id = 1 like page)

根据上面的伪代码，我们只需要求出 friends 和 user_id = 1 like page 就能完成本题。

首先我们求 friends。通过 Friendship 可以得到所有的朋友。user_id = 1 有可能在 user1_id，也有可能在 user2_id。因此我们需要两个 sql 分别求出朋友。 SELECT user1_id AS user_id FROM Friendship WHERE user2_id = 1

SELECT user2_id AS user_id FROM Friendship WHERE user1_id = 1

使用 UNION ALL 或者 UNION 得到所有的朋友（UNION ALL 和 UNION 的区别在于后者会去掉重复的行，前者不会，这里使用前者更高效）。

然后我们再求 user_id = 1 like page。这个也很简单，直接对表 Likes 使用 WHERE 语句即可。 SELECT page_id FROM Likes WHERE user_id = 1

SELECT 
    distinct page_id  as recommended_page
FROM 
    Likes 
WHERE
    user_id IN (
        SELECT user1_id AS user_id FROM Friendship WHERE user2_id = 1
        UNION ALL
        SELECT user2_id AS user_id FROM Friendship WHERE user1_id = 1 
    )
AND 
    page_id NOT IN (
        SELECT page_id FROM Likes WHERE user_id = 1 
    )

方法二： CASE WHEN 思路

对于方法一的求 friends，我们还可以用 CASE WHEN。

首先筛选出符合条件的记录：

SELECT * FROM Friendship WHERE user1_id = 1 OR user2_id = 1

然后使用 CASE WHEN，如果 user1_id = 1，取 user2_id。如果 user2_id = 1，则取 user1_id。

SELECT (
    case
      when user1_id = 1 then user2_id
      when user2_id = 1 then user1_id
    end
) AS user_id
FROM Friendship
WHERE user1_id = 1 OR user2_id = 1

570. 至少有5名直接下属的经理

输入: 
编写一个SQL查询，查询至少有5名直接下属的经理 。
Employee 表:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | None      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+
输出: 
+------+
| name |
+------+
| John |
+------+

select 
    name
from 
    Employee
where 
    id in (
        select 
            managerId 
        from
            Employee 
        group by managerId 
        having count(id) >= 5 )

牛客网

SQL12

获取每个部门中当前员工薪水最高的相关信息

此题思路如下：

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代替后输出。

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary
FROM salaries AS s INNER JOIN dept_emp As d
ON d.emp_no = s.emp_no 
GROUP BY d.dept_no

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

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

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

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

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

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

SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary
FROM 

# 创建maxsalary表用于存放当前每个部门薪水的最大值
( SELECT d.dept_no, MAX(s.salary) AS salary
  FROM salaries AS s INNER JOIN dept_emp As d
  ON d.emp_no = s.emp_no 
  GROUP BY d.dept_no
) AS maxsalary, 
  
# 创建currentsalary表用于存放当前每个部门所有员工的编号和薪水
( SELECT d.dept_no, s.emp_no, s.salary 
  FROM salaries AS s INNER JOIN dept_emp As d
  ON d.emp_no = s.emp_no 
) AS currentsalary

# 限定条件为两表的dept_no和salary均相等
WHERE currentsalary.dept_no = maxsalary.dept_no
AND   currentsalary.salary = maxsalary.salary

# 最后以currentsalary.dept_no排序输出符合要求的记录表
ORDER BY currentsalary.dept_no

SQL18 薪水第二多

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

（温馨提示:sqlite通过的代码不一定能通过mysql，因为SQL语法规定，使用聚合函数时，select子句中一般只能存在以下三种元素：常数、聚合函数，group by 指定的列名。如果使用非group by的列名，sqlite的结果和mysql 可能不一样)

emp_no	salary	from_date	to_date
10001	88958	2002-06-26	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01
10004	74057	2001-11-27	9999-01-01

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Chirstian	Koblick	M	1986-12-01

# 解法 1

# 1. 先找出最大的工资
select max(salary)
from salaries 

# 2. 将不等于最大工资的员工挑选出来 当作表 t1
select *
from salaries
where salary != (select max(salary) from salaries )

# 3. 找出第二大工资 当作表 t2
select max(t.salary)
from (select * from salaries
		where salary != (select max(salary) from salaries )) t1

# 4. 找出等于第二大工资的员工的编号与工资 当作表 t3
select emp_no, salary
from salaries
join (select max(t.salary) as maxsal
        from (select *
        from salaries
        where salary != (select max(salary)
                from salaries )) t1) t2
on salary = t2.maxsal

# 5. 表连接求结果
select e.emp_no, t3.salary, e.last_name, e.first_name
from employees e
join t3
on e.emp_no = t3.emp_no

# 解法 2
select s.emp_no,s.salary,e.last_name,e.first_name from
salaries s join employees e
on s.emp_no = e.emp_no
where s.salary = 
(
    select max(salary) from salaries
    where salary < (select max(salary) from salaries)
)

老杜

# emp 表
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

# dept 表
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

# salgrade 表
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

1、最高薪水

1、取得每个部门最高薪水的人员名称

取得每个部门最高薪水(按照部门编号分组，找出每一组最大值)

select deptno, max(sal) as maxsal from emp group by deptno;

+--------+----------+
| deptno |  maxsal  |
+--------+----------+
|     20 |  3000.00 |
|     30 |  2850.00 |
|     10 |  5000.00 |
+--------+----------+

将以上的查询结果当做一张临时表t，t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal

select e.ename, t.*
from emp e
join (select deptno, max(sal) as maxsal from emp group by deptno) t
on e.sal = t.maxsal and e.deptno = t.deptno;

2、平均薪水之上

哪些人的薪水在部门的平均薪水之上

# 第一步：找出每个部门的平均薪水
select deptno, avg(sal) as avgsal from emp group by deptno;

# 第二步：将以上查询结果当做t表，t和emp表连接
select e.ename, e.sal, t.deptno
from emp e
join (select deptno, avg(sal) as avgsal from emp group by deptno) t
on e.sal > t.avgsal and e.deptno = t.deptno;

3、平均等级

取得部门中（所有人的）平均的薪水等级

平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

1.找出每个人的薪水等级 emp e和salgrade s表连接

select e.ename, e.sal, e.deptno, s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;

+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+

+--------+---------+--------+-------+
| CLARK  | 2450.00 |     10 |     4 |
| KING   | 5000.00 |     10 |     5 |
| MILLER | 1300.00 |     10 |     2 |
+--------+---------+--------+-------+

+--------+---------+--------+-------+
| SMITH  |  800.00 |     20 |     1 |
| JONES  | 2975.00 |     20 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| ADAMS  | 1100.00 |     20 |     1 |
| FORD   | 3000.00 |     20 |     4 |
+--------+---------+--------+-------+

+--------+---------+--------+-------+
| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| TURNER | 1500.00 |     30 |     3 |
| JAMES  |  950.00 |     30 |     1 |
+--------+---------+--------+-------+

基于以上的结果继续按照deptno分组，求grade的平均值。

select e.deptno, avg(s.grade)
from emp e
join salgrade s
on e.sal between s.losal and s.hisal 
group by e.deptno ;

+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

4、最高薪水

4、不准用组函数（Max ），取得最高薪水

第一种：sal降序，limit 1

select ename,sal 
from emp 
order by sal desc 
limit 1;

select max(sal) from emp;

+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

第二种方案：表的自连接

select sal 
from emp 
where sal 
not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

select distinct a.sal 
from emp a 
join emp b 
on a.sal < b.sal

a表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
+---------+
b表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
+---------+

5、平均薪水最高

取得平均薪水最高的部门的部门编号（两种解决方案）

第一种方案：降序取第一个

# 第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
	+--------+-------------+
	| deptno | avgsal      |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+
	
# 第二步：降序选第一个。
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
	+--------+-------------+
	| deptno | avgsal      |
	+--------+-------------+
	|     10 | 2916.666667 |
	+--------+-------------+

第二种方案：max

# 第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

# 第二步：找出以上结果中avgsal最大的值。
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+

# 第三步：分组查询 过滤条件是 该部门的平均工资 avgsal 等于 全部部门中最高平均工资
select deptno,avg(sal) as avgsal 
from emp 
group by deptno
having avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

6、平均薪水最高

6、取得平均薪水最高的部门的部门名称

select d.dname, avg(e.sal) as avgsal 
from emp e
join dept d on e.deptno = d.deptno
group by d.dname
order by avgsal desc 
limit 1;	

+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

7、等级最低的部门

7、求平均薪水的等级最低的部门的部门名称

平均薪水是800 平均薪水是900 那么他俩都是 1 级别

但是平均薪水最低的对应的等级一定是最低的，可以先找出最低的等级，所有等于最低等级的部门都满足题意

# 按照部门编号分组求平均薪水
SELECT deptno, avg(sal) avgsal
FROM emp
GROUP BY deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

# 求出每个部门的平均工资等级
SELECT t.*, salgrade.grade
FROM salgrade
JOIN (SELECT deptno, avg(sal) avgsal
        FROM emp
        GROUP BY deptno) t 
on t.avgsal BETWEEN salgrade.losal AND salgrade.hisal;
+------------+-------------+-------+
| dname      | avgsal      | grade |
+------------+-------------+-------+
| SALES      | 1566.666667 |     3 |
| ACCOUNTING | 2916.666667 |     4 |
| RESEARCH   | 2175.000000 |     4 |
+------------+-------------+-------+

# 找出每个部门的平均薪水等级的最低值 == 3
SELECT salgrade.grade
FROM salgrade
JOIN (SELECT deptno, avg(sal) avgsal
        FROM emp
        GROUP BY deptno) t
ON t.avgsal between salgrade.losal AND salgrade.hisal
ORDER BY grade
LIMIT 1;
+-------+
| grade |
+-------+
|     3 |
+-------+


# 求出部门中平均薪资等级等于最小值的全部部门
SELECT t1.*
FROM (SELECT t.*, salgrade.grade
        FROM salgrade
        JOIN (SELECT deptno, avg(sal) avgsal
        FROM emp
        GROUP BY deptno) t
        on t.avgsal BETWEEN salgrade.losal AND salgrade.hisal) t1
JOIN (SELECT salgrade.grade
        FROM salgrade
        JOIN (SELECT deptno, avg(sal) avgsal
        FROM emp
        GROUP BY deptno) t
        ON t.avgsal between salgrade.losal AND salgrade.hisal
        ORDER BY grade
        LIMIT 1) t2
on t1.grade = t2.grade ;
+-------+-------------+-------+
| dname | avgsal      | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+

8、最高薪水还要高

8、取得比普通员工(员工代码没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名

比 普通员工的最高薪水 还要高的一定是领导！没毛病！！！！

not in 在使用的时候，后面小括号中记得排除 NULL

# 员工编号没有在以下范围内的都是普通员工
select distinct mgr from emp where mgr is not null;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+

# 找出普通员工的最高薪水！
# not in 在使用的时候，后面小括号中记得排除 NULL
select max(sal) 
from emp 
where empno not in(select distinct mgr 
                   from emp 
                   where mgr is not null);
+----------+
| max(sal) |
+----------+
|  1600.00 |
+----------+

# 找出高于1600的 的所有员工，注意如果该员工比 普通员工的最高薪水 还高，只能是领导了 有坑！！！
select ename,sal 
from emp 
where sal > (select max(sal) 
             from emp where empno 
             not in (select distinct mgr from emp where mgr is not null));

9、薪水最高

9、取得薪水最高的前五名员工

10、取得薪水最高的第六到第十名员工

11、取得最后入职的 5 名员工

select ename,sal from emp order by sal desc limit 5;

select ename,sal from emp order by sal desc limit 5, 5;

select ename,hiredate from emp order by hiredate desc limit 5;

12、多少员工

12、取得每个薪水等级有多少员工

SELECT s.grade ,count(*)
from emp e 
join salgrade s 
ON e.sal BETWEEN s.losal and s.hisal
GROUP BY s.grade;

13、==面试题==

有 3 个表 S(学生表)，C（课程表），SC（学生选课表）

S（SNO，SNAME）代表（学号，姓名）

C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：

1，找出没选过“黎明”老师的所有学生姓名。

# 1. 找出‘黎明’老师教过的课程
select cno from c where cteacher = '黎明'

# 2. 找出所有选过”黎明”老师的学生编号
select sno from sc where cno in (select cno from c where cteacher = '黎明')

# 3. 找出没选过他的学生
select sno from s 
where sno not in 
(select sno from sc where cno = (select cno from c where cteacher = '黎明'));

2，列出 2 门以上（含2门）不及格学生姓名及平均成绩。

# 1. 找不及格学生姓名 t1
select sc.sno, s.sname, count(*) courseNum
from sc join s on sc.sno=s.sno
where sc.scgrade < 60
group by sc.sno, s.sname
having courseNum >= 2;

# 2. 平均成绩 t2
select sc.sno, avg(sc.scgrade) as avgscgradge
from sc
group by sc.sno

# 3. 表连接 t1 t2
select t1.sname, t2.avgscgradge
from t1
join t2
on t1.sno = t2.sno

3，即学过 1 号课程又学过 2 号课所有学生的姓名。

select s.sname
from sc
join s
on sc.sno=s.sno
where cno=1 and sc.sno in (select sno from sc where cno=2);

14、列出所有

列出所有员工及领导的姓名

select a.ename '员工', b.ename '领导'
from emp a
left join emp b
on a.mgr = b.empno;

15、受雇日期

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

emp a 员工表

emp b 领导表

SELECT a.ename, a.hiredate, b.ename, b.hiredate, d.dname
FROM emp a
JOIN emp b
ON a.mgr = b.empno AND a.hiredate < b.hiredate
JOIN dept d ON a.deptno = d.deptno;

16、员工信息

16、列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门

SELECT d.dname, e.ename
FROM dept d
LEFT JOIN emp e 
ON d.deptno = e.deptno

17、所有部门

17、列出至少有 5 个员工的所有部门

SELECT deptno
FROM emp e
GROUP BY e.deptno
HAVING count(*) >= 5;

18、员工信息

18、列出薪金比"SMITH" 多的所有员工信息

select ename,sal from emp 
where sal > (select sal 
             from emp 
             where ename = 'SMITH');

19、部门人数

19、列出所有"CLERK"(办事员) 的姓名及其部门名称, 部门的人数

t1 查询所有"CLERK"(办事员) 的姓名及其部门名称，编号

select e.ename,e.job,d.dname,d.deptno
from emp e
join dept d
on e.deptno = d.deptno
where e.job = 'CLERK';

+--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING |     10 |
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
+--------+-------+------------+--------+

t2 查询每个部门的人数

select deptno, count(*) as deptcount 
from emp 
group by deptno;

+--------+-----------+
| deptno | deptcount |
+--------+-----------+
|     10 |         3 |
|     20 |         5 |
|     30 |         6 |
+--------+-----------+

t1 与 t2 表连接

SELECT t1.ename,t1.job,t1.deptno,t2.deptcount
FROM (  select e.ename,e.job,d.dname,d.deptno
        from emp e
        join dept d
        on e.deptno = d.deptno
        where e.job = 'CLERK') t1
JOIN (  select deptno, count(*) as deptcount 
        from emp 
        group by deptno) t2
ON t1.deptno = t2.deptno;

20、雇员人数

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

按照工作岗位分组求最小值

SELECT job, count(*) as member
from emp
GROUP BY job
HAVING min(sal) > 1500;

21、姓名

21、列出在部门"SALES"<销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.

select ename from emp where deptno = ( select deptno from dept where dname = 'SALES' );

22、工资等级

列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

SELECT e.ename, d.dname, l.ename, s.grade
FROM emp e
    # 连接 dept
    JOIN dept d
    ON e.deptno = d.deptno
    
    # 连接 emp 查询领导 
    LEFT JOIN emp l
    ON e.mgr = l.empno
    
    # 连接 salgrade 根据条件查询等级
    JOIN salgrade s
    ON e.sal BETWEEN s.losal AND s.hisal
    WHERE e.sal > (SELECT avg(sal) FROM emp)

23、部门名称

列出与"SCOTT" 从事相同工作的所有员工及部门名称

SELECT ename, job, dname
FROM emp
JOIN dept d ON emp.deptno = d.deptno
WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT')
AND ename != 'SCOTT';

24、姓名和薪金

列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.

# 在 30 号部门工作的所有员工的薪金（去重）
select distinct sal from emp where deptno = 30;

select ename,sal 
from emp 
where sal in(select distinct sal from emp where deptno = 30) 
and deptno != 30;

25、部门名称

列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

# 最多的薪资
select max(sal) from emp where deptno = 30;

selecte.ename,e.sal,d.dname
fromemp e
joindept d
on e.deptno = d.deptno
where e.sal > (select max(sal) from emp where deptno = 30);

26、平均服务期限

列出在每个部门工作的员工数量, 平均工资和平均服务期限

SELECT 	d.dname, d.deptno, 
		count(ename) as '部门人数', 
		ifnull(avg(emp.sal) , 0) as '平均工资',
		ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as '平均服务期限'
from emp 
right JOIN dept d 
ON emp.deptno = d.deptno
GROUP BY d.deptno

+--------+--------+-------------+----------------+
| deptno | ecount | avgsal      | 平均服务期限     |
+--------+--------+-------------+----------------+
|     10 |      3 | 2916.666667 |        38.0000 |
|     20 |      5 | 2175.000000 |        35.8000 |
|     30 |      6 | 1566.666667 |        38.3333 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+

27、工资

列出所有员工的姓名、部门名称和工资。

select e.ename,d.dname,e.sal
from emp e
join dept d
on e.deptno = d.deptno;

28、详细信息和人数

列出所有部门的详细信息和人数

select d.deptno, d.dname, d.loc, count(e.ename)
from emp e
right join dept d
on e.deptno = d.deptno
group by d.deptno, d.dname, d.loc;

29、雇员姓名

列出各种工作的最低工资及从事此工作的雇员姓名

# 各种工作的最低工资
select job, min(sal) as minsal
from emp
group by job;

# 找从事此工作的员工
SELECT e.ename, e.job, e.sal
from emp e
JOIN  (	select job, min(sal) as minSal
        from emp
        GROUP BY job ) t
ON e.job = t.job and e.sal = t.minSal;

30、最低薪金

列出各个部门的 MANAGER(领导) 的最低薪金

# 各个部门的 MANAGER(领导) job = 'manager'
SELECT  e.deptno, e.ename, ifnull(min(e.sal), 0) as minSal
FROM emp e
WHERE job = 'MANAGER'
GROUP BY deptno;

31、从低到高排序

列出所有员工的年工资, 按年薪从低到高排序

select ename,(sal + ifnull(comm,0)) * 12 as yearsal
from emp
order by yearsal asc;

32、名称与领导

求出员工领导的薪水超过3000的员工名称与领导

select a.ename '员工',b.ename '领导'
from emp a
join emp b
on a.mgr = b.empno
where b.sal > 3000;

33、工资合计、部门人数

求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数

SELECT d.deptno, d.dname, d.loc, ifnull(sum(e.sal), 0), count(e.deptno)
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno
WHERE d.dname LIKE '%S%'
GROUP BY d.deptno, d.dname, d.loc

34、加薪 10%

给任职日期超过 30 年的员工加薪 10%.

update emp 
set sal = sal * 1.1 
where timestampdiff(YEAR, hiredate, now()) > 30;

Emos 系统

Wiki 系统

快照表生成

方案一（ID不连续）：
1. 删除今天的数据
2. 为所有的电子书生成一条今天的记录
3. 更新总阅读数、总点赞数
4. 更新今日阅读数、今日点赞数      
       
方案二（ID连续）：
1. 为所有的电子书生成一条今天的记录，如果还没有
2. 更新总阅读数、总点赞数
3. 更新今日阅读数、今日点赞数

<update id="genSnapshot">
    insert into ebook_snapshot(ebook_id, `date`, view_count, vote_count, view_increase, vote_increase)
    select t1.id, curdate(), 0, 0, 0, 0
    from ebook t1
    where not exists(select 1
                     from ebook_snapshot t2
                     where t1.id = t2.ebook_id
                       and t2.`date` = curdate());

    update ebook_snapshot t1, ebook t2
    set t1.view_count = t2.view_count,
        t1.vote_count = t2.vote_count
    where t1.`date` = curdate()
      and t1.ebook_id = t2.id;

    update ebook_snapshot t1 left join (select ebook_id, view_count, vote_count
        from ebook_snapshot
        where `date` = date_sub(curdate(), interval 1 day)) t2
    on t1.ebook_id = t2.ebook_id
        set t1.view_increase = (t1.view_count - ifnull(t2.view_count, 0)),
            t1.vote_increase = (t1.vote_count - ifnull(t2.vote_count, 0))
    where t1.`date` = curdate();
</update>

<!-- 获取首页数值数据：总阅读数、总点赞数、今日阅读数、今日点赞数、今日预计阅读数、今日预计阅读增长 -->
<select id="getStatistic" resultType="com.jiawa.wiki.resp.StatisticResp">
    select
        t1.`date` as `date`,
        sum(t1.view_count) as viewCount,
        sum(t1.vote_count) as voteCount,
        sum(t1.view_increase) as viewIncrease,
        sum(t1.vote_increase) as voteIncrease
    from
        ebook_snapshot t1
    where
        t1.`date` >= date_sub(curdate(), interval 1 day)
    group by
        t1.`date`
    order by
        t1.`date` asc;
</select>

<select id="get30Statistic" resultType="com.jiawa.wiki.resp.StatisticResp">
    select
        t1.`date` as `date`,
        sum(t1.view_increase) as viewIncrease,
        sum(t1.vote_increase) as voteIncrease
    from
        ebook_snapshot t1
    where
        t1.`date` between date_sub(curdate(), interval 30 day) and date_sub(curdate(), interval 1 day)
    group by
        t1.`date`
    order by
        t1.`date` asc;
</select>

SQL 练习

知识点

LeetCode

模板

LC 1. GROUP_CONCAT

LC 2. 行转列（横表转竖表）

LC 3. 树

1699. 两人之间的通话次数

1251. 平均售价

牛客网

SQL12

SQL18 薪水第二多

老杜

1、最高薪水

2、平均薪水之上

3、平均等级

4、最高薪水

5、平均薪水最高

6、平均薪水最高

7、等级最低的部门

8、最高薪水还要高

9、薪水最高

12、多少员工

13、==面试题==

14、列出所有

15、受雇日期

16、员工信息

17、所有部门

18、员工信息

19、 部门人数

20、雇员人数

21、姓名

22、工资等级

23、部门名称

24、姓名和薪金

25、部门名称

26、平均服务期限

27、工资

28、详细信息和人数

29、雇员姓名

30、最低薪金

31、从低到高排序

32、名称与领导

33、工资合计、部门人数

34、加薪 10%

Emos 系统

Wiki 系统

19、部门人数