在mysql中使用嵌套查询选择query

dsf9zpds 于 2021-06-23 发布在 Mysql

关注(0)|答案(3)|浏览(320)

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

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

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

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

我的卡了，怎么办？

mysql

来源：https://stackoverflow.com/questions/51724336/select-query-using-nested-queries-in-mysql

3条答案

按热度按时间

von4xj4u1#

你需要 JOIN 到明细表。我决定使用子查询来限制为联接操作查找的行数。

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

请注意，我删除了您的子查询，因为它是多余的。

赞(0）回复(0）举报 2021-06-23

vsaztqbk2#

要获得以下输出，可以使用join编写查询，如下所示：
output:-

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

query:-

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

如果要同时打印总和金额，则只需在select语句中再添加一列：
output:-

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

query:-

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

赞(0）回复(0）举报 2021-06-23

xpcnnkqh3#

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

赞(0）回复(0）举报 2021-06-23

我来回答

在mysql中使用嵌套查询选择query

3条答案

相关问题

热门标签

最新问答