这是我的架构和示例数据：
-- -----------------------------------------------------
-- 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（））。但是我想返回一个或多个具有最大订单数的行。
因为您只需要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)customer_customer_id | count_r --------------------：| ------： 1 | 2 2 | 2 3 | 2
db <> 在这里拨弄
为了从Orders中选择所有想要的列，并且仅仅包含ID（具有最高数量的订单），我必须做两个嵌套的Queries才能获得最高的Orders数量。
这HAVING是必需的，因为我必须对count_r列运行“最大数”，而我无法在WHERE子句上执行此操作。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句