时间戳

首页 > 解决方案 > 子查询和公用表表达式

mysql - 子查询和公用表表达式

问题描述

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

编写一个查询来计算 health.user_logs 表中的重复数据记录

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

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

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

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

何时决定 select count 语句应该在开头还是结尾？

标签： mysqlsqlsubquery

解决方案

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

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是您可以多次访问同一个子查询：

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

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

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 ...

推荐阅读