题目描述

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

表1：Employee (Primary Key: Id)

Id Salary
1 100
2 200
3 300

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

SecondHighestSalary
200

相关知识

  1. SQL中使用ORDER BY进行排序，降序排列使用DESC
  2. LIMIT n,m 第一个参数指定第一个返回记录的偏移量，第二个参数指定返回记录行的最大数目，起始行偏移量是0

解题过程

  1. 编写SQL

    SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1

  2. 提交后，答案错误

    • my output

      {
    "headers":[
        "Salary"
    ],
    "values":[
        [
            200
        ]
    ]
}

    • standard output

      {
    "headers":[
        "SecondHighestSalary"
    ],
    "values":[
        [
            200
        ]
    ]
}

    • 返回的字段名需要更改

  3. 修改sql，使用AS关键字可以给字段起一个别名，并用别名返回

    SELECT Salary AS SecondHighestSalary  FROM Employee ORDER BY Salary DESC LIMIT 1,1

  4. 提交，解答错误

    • my output

      {
    "headers":[
        "SecondHighestSalary"
    ],
    "values":[
     
    ]
}

    • standard output

      {
    "headers":[
        "SecondHighestSalary"
    ],
    "values":[
        [
            null
        ]
    ]
}

    • sql查询无结果时，默认返回的是空白，而不是空值。

  5. 查询资料，发现可以通过ifnull方法进行替换，然而查询记录为空并不是NULL，所以这个方法没用

  6. 多次尝试后，编写sql

    WITH CTE AS (SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1) 
SELECT CASE(SELECT COUNT(*) FROM CTE) WHEN 1 THEN (SELECT Salary FROM CTE) ELSE NULL END AS SecondHighestSalary
    • 公用表表达式允许使用命名的临时结果集，通过允许在SELECT和某些其他语句前面使用WITH子句来实现，格式为WITH CTEname AS (query)，这样就可以避免多次重复执行这个查询
    • 使用CASE WHEN END控制结构，来实现当查询结果为空时，把[]替换为[null]

  7. 提交，出现了新的错误

    • input

      {
    "headers":{
        "Employee":[
            "Id",
            "Salary"
        ]
    },
    "rows":{
        "Employee":[
            [
                1,
                100
            ],
            [
                2,
                100
            ]
        ]
    }
}

    • my output

      {
    "headers":[
        "SecondHighestSalary"
    ],
    "values":[
        [
            100
        ]
    ]
}

    • standard output

      {
    "headers":[
        "SecondHighestSalary"
    ],
    "values":[
        [
            null
        ]
    ]
}

    • 可以看出这是因为忘记了去除重复值

  8. 使用DISTINCT去除重复值，构建sql，DISTINCT需要在select的字段名前修饰

    WITH CTE AS (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1) 
SELECT CASE(SELECT COUNT(*) FROM CTE) WHEN 1 THEN (SELECT Salary FROM CTE) ELSE NULL END AS SecondHighestSalary

学习总结

  1. 公用表表达式(CTE)

    不能在同一查询中两次引用派生表，因为查询会根据派生表的引用次数计算两次或多次，这会引发严重的性能问题

    • 假如想要了解每年工资相对前一年的增长百分比，工资表为salaries表，因为要对比相邻两年的涨幅，就需要进行笛卡尔积，而要进行笛卡尔积的表需要对目前的表进行处理，因为目前的表中一行是每次发工资的条目，而不是一年一个条目，对数据处理完后形成一个派生表，之后进行笛卡尔积。从下面的代码中可以看到，q1和q2其实是同样的内容，但是却要每个单独计算一遍，浪费了一半的计算量。

      SELECT
	q1.year;
	q2.year AS next_year;
	q1.sum;
	q2.sum AS next_sum;
	100*(q2.sum-q1.sum)/q1.sum AS percent
FROM
	(SELECT year(from_date) as year,sum(salary) as sum FROM salaries GROUP BY year) AS q1,
	(SELECT year(from_date) as year,sum(salary) as sum FROM salaries GROUP BY year) AS q2
WHERE q1.year=q2.year-1;

    • 公式表表达式正是用来解决这个问题，它允许在SELECT等语句前使用WITH语句来对临时结果集进行命名。

    • 在命名之后，本次SQL中就可以通过这个名字来访问临时结果集，而无需再次计算，上面的代码也就可以精简为

      WITH CTE AS (SELECT year(from_date) as year,sum(salary) as sum FROM salaries GROUP BY year)
SELECT
     	
FROM
	CTE AS q1,
	CTE AS q2
WHERE
	q1.year=q2.year-1;

  2. 数据库中的NULL

  • NULL、空字符串的长度比较

    • sql

      SELECT LENGTH(NULL),LENGTH(''),LENGTH(233),LENGTH('6666')

    • 执行结果

      LENGTH(NULL) LENGTH(‘’) LENGTH(0) LENGTH(‘6666’)
      NULL 0 1 4

    • 可以看出NULL并不是0，也不是空字符串

  • NULL是未定义的或未知的

  • 判断是否为NULL时需要使用ISNULL()函数，而不能用等号和不等号进行判断

  • 当数据表中某个字段允许为NULL时，会使用额外的比特位标识当前元组的这个属性是否为NULL

  • NULL参与任何运算的结果均是NULL，例如判断NULL是否等于NULL

    • 如下sql，执行结果为1

      SELECT (2=2)

    • 而如下sql，执行结果为NULL

      SELECT (NULL=NULL)

  • 对于一件语句，其他语言基本是二值逻辑，即真和假，而在数据库中会有真、假、未知真假三种状态，而NULL就用来表示这个状态

  • 当查询表之后，一条记录都没有时，是NULL的状态

  • 数据表的某个元组的某个属性也可能是NULL的状态

  • 如果想实现NULL to something，可以使用IFNULL(字段名，表达式)来对字段为NULL时进行某些操作

  • *在数据库中代表匹配所有，此时会匹配NULL；当指定某个字段进行某些操作时，如CONUT()，就会忽略NULL

  1. 之前提交出错的sql

    • 尝试IF()出错，IF()函数接收三个表达式，如果第一个参数为真，则执行第二个参数并作为结果返回，否则执行第三个参数并作为结果返回

      • sql

        SELECT DISTINCT IF(Salary,Salary,NULL) AS SecondHighestSalary  FROM Employee ORDER BY Salary DESC LIMIT 1,1

      • 它在结果不为空时正常返回，在结果为空时没有替换为NULL

      • 当删掉FROM….LIMIT 1，1这些语句，即不从表中查询时，将判断语句改为2<1，结果可以正常替换为NULL

      • 修改sql

        SELECT
	IF(
        (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1),
        (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1),
        NULL    
    )
    AS SecondHighestSalary

      • 提交sql，出现了新的错误

        • input

          {
    "headers":{
        "Employee":[
            "Id",
            "Salary"
        ]
    },
    "rows":{
        "Employee":[
            [
                3,
                1
            ],
            [
                2,
                0
            ],
            [
                1,
                0
            ]
        ]
    }
}

        • my output

          {
    "headers":[
        "SecondHighestSalary"
    ],
    "values":[
        [
            null
        ]
    ]
}

        • 可以看出这里的第二高是0，而IF语句把第一个语句的返回值0当作了执行结果为假，导致跳转到条件为假的第三个表达式执行，所以错误输出了NULL

      • 修改sql

        SELECT
	IF(
        (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1) IS NOT NULL,
        (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1),
        NULL    
    )
    AS SecondHighestSalary

      • 提交，答案正确

      • 但是本次提交的sql浪费了接近一半的计算，分别是IF()的第一个表达式和第二个表达式，它们两次查表的结果完全一样，却执行了两遍

      • 使用公用表表达式进行改进

        WITH CTE AS (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1) 
SELECT
	IF(
    	(SELECT COUNT(*) FROM CTE)=0,
        NULL,
        (SELECT Salary FROM CTE)
    )
    AS SecondHighestSalary

    • 使用IFNULL()函数，这个函数接收两个表达式，如果第一个参数不为NULL，则返回第一个参数的执行结果，否则返回第二个参数的执行结果

      • sql

        SELECT DISTINCT IFNULL( Salary,NULL) AS SecondHighestSalary FROM Employee ORDER BY Salary DESC LIMIT 1,1

      • my output

        {
    "headers":[
        "SecondHighestSalary"
    ],
    "values":[
       
    ]
}

      • standard output

        {
    "headers":[
        "SecondHighestSalary"
    ],
    "values":[
        null
    ]
}

      • 仍然是没有完成替换，查看题解，发现题解中一旦有了IFNULL()函数就不再有FROM WHERE子句

        SELECT
	IFNULL(
        (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1),
        NULL
    )
    AS SecondHighestSalary