时间戳

首页 > 解决方案 > 如何为以下数据编写高级复杂的 SQL 查询？

mysql - 如何为以下数据编写高级复杂的 SQL 查询？

问题描述

如何编写复杂的 SQL 查询？

以下信息是使用不同服务的用户的旧数据，以及特定 Service_Area 和 Service_Sector 的服务状态和最后更改日期。所有用户都在处理多个服务，其中一些服务对于特定的 Service_Area 和 Service_Sector 仍然处于活动状态。

文字要求：

当用户的服务处于非活动状态时，我需要选择所有列，并且只记录该用户的特定 Service_Area 和 Service_Sector。

如前所述，用户可以在 Service_Area 和 Service_Sector 组合上拥有多个服务。如果用户的 Service_Area 和 Service_Sector 上有多个服务，那么如果所有服务都处于非活动状态，我们需要选择最后更改的记录（我的意思是，当两者之间有一条服务状态为活动的记录时，那么我们无法从该用户的该 Service_Area 和 Service_Sector 中选择任何用户数据）。

场景：

案例1： 当有5个服务时，所有服务都处于非活动状态，我们需要选择最后更改的记录。

情况 2： 当 5 个服务中至少有一个处于活动状态的服务时，我们无法从该用户的特定 Service_Area 和 Service_Sector 中选择该用户数据。

案例 3 当该用户的该 Service_Area 和 Service_Sector 上只有一个服务时，它处于非活动状态，该记录也将被选中。

关于表：

Location_Code、User_Id、Service_Area、Service_Sector、Service 将始终进行唯一的组合。并且这个组合上可能有很多服务Location_Code、User_Id、Service_Area、Service_Sector。

请在下面找到带有结果表的示例表（显示预期输出）。

样品表：

Loc_Code 用户身份 服务区 服务部门 服务 地位 Last_Changed
101 1001 C 1 画 不活跃 2020 年 11 月 28 日
101 1002 一种 1 木头 不活跃 2020 年 12 月 7 日
101 1002 一种 1 打扫 积极的 2020 年 11 月 23 日
101 1002 一种 1 害虫 不活跃 2020 年 12 月 7 日
101 1002 一种 2 光 不活跃 2020 年 12 月 7 日 `
101 1002 乙 2 交流电 不活跃 2020 年 11 月 28 日
101 1002 乙 2 加热器 不活跃 2020 年 11 月 30 日

结果表：

总共选择了 3 条记录。

  1. 当用户在特定 Service_Area 和 Service_Sector 上处理单个非活动服务时，选择第一条记录。
  2. 之所以选择第二条记录，是因为 Location_Code、User_Id、Service_Area、Service_Sector 组合上只有一个服务，它是非活动的。
  3. 选择第三条记录是因为Location_Code、User_Id、Service_Area、Service_Sector组合列表2-非活动服务，选择了最后一次更改的服务。
Loc_Code 用户身份 服务区 服务部门 服务 地位 Last_Changed
101 1001 C 1 画 不活跃 2020 年 11 月 28 日
101 1002 一种 2 光 不活跃 2020 年 12 月 7 日
101 1002 乙 2 加热器 不活跃 2020 年 11 月 30 日

标签： mysqlsqldatabase

解决方案

SQL | WITH clause
Last Updated: 05-09-2019
The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.

The clause is used for defining a temporary relation such that the output of this temporary relation is available and is used by the query that is associated with the WITH clause.
Queries that have an associated WITH clause can also be written using nested sub-queries but doing so add more complexity to read/debug the SQL query.
WITH clause is not supported by all database system.
The name assigned to the sub-query is treated as though it was an inline view or table
The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database.
Syntax:

WITH temporaryTable (averageValue) as
    (SELECT avg(Attr1)
    FROM Table),
    SELECT Attr1
    FROM Table
    WHERE Table.Attr1 > temporaryTable.averageValue;


In this query, WITH clause is used to define a temporary relation temporaryTable that has only 1 attribute average value. averageValue holds the average value of column Attr1 described in relation Table. The SELECT statement that follows the WITH clause will produce only those tuples where the value of Attr1 in relation Table is greater than the average value obtained from the WITH clause statement.

Note: When a query with a WITH clause is executed, first the query mentioned within the  clause is evaluated and the output of this evaluation is stored in a temporary relation. Following this, the main query associated with the WITH clause is finally executed that would use the temporary relation produced.



Queries

Example 1:  Find all the employee whose salary is more than the average salary of all employees.
Name of the relation: Employee

EMPLOYEEID  NAME    SALARY
100011  Smith       50000
100022  Bill        94000
100027  Sam         70550
100845  Walden      80000
115585  Erik        60000
1100070 Kate        69000

SQL Query:

WITH temporaryTable(averageValue) as
    (SELECT avg(Salary)
    from Employee), 
        SELECT EmployeeID,Name, Salary 
        FROM Employee, temporaryTable 
        WHERE Employee.Salary > temporaryTable.averageValue;
Output:

EMPLOYEEID  NAME    SALARY
100022  Bill        94000
100845  Walden      80000
Explanation: The average salary of all employees is 70591. Therefore, all employees whose salary is more than the obtained average lies in the output relation.

推荐阅读