Mastering Multi‑Business Sales Queries with Row‑Comparison in MySQL

Environment Preparation

Database version:

MySQL 5.7.20-log

Table Definition

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

Prepared 769,063 rows of data.

Requirement Background

Business units sell products; the relationship between units and products is many‑to‑many. The task is to query sales figures for several stores and their respective products, where both the list of stores and the list of products are dynamic.

Loop Query

Idea: iterate over the business‑id list in application code and execute a separate query for each unit. Simple and index‑friendly, but prohibited by department development standards.

Example SQL (single‑unit query) shown in the following image:

Index usage confirmed.

OR Concatenation

Use MyBatis dynamic SQL to concatenate many OR conditions into a single query. The query remains index‑friendly but becomes long when many business units are involved.

Generated SQL example:

Index usage confirmed.

Mixed Filter

Combine the business_id list and ware_inside_code list in one dynamic SQL statement. The result set is larger than needed, requiring an additional filtering step.

SQL example:

Index usage confirmed.

Row Comparison

SQL‑92 introduces row‑value comparison, allowing predicates like (=, <, >, IN) to operate on tuples. Implemented via MyBatis dynamic SQL, this approach yields a single, index‑friendly query that directly returns the desired rows, though it may be less familiar.

SQL example:

Index usage confirmed.

Summary

1. Row‑comparison was finally chosen to satisfy the requirement.

2. A single requirement can be solved in many ways; the best solution balances business needs and technical constraints.

3. Row‑comparison is part of the SQL‑92 standard (1992) and is not a new feature.

References

SQL Advanced Tutorial

MySQL Execution Plan – EXPLAIN

SQL Performance Optimization

Index Usage in SQL