Multi‑Branch Product Sales Statistics Query Using Row Comparison in MySQL

The article begins by describing the environment setup: MySQL 5.7.20 is used, and a table t_ware_sale_statistics is created to store product sales statistics, including fields such as id , business_id , ware_inside_code , daily and period sales, and audit columns.

Sample DDL:

DROP TABLE IF EXISTS `t_ware_sale_statistics`;
CREATE TABLE `t_ware_sale_statistics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `business_id` bigint(20) NOT NULL COMMENT '业务机构编码',
  `ware_inside_code` bigint(20) NOT NULL COMMENT '商品自编码',
  `weight_sale_cnt_day` double(16,4) DEFAULT NULL COMMENT '平均日销量',
  `last_thirty_days_sales` double(16,4) DEFAULT NULL COMMENT '最近30天销量',
  `last_sixty_days_sales` double(16,4) DEFAULT NULL COMMENT '最近60天销量',
  `last_ninety_days_sales` double(16,4) DEFAULT NULL COMMENT '最近90天销量',
  `same_period_sale_qty_thirty` double(16,4) DEFAULT NULL COMMENT '去年同期30天销量',
  `same_period_sale_qty_sixty` double(16,4) DEFAULT NULL COMMENT '去年同期60天销量',
  `same_period_sale_qty_ninety` double(16,4) DEFAULT NULL COMMENT '去年同期90天销量',
  `create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_user` bigint(20) DEFAULT NULL COMMENT '最终修改人',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最终修改时间',
  `is_delete` tinyint(2) DEFAULT '2' COMMENT '是否删除，1：是，2：否',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_business_ware` (`business_id`,`ware_inside_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='商品销售统计';

After creating the table, 769,063 rows of test data are inserted to simulate real‑world volume.

The business requirement is to query sales for multiple business units, each with a dynamic list of product codes, essentially a two‑level list where both the business‑unit list and the product‑code list are not fixed.

Several solution approaches are examined:

Loop Query: Iterate over each business unit in application code and execute a separate SQL statement per unit. While simple and index‑friendly, this violates the team’s rule against looping database calls.

OR Concatenation: Build a single dynamic SQL statement with a long series of OR conditions for each business_id . This also uses indexes and requires only one query, but the SQL becomes unwieldy when many units are involved.

Mixed Filtering: Combine business_id and ware_inside_code lists in a dynamic WHERE clause, then filter the result set in application code. Although index‑friendly, the returned set is larger than needed, requiring extra post‑processing.

Row‑Comparison (SQL‑92): Use the row‑comparison feature (e.g., (business_id, ware_inside_code) IN ((?, ?), (?, ?), ...) ) via MyBatis dynamic SQL. This method remains index‑friendly, executes a single query, and directly returns the exact rows needed, albeit with a syntax that many developers find unfamiliar.

The article concludes that the row‑comparison technique was chosen as the final solution because it satisfies performance, single‑query, and development‑policy requirements, demonstrating that older SQL‑92 features can still provide elegant solutions to modern problems.

Key takeaways: multiple implementation paths exist for complex queries; selecting the optimal one requires balancing business constraints, code maintainability, and database performance; and row‑comparison, though not new, remains a powerful tool for multi‑column filtering.