SQL 102：中级SQL-DEV365 开发者社区

这篇文章是Introduction to SQL的延续。但是，如果您熟悉基本的SQL概念，则可以通过本文阅读以获取更多提前概念。

情况何时

评估一组条件，就像else语句一样，并在条件为真时返回相应的结果。
相应的语法如下所示：

SELECT column1,
       column2,
       CASE WHEN condition1 THEN result1
            WHEN condition2 THEN result2
           ...(other when conditions)
           ELSE default_result
       END AS new_column_name
FROM table_name;

让我们看一个更具体的示例。假设我们有一个与不同的球队以及他们的比赛方式的比赛表，并且我们想确定他们在家比赛时是否赢了，我们将有以下查询。

SELECT id,
       name,
       CASE WHEN home_goal > away_goal THEN 'Its a home win'
            WHEN home_goal < away_goal THEN 'Its a home loss'
            ELSE 'Its a tie'
       END AS home_scores
INTO home_analysis
FROM matches;

上面代码的输出是三列 iD，name 和 home_scores ，存储在home_analysis表中。
我们使用案例语句过滤home_scores列的结果。

您还可以使用案例语句根据某个子句的结果来汇总数据，因为汇总函数不能直接在WHERE子句中使用。

假设您有一张名为“员工”列的桌子，上面有“名称”，“薪金”和“部门”。您想创建一个报告，以显示每个部门的总工资，并根据总薪水将每个部门归类为“高薪”或“低薪”。结果应包括在2023年1月1日或之后仅雇用的员工

SELECT department,
       SUM(salary) as total_salary
       CASE WHEN SUM(salary) >= 1000000 THEN 'High paying dpt'
            ELSE 'Low paying dpt'
       END AS dpt_salary
FROM employees
WHERE hire_date >= '2023-01-01'
GROUP BY department;

薪水的总和只有在任何类别中满足该条款。

。

子征服

它们是其他查询中发现的查询，因此也称为嵌套查询。该子查询首先执行，然后在主查询中使用结果。这使您可以执行涉及多个表和条件的更复杂的查询。
语法如图所示：

SELECT column
FROM (SELECT column
      FROM table) AS subquery

可以将一个子查询放在查询的任何部分，例如
的选择，从何处或组允许您将汇总值与详细数据进行比较，并根据需要重塑数据。
还使用子查询来组合无法连接的数据。

Where子句中的子查询

它用于根据另一个表的结果来过滤数据。例如，如果您有客户表和订单表，则可以使用子查询查找所有至少一个订单的客户。

SELECT *
FROM customers
WHERE id IN (SELECT c_id FROM orders);

从子句中的子查询

它们用于重组和转换要选择的数据。确保您别名该子查询，以便于参考。
让我们使用客户和订单表供参考，并在2022/2023季节查看每个客户的订单。

SELECT name,
       order
FROM (SELECT c.name AS name 
             o.id AS order
      FROM customers AS c
      INNER JOIN orders AS o
      ON c.order = o.id)
      AS customer_order
WHERE season = '2022/2023';

我们还可以在语句中创建多个子查询。

SELECT customers.customer_id,
      customers.first_name, '
      customers.last_name, 
      orders.total
FROM (
   SELECT customer_id, 
          SUM(price * quantity) AS total
   FROM order_items
   GROUP BY customer_id
   ) AS orders
INNER JOIN customers 
ON orders.customer_id = customers.customer_id
WHERE orders.total > (
   SELECT AVG(total)
       FROM (
          SELECT customer_id, 
                 SUM(price * quantity) AS total
          FROM order_items
          GROUP BY customer_id
         ) AS order_totals
)

在此示例中，我们在从子句中使用了两个子查询。第一个子查询计算每个客户在所有订单上的总金额。第二个子查询计算所有客户的平均总额。

然后，主要查询与客户表连接订单子查询，以检索客户的ID，名字和姓氏，以及他们的总额。最后，该条款将结果过滤到仅包括其总额支出的客户大于所有客户的平均总金额。

选择语句中的子Quries

它们可用于根据来自另一个表的数据进行计算。例如，如果您有订单表和产品表，则可以使用子查询来计算每个订单的总价。

SELECT id, 
       (SELECT SUM(price * quantity)
        FROM products 
         WHERE order.id = products.order_id) AS total_price
FROM orders;

正如我们在上面看到的那样，它也可以用来从表中检索信息，从产品表中获得了我们商品的总数。
然而，最大的问题是，考虑到每个子查询需要额外的计算能力，建议使用次数。选择子查询时需要考虑某些因素：
1.DATA复杂性
当需要需要多个表和条件的复杂数据查询时，子查询很有用。如果查询涉及多个子查询或多个嵌套子征，它可能很难阅读和维护。在这种情况下，最好将查询分解为较小的部分或考虑使用其他方法。

2.Data卷
处理大型数据集时，子征服的执行速度可能很慢。如果查询涉及大量数据，则最好使用JOIN或其他类型的查询。

3.绩效
子查询的性能取决于所使用的数据库引擎。一些数据库引擎可以优化子查询以提高性能。在决定是否使用子查询时考虑性能的影响很重要。

4.维护性
子查询可能会使查询更难阅读和维护。重要的是要考虑使用子查询是否会使查询从长远来看或多或少可维护。
5.试验重复使用
如果在查询的不同部分或跨多个查询中多次使用相同的子查询，则创建视图或临时表而不是使用子查询可能更有效。

加在一起的子征服

这是一种子查询，它使用外部查询中的值通过引用主查询中的一个或多个列来生成结果。它总是为每一个新的行重新运行。

SELECT *
FROM employees AS e
WHERE e.salary > (
     SELECT AVG(salary)
     FROM employees
     WHERE dpt_id = e.dpt_id
)

也很有可能具有嵌套子查询。让我们选择员工的姓名和他在今年第一季度获得的平均工资。

SELECT
     e.name AS employee,
     (SELECT AVG(jan_pay + feb_pay + mar_pay),
      FROM salary AS s
      WHERE s.empl_id = e.id
          AND id IN (
               SELECT id 
               FROM salary 
               WHERE year == 2023
               ) AS avg_salary
FROM employees AS e
GROUP BY employee;

选项条款中的子查询取决于工资和员工ID的平等，这也取决于薪水表中的年份列。

通用表表达式（CTE）

子Quries是将多个无关表并简化计算的好方法。但是，具有多个嵌套子征服可能很难读取和理解，如上所述。这就是为什么可以使用CTE的解决方案的原因。 CTE在主要查询之前使用A语句声明，并在from语句中稍后引用。
可以在SQL中的选择，插入，更新或删除语句中引用它。

WITH s AS (
   SELECT emp_id, id,
         AVG(jan_pay + feb_pay + mar_pay) AS q1
   FROM salary
   WHERE year == 2023
   GROUP BY id
)
SELECT e.name AS employee,
       s.q1
FROM employees AS e
INNER JOIN s
ON e.id = s.emp_id

ctes比亚克雷斯更好，因为它们仅运行一次然后存储在内存中，因此减少了运行查询所需的时间。
您也可以拥有多个CTE，并由逗号隔开，但请确保您以各自的顺序加入。

WITH max_salary AS (
  SELECT department_id, MAX(salary) AS max_salary
  FROM salary
  GROUP BY department_id
), 
avg_salary AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM salary
  GROUP BY department_id
)
SELECT employees.*, max_salary.max_salary, avg_salary.avg_salary
FROM employees
JOIN max_salary ON employees.department_id = max_salary.department_id
JOIN avg_salary ON employees.department_id = avg_salary.department_id
WHERE employees.salary = max_salary.max_salary

窗口功能

他们允许您在行上执行一组功能或已经生成的结果集。
它们被称为“窗口”功能，因为它们在行的“窗口”上操作，而不是在单个行上定义。

SELECT transaction_id, 
       sales_amount, 
       SUM(sales_amount) OVER() AS total_sales
FROM sales_transactions

这总和每个量sales_amount并将其显示为total_sales。
我们可以使用cark（）窗口函数生成等级。

SELECT transaction_id, 
       sales_amount, 
       SUM(sales_amount) RANK() OVER() AS total_sales
FROM sales_transactions

请注意，在处理其他查询后会产生窗口函数。它使用生成的表查找其结果。
*窗口分区组一行的结果集中到一个较小的组中，以便将窗口函数分别应用于这些子集。

SELECT department_id, 
       employee_id, 
       salary, 
       AVG(salary) OVER (PARTITION BY department_id
                              ) AS avg_salary
FROM employees

结果将首先分区，然后计算平均工资。

滑动窗口

他们使我们能够相对于数据集的当前窗口执行计算。
该窗口由“滑动”或基于指定订购的分区移动的一排愤怒来定义。
语法如图所示：

ROWS BETWEEN <start> AND <finish>

开始和结束可以用前面的（以后），以下（随后），无界的先前（分区中的所有行和包括当前行），无限之后，当前行

SELECT date,
       jan_pay,
       SUM (jan_pay)
       OVER(ORDER BY date ROWS BETWEEN
            UNBOUNDED PRECEDING AND CURRENT ROW)
       AS jan_total
FROM salary
WHERE year = 2023;