mysql - 使用 mysql 中的嵌套查询选择查询

Question

我有 2 个包含以下数据的表（比如客户和详细信息）。我需要打印投资最高金额的人的详细信息。

顾客：

+------+------------+--------+
| name | visited    | amount |
+------+------------+--------+
| xyz  | 2018-04-11 |    100 |
| xyz  | 2018-04-11 |   1000 |
| abc  | 2018-02-21 |    500 |
| xyz  | 2018-03-11 |    700 |
| abc  | 2018-01-24 |     50 |
+------+------------+--------+

细节：

+------+------------+
| name | detail     |
+------+------------+
| abc  | california |
| xyz  | hongkong   |
+------+------------+

我找到了使用查询投资最大金额的客户

select name,sum(amount) 
from (
  select name,amount 
  from customer 
  where visited >= DATE_SUB(CURDATE(),INTERVAL 8 MONTH)
) as subtable 
group by name 
order by amount 
limit 1;

我有以下输出

+------+-------------+
| name | sum(amount) |
+------+-------------+
| xyz  |        1800 |
+------+-------------+

现在我如何从详细信息表中找到 xyz 的详细信息？我需要在一个查询中完成这一切。我的输出一定是这样的。

+------+------------+
| name | detail     |
+------+------------+
| xyz  | hongkong   |
+------+------------+

我的卡住了，如何进行？

标签： mysql

Answer 1

select d.name, d.details, sum(c.amount) 
from details d
join customer c on c.name = d.name
where c.visited >= DATE_SUB(CURDATE(),INTERVAL 8 MONTH)
group by d.name 
order by sum(c.amount) desc
limit 1