Mastering Multi‑Entity Sales Queries with SQL Row Comparison

Environment Preparation

Database: MySQL 5.7.20. The table t_ware_sale_statistics is created with columns for primary key, business ID, product code, various sales metrics, audit fields, and a deletion flag. An index idx_business_ware on business_id and ware_inside_code is defined.

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

After initialization the table holds 769,063 rows.

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 goal is to query sales figures for a dynamic list of institutions together with a dynamic list of products for each institution.

Solution Attempts

Loop Query

Iterate over the institution list and execute a separate query per institution. This approach is simple but violates the team rule that forbids looping over the database.

OR Concatenation

Generate a single SQL statement with a long series of OR conditions for each (business_id, ware_inside_code) pair using MyBatis dynamic SQL. The query uses indexes and runs once, but becomes unwieldy when many institutions are involved.

Mixed Filtering

Combine all business_id values in one IN clause and all product codes in another IN clause. The result set is larger than required, so an additional filtering step is needed.

Row‑by‑Row Comparison

Use the SQL‑92 row comparison syntax (business_id, ware_inside_code) IN ((?, ?), (?, ?), ...) within a MyBatis dynamic SQL block. This single query respects indexes, returns exactly the desired rows, and was selected as the final solution.

Conclusion

The row‑by‑row comparison method was chosen for its elegance and performance.

Multiple implementation paths exist; the optimal choice depends on business constraints and maintainability.

Row comparison is part of the SQL‑92 standard and has been available since 1992.