mysql-在另一个子查询中使用子查询结果

vawmfj5a 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(398)

我使用下表进行查询：
顾客

id  | name   | lastname
---------------------------------
 1  | john   | text
 2  | rock   | text
 3  | jane   | text
 4  | jack   | text

订单_

id_order | customer_id | ...
---------------------------------
 1       |      1      | ...
 2       |      2      | ...
 3       |      3      | ...
 4       |      1      | ...
 5       |      2      | ...

我想实现一个查询来显示最大订单数的客户的名字和姓氏。如果两个或两个以上的客户有相同的最大订单数，我想显示。
到目前为止，我已经完成了以下子查询：

SELECT name, lastname FROM customer
WHERE customer.id IN 
(SELECT * FROM (SELECT order_.customer_id FROM order_
                INNER JOIN customer
                WHERE order_.customer_id = customer.id
                GROUP BY order_.customer_id
                ) temp
)

我想我必须使用最后一个子查询的max函数来得到结果，我只是不知道怎么做。有什么想法吗？

sql mysql subquery

来源：https://stackoverflow.com/questions/51553631/mysql-use-subquery-result-in-another-subquery

4条答案

按热度按时间

4xrmg8kj1#

select c.id as 'id',c.name as 'name',c.lastname as 'lastname',count(o.id_order) as 'total_orders'
 from customer c
 inner join order_ o
 ON c.id = o.customer_id
 group by c.id
 having total_orders = (select max(orders)
 from (select count(o1.id_order) as 'orders'
       from customer c1
       inner join order_ o1
        ON c1.id = o1.customer_id
        group by c1.id) derived)

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

6xfqseft2#

DROP TABLE customers;

CREATE TABLE customers
(customer_id  SERIAL PRIMARY KEY
,name VARCHAR(12) NOT NULL
,lastname VARCHAR(12) NOT NULL
);

INSERT INTO customers VALUES
(1,'john','text'),
(2,'rock','text'),
(3,'jane','text');

DROP TABLE orders;

CREATE TABLE orders
(order_id SERIAL PRIMARY KEY
,customer_id INT NOT NULL
);

INSERT INTO orders VALUES
(1,1),
(2,2),
(3,3);

SELECT a.*
  FROM 
     ( SELECT c.*
            , COUNT(*) total 
         FROM customers c
         JOIN orders o
           ON o.customer_id = c.customer_id
        GROUP 
           BY customer_id
    ) a
 JOIN   
    ( SELECT MAX(x.total) max_total
        FROM 
           ( SELECT customer_id
                  , COUNT(*) total 
               FROM orders 
              GROUP 
                 BY customer_id
           ) x
   ) b
  ON b.max_total = a.total;

+-------------+------+----------+-------+
| customer_id | name | lastname | total |
+-------------+------+----------+-------+
|           1 | john | text     |     1 |
|           2 | rock | text     |     1 |
|           3 | jane | text     |     1 |
+-------------+------+----------+-------+

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

gkn4icbw3#

你可以做：

SELECT c.name, c.lastname, count(o.id_order) 
FROM order_ o INNER JOIN 
     customer c
     on o.customer_id = c.id
GROUP BY c.name, c.lastname
ORDER BY count(o.id_order) DESC
LIMIT 1;

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

tct7dpnv4#

这是更复杂的，因为你想要重复。假设 customer_id 定义客户而不是名称，您可以执行以下操作：

select c.id, c.name, c.lastname
from customer c join
     order_ o
     on o.customer_id = c.id
group by c.id, c.name, c.lastname
having count(*) = (select count(*)
                   from order_ o2
                   group by o2.customer_id
                   order by count(*) desc
                   limit 1
                  );

请注意 having 子句不需要 join 回到 customer --假设 id 是对客户的恰当定义。

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

我来回答

mysql-在另一个子查询中使用子查询结果

4条答案

相关问题

热门标签

最新问答