搞定SQL!5个棘手SQL查询的解决方法

数据库 SQL Server
对于数据科学家来讲,SQL(结构化查询语言)是其工具箱中比较重要的工具之一。掌握SQL不仅有助于你在面试中脱颖而出,而且通过解决复杂查询达到对SQL的充分理解,还能在让你许多竞争中保持领先地位。

 对于数据科学家来讲,SQL(结构化查询语言)是其工具箱中比较重要的工具之一。掌握SQL不仅有助于你在面试中脱颖而出,而且通过解决复杂查询达到对SQL的充分理解,还能在让你许多竞争中保持领先地位。

[[344803]]

本文就将介绍5个有关SQL的棘手问题和其解决方法。注意,每个查询都能以不同方式编写。在参考本文解决方案之前,你可以先试着自己思考一下。

 

查询1

下列表格由名字和职业两列组成。需要查询所有姓名,且使其后紧跟一个括号,括住“职业”列中对应的首字母。

 

搞定SQL!5个棘手SQL查询的解决方法

 

  • 本文解决方案

 

  1. SELECT 
  2. CONCAT(Name, ’(‘, SUBSTR(Profession, 1, 1), ’)’) 
  3. FROM table

由于需要把名字和职业结合起来,可以使用CONCAT。而且因为括号内只需要一个字母,可以使用SUBSTR来传递列名、开始索引和结束索引。因为只需要首字母,所以我们将传递1,1(开始索引包括在内,结束索引不包括在内)。

 

查询2

蒂娜需要从她创建的EMPLOYEES表中计算所有员工的平均工资,但结果显示的平均值很低,这可能是键盘上的回零键失效了。她希望我们帮助找出错误计算的平均值和实际平均值之间的差异。我们须编写一个查找错误的查询(实际平均值-计算平均值)。

 

搞定SQL!5个棘手SQL查询的解决方法

 

  • 本文解决方案

 

  1. SELECT 
  2. AVG(Salary) - AVG(REPLACE(Salary, 0, ’’)) 
  3. FROM table

需要注意,只有一个表包含了实际工资值。为了创建错误场景,使用REPLACE替换0。接着传递列名、替换值以及用于替换REPLACE方法的值。然后,使用聚集函数AVG来求平均值的差。

 

查询3

给定一个表,它是由节点和父节点两列组成的二元搜索树。需要编写一个查询,以返回按节点值进行升序排序的节点类型。有3种类型:

 

  • 根(Root)——如果节点是根
  • 叶(Leaf)——如果节点是叶
  • 内部(Inner)——如果节点既不是根也不是叶

 

搞定SQL!5个棘手SQL查询的解决方法

 

 

 

  • 本文解决方案

经过初步分析,可以得出结论:如果给定节点N的相应P值为NULL(空),则它是根。而如果给定节点N存在于P列中,则它不是内部节点。基于此想法编写一个查询。

 

  1. SELECT CASE 
  2.     WHEN P IS NULL THENCONCAT(N, ' Root'
  3.     WHEN N IN (SELECTDISTINCT P from BST) THEN CONCAT(N, ' Inner'
  4.     ELSE CONCAT(N, ' Leaf'
  5.     ENDFROM BSTORDER BY N asc

可使用CASE作为开关函数。正如前文提到的,如果对于给定节点N,P为空值,则N是根。因此,我们使用CONCAT来组合节点值和标签。

类似地,如果给定节点N存在于P列中,则它是内部节点。为了获得P列中的所有节点,我们编写了一个返回P列中所有不同节点的子查询。由于要求按节点值升序对输出进行排序,因此要使用ORDER BY子句。

 

查询4

该事务表由transaction_id, user_id, transaction_date,product_id, and quantity(交易ID,用户ID,交易日期,产品ID和数量)组成。需要查询多天来购买产品的用户数量(注意,给定用户可以在一天内购买多个产品)。

 

搞定SQL!5个棘手SQL查询的解决方法

 

  • 本文解决方案

为了解决该查询,不能直接计算user_id的出现次数,由于给定用户在一天中可以多次购买,user_id或许会有多次返回。因此,只有当存在多个不同日期与给定的user_id相关联时,才意味着该用户多天购买了产品。按照相同方法,进行查询编写。(内部查询)

 

  1. SELECT COUNT(user_id) 
  2. FROM 
  3. (SELECT user_id 
  4.  FROM orders 
  5.  GROUP BY user_id 
  6.  HAVING COUNT(DISTINCT DATE(date))> 1 
  7. ) t1 

由于问题询问的是user_id的数量,而不是user_id本身,因此在外部查询中使用 COUNT 。

 

查询5

给定一个订阅表,其中包含每个用户订阅的开始和结束日期。需要编写一个查询,根据与其他用户的日期重叠情况,为每个用户返回true/false。例如,如果user1的订阅周期与其他任何用户重叠,则查询必须为user1返回true。

 

搞定SQL!5个棘手SQL查询的解决方法

 

  • 本文解决方案

经过初步分析,我们可以知道必须将每项订阅与其他订阅进行比较。将userA的开始和结束日期视为startA 和endA,类似地,userB也依此设为startB和endB。如果startA≤endB且endA≥startB,则可以说这两个日期范围重叠。我们来举两个例子,先比较一下U1和U3:

 

  1. startA = 2020–01–01 
  2. endA = 2020–01–31 
  3. startB = 2020–01–16 
  4. endB = 2020–01–26 

这里可以看出,startA(2020–01–01)小于endB(2020–01–26),那么同样,endA(2020–01–31)大于 startB(2020–01–16),因此可以得出结论,日期重叠。类似地,如果比较U1和U4,上述条件就不成立,于是返回FALSE。

这里还必须确保不会将用户与其自己的订阅进行比较。同时希望运行一个左连接,能够自行将用户与满足条件的其他用户进行匹配。现在,我们将创建同一表的两个副本S1和S2。

 

  1. SELECT * 
  2. FROM subscriptions AS s1 
  3. LEFT JOIN subscriptions AS s2 
  4.     ON s1.user_id != s2.user_id 
  5.         AND s1.start_date <=s2.end_date 
  6.         AND s1.end_date >=s2.start_date 

给定条件连接,在日期之间存在重叠的情况下,对于S1中的每个user_id,应该存在来自S2的user_id。

  • 输出

 

搞定SQL!5个棘手SQL查询的解决方法

 

可以看到,以防日期重叠,每个用户都有一个对应用户。对于user1,有2行显示其与2个用户相匹配。对于用户4,对应的ID为空,表示他与其他任何用户都不匹配。现在,将其全部组合在一起,按照s1.user_ID字段进行分组,并检查s2.user_ID不为空的用户的值是否为真。

  • 最终查询

 

  1. SELECT 
  2.     s1.user_id    , (CASE WHEN s2.user_idIS NOT NULL THEN 1 ELSE 0 ENDAS overlap 
  3. FROM subscriptions AS s1 
  4. LEFT JOIN subscriptions AS s2 
  5.     ON s1.user_id != s2.user_id 
  6.         AND s1.start_date <=s2.end_date 
  7.         AND s1.end_date >=s2.start_date 
  8. GROUP BY s1.user_id 

使用 CASE子句根据给定用户的s2.user_id值来标记1和0。最终输出如下:

 

 

搞定SQL!5个棘手SQL查询的解决方法

 

 

 

责任编辑:华轩 来源: 读芯术
相关推荐

2010-10-19 10:25:29

SQL Server连

2013-01-05 13:49:00

2010-09-28 13:53:59

sql text字段

2010-10-19 12:22:02

SQL Server远

2010-10-22 14:35:02

sql server系

2010-09-03 11:05:59

SQL删除

2010-11-08 16:16:57

SQL Server远

2010-11-10 13:42:32

SQL Server删

2020-03-16 08:13:58

SQL性能问题

2010-10-19 11:08:00

SQL Server安

2011-07-22 13:46:41

SQL Server MDAC

2009-05-04 13:43:16

SQL Server置疑数据库恢复

2011-08-01 09:25:32

SQL Server数

2010-10-20 17:21:07

连接SQL Serve

2010-09-16 15:36:26

SQL Server2

2011-04-02 13:57:05

Sql Server

2010-10-21 09:43:15

2010-10-13 17:22:12

MySQL查询乱码

2010-10-19 15:45:32

SQL Server创

2011-08-23 16:45:52

JSP链接SQL Se
点赞
收藏

51CTO技术栈公众号