mysql - 在单个外部 SELECT 查询中使用来自 SELECT 子查询的两个聚合值
问题描述
对于相关的表和列(比以下更多),我有一个customer
带有cust_id
和state
列的account
表和一个带有account_id
、cust_id
和avail_balance
列的表。
示例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 |
这是我希望优化的查询示例。
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 个结果):
| cust_id |
|--------:|
| 13 |
account
上面的问题是代码冗余以及我们如何使用两个子查询多次遍历表。我希望可以使用一个子查询从表中获取最大值cust_id
,并在外部查询中使用这两个标量。例如,查询的形状可能如下所示:avail_balance
account
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');
显然,上面的方法是行不通的。实现上述代码冗余较少且仅在一个查询中(可能包含子查询)的最有效方法是什么?
解决方案
您可以将 2 个子查询合并为 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'
请参阅演示。
结果:
> | cust_id |
> | ------: |
> | 13 |
推荐阅读
- css - 如何更改 pTooltip 位置以将更长的文本视为 ui-tooltip-top
- apache - CentOS 7 中的 Apache 升级
- php - 是否可以使用 twig 模板通过 makerbundle 生成 php 文件
- kotlin - RealmLists 中只允许具体的 Realm 类。接口和抽象类都不允许
- android - 是否可以以不同的名称上传两个几乎相同的应用程序?
- android-studio - 清单合并失败的android studio
- spring-kafka - IntegrationTests 的正确@EmbeddedKafka 用法是什么?
- python - Python mmap - 缓慢访问文件末尾[带有测试代码]
- javascript - 如何在号码输入字段中以这种“xxx-xxx-xxxx”格式输入电话号码
- solr - SolrCloud 总是从 Example 开始。Solr Home 配置不起作用