Using Row‑Row Comparison to Query Sales Statistics for Multiple Business Units and Products 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 'Primary key ID',
  `business_id` bigint(20) NOT NULL COMMENT 'Business unit code',
  `ware_inside_code` bigint(20) NOT NULL COMMENT 'Product internal code',
  `weight_sale_cnt_day` double(16,4) DEFAULT NULL COMMENT 'Average daily sales',
  `last_thirty_days_sales` double(16,4) DEFAULT NULL COMMENT 'Sales in the last 30 days',
  `last_sixty_days_sales` double(16,4) DEFAULT NULL COMMENT 'Sales in the last 60 days',
  `last_ninety_days_sales` double(16,4) DEFAULT NULL COMMENT 'Sales in the last 90 days',
  `same_period_sale_qty_thirty` double(16,4) DEFAULT NULL COMMENT 'Same period sales 30 days ago',
  `same_period_sale_qty_sixty` double(16,4) DEFAULT NULL COMMENT 'Same period sales 60 days ago',
  `same_period_sale_qty_ninety` double(16,4) DEFAULT NULL COMMENT 'Same period sales 90 days ago',
  `create_user` bigint(20) DEFAULT NULL COMMENT 'Creator',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `modify_user` bigint(20) DEFAULT NULL COMMENT 'Last modifier',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last modification time',
  `is_delete` tinyint(2) DEFAULT '2' COMMENT 'Deletion flag, 1: yes, 2: no',
  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='Product sales statistics';

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. Given a set of business units and, for each unit, a set of product codes, the goal is to retrieve the sales statistics for those specific products under those specific units.

The problem can be visualized as a two‑level list: a list of business units, each containing a dynamic list of products.

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

Because both the business‑unit list and the product list are dynamic, a flexible query method is required.

Explored Query Strategies

1. Loop Query

Iterate over the business‑unit list in application code and execute a separate query for each unit. This approach is simple and index‑friendly, but the development guidelines forbid looping over the database.

2. OR Concatenation

Use MyBatis dynamic SQL to concatenate multiple OR conditions, e.g., (business_id = 100001 AND ware_inside_code IN (1000,1001,1003)) OR (business_id = 100002 AND ware_inside_code IN (1003,1004)) … . The query runs once, uses indexes, but the SQL becomes very long when many units are involved.

3. Mixed Filtering

Also using MyBatis dynamic SQL, concatenate all business_id values into one list and all ware_inside_code values into another list, then filter the result set in application code. The query is index‑friendly and runs once, but the returned result set is larger than needed, requiring an extra filtering step.

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

Leverage the row‑row comparison syntax introduced in SQL‑92, allowing predicates such as (business_id, ware_inside_code) IN ((100001,1000),(100001,1001),(100001,1003),…) . This method is index‑friendly, executes a single query, and returns exactly the desired rows without additional filtering.

Although the syntax is less common and may look unfamiliar, it is part of the standard SQL‑92 specification and is supported by major relational databases.

Conclusion

1. The final implementation uses the row‑row comparison technique because it satisfies the requirement of a single, index‑friendly query while returning precisely the needed data.

2. Different solutions exist for a given requirement; the best choice depends on business constraints, performance considerations, and maintainability.

3. Row‑row comparison has been part of the SQL‑92 standard since 1992; it is not a new feature but a long‑standing fundamental capability.

Source: Java充电社

Final Note (Self‑Promotion)

If you found this article helpful, please like, view, share, and bookmark it. Your support motivates me to keep creating content.

Additionally, I have a Knowledge Planet community where I share advanced projects and tutorials (e.g., large‑scale data sharding, DDD micro‑services, RocketMQ, etc.). Membership costs 199 CNY, with incremental pricing for each added series.

To join, add my WeChat: special_coder