SQL技术需求：统计并排序FINISHED状态最多的product_id

需求 #

统计表中各product_id对应的FINISHED状态订单数量，并按数量从多到少对product_id进行排序。

表结构定义 #

CREATE TABLE order2 (
  id         int NOT NULL AUTO_INCREMENT,
  status     enum('PENDING','FINISHED') NOT NULL DEFAULT 'PENDING',
  note       varchar(255) DEFAULT NULL,
  client_id  int DEFAULT NULL,
  product_id int NOT NULL,

  PRIMARY KEY (id),  
  KEY fk_order_client2_idx (client_id),
  KEY fk_order_product2_idx (product_id),
  
  CONSTRAINT fk_order_client2  FOREIGN KEY (client_id)  REFERENCES client (id)  ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT fk_order_product2 FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

解决方案 #

SELECT 
    COUNT(order2.product_id) AS total, order2.product_id
FROM
    order2
WHERE
    order2.status = 'FINISHED'
GROUP BY order2.product_id
ORDER BY COUNT(order2.product_id) DESC

代码说明 #

• WHERE order2.status = 'FINISHED'：筛选出所有状态为已完成的订单
• GROUP BY order2.product_id：按product_id分组，聚合统计每个产品的已完成订单数据
• COUNT(order2.product_id) AS total：计算每组的订单数量，并将结果列命名为total
• ORDER BY COUNT(order2.product_id) DESC：按订单数量从多到少排序结果

内容的提问来源于stack exchange，提问作者Espeto