Implementing Multi‑Business‑Unit Sales Statistics Queries with Row‑by‑Row Comparison in MySQL

This article describes a practical problem: given many business units, each selling multiple products, how to retrieve sales statistics for specific unit‑product combinations in a single MySQL query without violating department coding rules that forbid looping over the database.

Environment preparation – The target database is MySQL 5.7.20‑log. The required table t_ware_sale_statistics is created with the following 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='商品销售统计';

Requirement background – Business units and products have a many‑to‑many relationship. The goal is to fetch sales data for a dynamic list of unit‑product pairs, e.g., unit 100001 with products 1000,1001,1003; unit 100002 with products 1003,1004; etc.

Solution attempts

Loop query: iterate over each business unit in application code and execute a separate query. Simple but prohibited by the department’s coding standards.

OR concatenation: build a single SQL with many OR conditions using MyBatis dynamic SQL. Works and uses indexes, but the query becomes excessively long when many units are involved.

Mixed filter: concatenate business_id and ware_inside_code lists together and filter the result set after a single query. The result set is larger than needed, requiring an extra filtering step.

Row‑by‑row comparison (SQL‑92 feature): use a row value comparison such as (business_id, ware_inside_code) IN ((100001,1000),(100001,1001),...) . This approach keeps the query short, lets the optimizer use indexes, and satisfies the “no loop” rule.

Final choice – The author selected the row‑by‑row comparison method because it is concise, index‑friendly, and complies with the coding constraints. The article notes that this technique has existed since the SQL‑92 standard and is supported by all major relational databases.

Summary points

Row‑by‑row comparison was chosen to implement the requirement.

Multiple implementation paths exist; the best one balances performance, readability, and organizational constraints.

Row‑by‑row comparison is a mature SQL‑92 feature, not a new trick.

References

《SQL进阶教程》

MySQL execution plan analysis (EXPLAIN)

SQL performance optimization guides