mysql两个子查询相除_MySQL多个子查询与整个查询

由于这三个聚合来自具有相同WHERE条件的同一个表，因此您不需要子选择。所有三个聚合都在相同的行分组上运行(未指定GROUP BY，因此整个表格为一行)，因此它们都可以直接存在于SELECT列表中。

SELECT

SUM(number) AS number_sum,

MAX(number) AS number_max,

MIN(number) AS number_min

FROM `table`如果任何聚合需要基于不同的条件，您将在WHERE子句中进行过滤，那么您将需要使用子选择来表示不同的条件，或者进行笛卡尔连接。对于仅返回一行的聚合，此子选择和以下LEFT JOIN方法应该是等效的，性能方面：

SELECT

/* Unique filtering condition - must be done in a subselect */

(SELECT SUM(number) FROM `table` WHERE `somecolumn` = `somevalue`) AS number_sum,

MAX(number) AS number_max,

MIN(number) AS number_min

FROM `table`或者等效于上面的查询，您可以对没有ON子句的子查询LEFT JOIN。只有当您知道子查询只返回一行时才应该这样做。否则，您将得到一个笛卡尔积 - 由连接的一侧返回的行数乘以另一侧返回的行数。

如果您需要使用一组WHERE子句条件和一些具有不同WHERE条件的列返回几列，但JOIN的每一侧只有一行，则这很方便。在这种情况下，JOIN应该比使用相同的WHERE子句执行两个子选择更快。

这应该更快......

SELECT

/* this one has two aggregates sharing a WHERE condition */

subq.number_sum_filtered,

subq.number_max_filtered,

/* ...and two aggregates on the main table with no WHERE clause filtering */

MAX(`table`.number) AS number_max,

MIN(`table`.number) AS number_min

FROM

`table`

LEFT JOIN (

SELECT

SUM(number) AS number_sum_filtered,

MAX(number) AS number_max_filtered

FROM `table`

WHERE `somecolumn = `somevalue`

) subq /* No ON clause here since there's no common column to join on... */比这个...

SELECT

/* Two different subselects each over the same filtered set */

(SELECT SUM(number) FROM `table` WHERE `somecolumn` = `somevalue`) AS number_sum_filtered,

(SELECT MAX(number) FROM `table` WHERE `somecolumn` = `somevalue`) AS number_max_filtered,

MAX(`table`.number) AS number_max,

MIN(`table`.number) AS number_min

FROM

`table`