子查询和公用表表达式 [英] sub query and common table expression

问题描述

我想了解如何在子查询与公用表表达式中构造查询:请参见下面的示例.

Hi I want to understand how to structure query in subquery vs common table expression: See example below.

编写查询以计算 health.user_logs 表中重复数据删除的记录

方法一:使用子查询:select count语句在开头

Approach 1: use sub query: select count statement is in the beginning

SELECT COUNT(*)
FROM (
  SELECT DISTINCT *
  FROM health.user_logs
) AS subquery;

方法二:使用公表表达式:select count语句到底是什么?

Approach 2: use common table expression: select count statement is in the end?

WITH deduped_logs AS (
  SELECT DISTINCT *
  FROM health.user_logs
)
SELECT COUNT(*)
FROM deduped_logs;

什么时候决定 select count 语句应该在开头还是结尾?

When to decide if the select count statement should be in the beginning or in the end?

推荐答案

大多数情况下，这与个人偏好有关，即您更喜欢什么并且认为更具可读性.

Most often this is about personal preferences, i.e. what you like better and consider more readable.

SELECT ...
FROM
(
  SELECT ...
  FROM some_table
  WHERE ...
) AS subquery
JOIN another_table ON ...

和

WITH
(
  SELECT ...
  FROM some_table
  WHERE ...
) AS subquery
SELECT ...
FROM subquery
JOIN another_table ON ...

是等价的，一个和另一个一样好.WITH 子句的优点之一是您可以多次访问同一个子查询:

are equivalent and one is as good as the other. One advantage with the WITH clause is that you can access the same subquery more than once:

WITH
(
  SELECT ...
  FROM some_table
  WHERE ...
) AS subquery
SELECT ...
FROM subquery s1
JOIN subquery s2 ON s2.type = s1.type AND s2.id < s1.id

另一个优点是您可以逐步构建查询而无需在子查询中嵌套子查询(至少不可见)，因此查询可能被认为更具可读性:

Another advantage is that you can build your query step by step without nesting subquery in subquery (at least not visibly), so the query may be considered more readable:

WITH all_jobs AS (...)
   , technical_jobs AS (... FROM all_jobs ...)
   , well_paid_technical_jobs AS (... FROM technical_jobs ...)
SELECT *
FROM well_paid_technical_jobs
WHERE ...

对比

SELECT *
FROM 
(
  SELECT ...
  FROM 
  (
    SELECT ...
    FROM 
    (
      ...
    ) all_jobs
    WHERE ...
  ) technical_jobs
  WHERE ...
) well_paid_technical_jobs
WHERE ...

这篇关于子查询和公用表表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！