在单个外部 SELECT 查询中使用来自 SELECT 子查询的两个聚合值 [英] Using two aggregate values from a SELECT subquery in a single outer SELECT query

问题描述

对于相关的表和列(比下面的要多得多)，我有一个 customer 表，其中包含 cust_id 和 state列和带有 account_id、cust_id 和 avail_balance 列的 account 表.

For relevant tables and columns (a lot more exist than the following), I have a customer table with cust_id and state columns and an account table with account_id, cust_id, and avail_balance columns.

示例 customer 表:

| cust_id | state |
|--------:|-------|
|       1 | MA    |
|       2 | MA    |
|       3 | MA    |
|       4 | MA    |
|       5 | NH    |
|       6 | MA    |
|       7 | MA    |
|       8 | NH    |
|       9 | MA    |
|      10 | NH    |
|      11 | MA    |
|      12 | NH    |
|      13 | MA    |

示例 account 表:

| account_id | cust_id | avail_balance |
|-----------:|--------:|--------------:|
|          1 |       1 |       1057.75 |
|          2 |       1 |           500 |
|          3 |       1 |          3000 |
|          4 |       2 |       2258.02 |
|          5 |       2 |           200 |
|          7 |       3 |       1057.75 |
|          8 |       3 |        2212.5 |
|         10 |       4 |        534.12 |
|         11 |       4 |        767.77 |
|         12 |       4 |       5487.09 |
|         13 |       5 |       2237.97 |
|         14 |       6 |        122.37 |
|         15 |       6 |         10000 |
|         17 |       7 |          5000 |
|         18 |       8 |       3487.19 |
|         19 |       8 |        387.99 |
|         21 |       9 |        125.67 |
|         22 |       9 |       9345.55 |
|         23 |       9 |          1500 |
|         24 |      10 |      23575.12 |
|         25 |      10 |             0 |
|         27 |      11 |       9345.55 |
|         28 |      12 |      38552.05 |
|         29 |      13 |         50000 |

这是我希望优化的查询示例.

Here is an example of a query that I wish to optimize.

SELECT account.cust_id
FROM account, customer
WHERE
      account.cust_id = customer.cust_id
  AND customer.state = 'MA'
  AND customer.cust_id >
      (SELECT MAX(customer.cust_id) AS max_nh_cust_id
       FROM account, customer
       WHERE
             account.cust_id = customer.cust_id
         AND state = 'NH')
  AND account.avail_balance >
      (SELECT MAX(avail_balance) AS max_nh_avail_balance
       FROM account, customer
       WHERE
             account.cust_id = customer.cust_id
         AND state = 'NH');

上述示例数据的预期结果(如果数据不同，可能不只是 1 个结果):

Expected result for the above example data (may not be just 1 result if data is different):

| cust_id |
|--------:|
|      13 |

上面的问题是代码冗余以及我们如何使用两个子查询多次遍历 account 表.我希望可以使用一个子查询从 account 表中获取最大的 cust_id 和 avail_balance 并使用这两个标量外部查询.例如，查询的形状可能如下所示:

The problem with the above is the code redundancy and how we have to iterate multiple times through the account table with the two subqueries. I was hoping that it would be possible to use one subquery to get the maximum cust_id and avail_balance from the account table and use those two scalars in the outer query. For example, the shape of the query might look something like this:

SELECT account.cust_id
FROM account, customer
WHERE
      account.cust_id = customer.cust_id
  AND (customer.cust_id > max_nh_cust_id AND account.avail_balance) > max_nh_avail_balance) IN
      (SELECT MAX(customer.cust_id) AS max_nh_cust_id, MAX(avail_balance) AS max_nh_avail_balance
       FROM account, customer
       WHERE
             account.cust_id = customer.cust_id
         AND state = 'NH');

显然，上述方法不起作用.以较少的代码冗余并且仅在一个查询(可能包含子查询)中实现上述内容的最有效方法是什么?

Obviously, the above does not work. What is the most efficient way of implementing something like the above with less code redundancy and only in one query (that may contain subqueries)?

推荐答案

可以将2个子查询合并为1个:

You can merge the 2 subqueries into 1:

SELECT MAX(c.cust_id) AS max_nh_cust_id,
       MAX(a.avail_balance) AS max_nh_avail_balance 
FROM account a INNER JOIN customer c 
ON a.cust_id = c.cust_id 
WHERE c.state = 'NH'

然后像这样加入:

SELECT a.cust_id
FROM account a 
INNER JOIN customer c ON a.cust_id = c.cust_id
INNER JOIN (
  SELECT MAX(c.cust_id) AS max_nh_cust_id,
         MAX(a.avail_balance) AS max_nh_avail_balance 
  FROM account a INNER JOIN customer c 
  ON a.cust_id = c.cust_id 
  WHERE c.state = 'NH'
) t ON c.cust_id > t.max_nh_cust_id AND a.avail_balance > t.max_nh_avail_balance
WHERE c.state = 'MA'

请参阅演示.
结果:

See the demo.
Results:

> | cust_id |
> | ------: |
> |      13 |

这篇关于在单个外部 SELECT 查询中使用来自 SELECT 子查询的两个聚合值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！