Implementing Multi‑Business Product Sales Statistics Queries Using Row‑Comparison in MySQL

Environment Preparation

Database version: MySQL 5.7.20-log

Table creation SQL:

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='商品销售统计';

Data initialization: 769,063 rows were inserted for testing.

Requirement Background

Each business institution can sell multiple products and each product can be sold by multiple institutions, forming a many‑to‑many relationship. The task is to retrieve sales statistics for a dynamic list of institutions and their associated products, e.g., institution 100001 with products 1000, 1001, 1003, etc.

Loop Query

The straightforward approach is to iterate over the institution list in application code and execute a separate query for each. While the SQL can use indexes, departmental policies forbid looping queries against the database.

OR Concatenation

Using MyBatis dynamic SQL, all institution IDs are concatenated with OR conditions in a single query. This reduces database round‑trips and still leverages indexes, but the generated SQL becomes long when many institutions are involved.

Mixed Filtering

Another MyBatis dynamic‑SQL technique concatenates both business_id and ware_inside_code lists, producing a single query that can use indexes. However, the result set may contain extra rows, requiring an additional filtering step in the application.

Row‑Comparison

SQL‑92 introduced row‑comparison, allowing predicates like (business_id, ware_inside_code) IN ((?, ?), (?, ?), ...) . With MyBatis dynamic SQL, this approach generates a compact query that uses indexes, returns exactly the desired rows, and avoids the drawbacks of the previous methods. Although less familiar, it is standard across relational databases.

Summary

1. The final solution adopts the row‑comparison technique to satisfy the requirement.

2. Multiple implementation options exist; the best choice depends on business constraints and performance considerations.

3. Row‑comparison is part of the SQL‑92 standard introduced in 1992, not a new feature.

4. It is a fundamental capability that has been available in relational databases for decades.

References

《SQL进阶教程》

神奇的 SQL 之 MySQL 执行计划 → EXPLAIN，让我们了解 SQL 的执行过程！

神奇的 SQL 之性能优化 → 让 SQL 飞起来

神奇的 SQL 之擦肩而过 → 真的用到索引了吗