How to Efficiently Query Multi‑Business Sales Data 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 sample data.

Requirement Background

Each business unit can sell multiple products, and each product can be sold by multiple business units, forming a many‑to‑many relationship. The goal is to query the sales figures for a dynamic list of business units and a dynamic list of products under each unit.

Example: retrieve sales for business 100001 (products 1000, 1001, 1003), business 100002 (products 1003, 1004), and business 100003 (products 1006, 1008, 1009).

Solution Attempts

1. Loop Query

Iterate over the business list in application code and execute a separate query for each business. This approach is simple and index‑friendly but violates the department’s rule against looping database calls.

2. OR Concatenation (MyBatis Dynamic SQL)

Build a single SQL statement by concatenating multiple OR conditions for each business‑product pair. The query remains index‑friendly and hits the database only once, but the generated SQL can become very long when many businesses are involved.

3. Mixed Filter

Combine the business IDs and product codes into two comma‑separated strings and filter the result set after retrieval. Although the query uses indexes, the returned dataset is larger than needed and requires an additional filtering step.

4. Row‑by‑Row Comparison (SQL‑92)

Use the row‑comparison feature introduced in SQL‑92, allowing predicates like (business_id, ware_inside_code) IN ((?, ?), (?, ?), ...). This method is index‑friendly, executes a single query, and keeps the result set exact without extra post‑processing.

Summary

1. The final implementation uses the row‑by‑row comparison technique because it offers high readability, leverages indexes, and satisfies the no‑loop constraint.

2. Multiple solutions exist for a given requirement; the optimal choice depends on business rules, performance, and maintainability.

3. Row‑by‑row comparison is part of the SQL‑92 standard (published in 1992) and is therefore a long‑standing, widely supported feature, not a new proprietary extension.

Source: Java Charging Club public account.