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

dsf9zpds  于 2021-06-23  发布在  Mysql
关注(0)|答案(3)|浏览(325)

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

  1. +------+------------+--------+
  2. | name | visited | amount |
  3. +------+------------+--------+
  4. | xyz | 2018-04-11 | 100 |
  5. | xyz | 2018-04-11 | 1000 |
  6. | abc | 2018-02-21 | 500 |
  7. | xyz | 2018-03-11 | 700 |
  8. | abc | 2018-01-24 | 50 |
  9. +------+------------+--------+

细节:

  1. +------+------------+
  2. | name | detail |
  3. +------+------------+
  4. | abc | california |
  5. | xyz | hongkong |
  6. +------+------------+

我通过查询找到了投资金额最大的客户

  1. select name,sum(amount)
  2. from (
  3. select name,amount
  4. from customer
  5. where visited >= DATE_SUB(CURDATE(),INTERVAL 8 MONTH)
  6. ) as subtable
  7. group by name
  8. order by amount
  9. limit 1;

我有以下输出

  1. +------+-------------+
  2. | name | sum(amount) |
  3. +------+-------------+
  4. | xyz | 1800 |
  5. +------+-------------+

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

  1. +------+------------+
  2. | name | detail |
  3. +------+------------+
  4. | xyz | hongkong |
  5. +------+------------+

我的卡了,怎么办?

von4xj4u

von4xj4u1#

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

  1. select c.name, d.detail
  2. from (
  3. select
  4. name,
  5. sum(amount) as sum_amount
  6. from customer
  7. where visited >= DATE_SUB(CURDATE(),INTERVAL 8 MONTH)
  8. group by name
  9. order by sum_amount
  10. limit 1
  11. ) c
  12. left join details d on c.name = d.name

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

vsaztqbk

vsaztqbk2#

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

  1. +------+------------+
  2. | name | detail |
  3. +------+------------+
  4. | xyz | hongkong |
  5. +------+------------+

query:-

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

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

  1. +------+------------+--------------+
  2. | name | detail | sum(amount) |
  3. +------+------------+--------------+
  4. | xyz | hongkong | 1800 |
  5. +------+------------+--------------+

query:-

  1. select d.name, d.details, sum(cust.amount)
  2. from details d
  3. join customer cust on cust.name = d.name and
  4. cust.visited >= DATE_SUB(CURDATE(),INTERVAL 8 MONTH)
  5. group by d.name
  6. order by sum(cust.amount) desc
  7. limit 1;
展开查看全部
xpcnnkqh

xpcnnkqh3#

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

相关问题