时间戳

首页 > 解决方案 > 具有子句 MySql 中的嵌套聚合函数

mysql - 具有子句 MySql 中的嵌套聚合函数

问题描述

这是我的架构和示例数据：

-- -----------------------------------------------------
-- Table `Music_store`.`customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Music_store`.`customers` (
  `customer_id` INT NOT NULL AUTO_INCREMENT,
  `customer_firstname` VARCHAR(45) NOT NULL,
  `customer_lastname` VARCHAR(45) NOT NULL,
  `customer_mobno` VARCHAR(20) NOT NULL,
  `music_store_store_id` INT NOT NULL,
  PRIMARY KEY (`customer_id`),
  INDEX `fk_customers_music_store1_idx` (`music_store_store_id` ASC) VISIBLE,
  UNIQUE INDEX `customer_mobno_UNIQUE` (`customer_mobno` ASC) VISIBLE,
  CONSTRAINT `fk_customers_music_store1`
    FOREIGN KEY (`music_store_store_id`)
    REFERENCES `Music_store`.`music_store` (`store_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Music_store`.`orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Music_store`.`orders` (
  `order_id` INT NOT NULL AUTO_INCREMENT,
  `order_date` DATETIME NOT NULL,
  `ship_amount` INT NOT NULL,
  `tax_amount` INT NOT NULL,
  `ship_date` DATETIME NOT NULL,
  `customers_customer_id` INT NOT NULL,
  PRIMARY KEY (`order_id`),
  INDEX `fk_orders_customers1_idx` (`customers_customer_id` ASC) VISIBLE,
  CONSTRAINT `fk_orders_customers1`
    FOREIGN KEY (`customers_customer_id`)
    REFERENCES `Music_store`.`customers` (`customer_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

INSERT INTO customers (customer_firstname, customer_lastname, customer_mobno, music_store_store_id)
        VALUES 
("Prasanth", "Pandiselvan", "456-986-1234", 1),
("Dan", "Druar", "347-234-9876", 1),
("Lukeman", "Hakkim", "488-234-0987", 2),
("Paul", "Antony", "356-257-1233", 2),
("Nikhil", "Reddy", "678-234-1345", 3),
 ("Saravana", "Kumar", "456-986-2671", 3),
("Mugil", "Pandian", "877-285-3322", 4);

INSERT INTO orders (order_date, ship_amount, tax_amount, ship_date, customers_customer_id)
        Values
("2020-03-03 22:59:52", "127", 4,  "2020-03-04 22:59:52", 1),
("2020-03-13 22:59:52", "158", 5,  "2020-03-15 22:59:52", 1),
("2020-02-08 22:59:52", "201", 5,  "2020-02-10 22:59:52", 2),
("2020-02-25 22:59:52", "300", 6,  "2020-02-27 22:59:52", 2),
("2020-05-14 22:59:52", "500", 7,  "2020-05-16 22:59:52", 3),
("2020-02-08 22:59:52", "557", 7,  "2020-02-12 22:59:52", 3),
("2020-06-19 22:59:52", "658", 7,  "2020-06-21 22:59:52", 4);

例如，这将是查询的查询结果

select customers.customer_id, count((orders.customers_customer_id)) as mac
from customers inner join orders on customers.customer_id = orders.customers_customer_id
group by orders.customers_customer_id;

结果：

# customer_id, count_of_orders
     '1',         '2'
     '2',         '2'
     '3',         '2'
     '4',         '1'

我想获得最大订单的客户/客户。我无法使用 MAX(count())。但是我想返回一个或多个具有最大订单的行。

标签： mysql

解决方案

由于您只需要 id，因此您不需要加入客户表

SELECT customers_customer_id, COUNT(*) count_r 
FROM orders
GROUP BY customers_customer_id
HAVING count_r = ( 
SELECT MAX(count_r) FROM (SELECT COUNT(*) count_r FROM `orders` GROUP BY customers_customer_id) t1)

customers_customer_id | count_r
--------------------: | ------:
                    1 | 2
                    2 | 2
                    3 | 2

db<>在这里摆弄

要从 Orders 中选择所有想要的列，并且只包括 ids ，订单数量最多的，我必须做两个嵌套查询以获得最高数量的订单。

  1. 为曾经的customers_customer_id选择订单数
  2. 因为我只需要最高计数，所以我从我在第一个 Select 中收集的所有计数中选择 MAX
  3. 我用它来过滤掉订单数量最多的所有customers_customer_ids。

这HAVING是必需的，因为我必须针对列 count_r 运行最大数量，而这在 WHERE 子句中是无法做到的。

推荐阅读