Using SQL‑92 Row‑by‑Row Comparison in MySQL to Query Multi‑Agency Product Sales

Environment preparation : The target database is MySQL 5.7.20‑log. The article provides the DDL for the t_ware_sale_statistics table, which stores sales metrics such as daily average sales, last 30/60/90 days sales, and reference fields for business ID and product code.

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 : About 769,063 rows of sample data are inserted for testing.

Requirement background : Business units sell multiple products and a product can be sold by multiple units, forming a many‑to‑many relationship. The goal is to retrieve sales figures for a dynamic set of units and their associated product codes in a single query.

Loop query : The naïve solution iterates over each business ID in application code and executes a separate SQL statement per unit. Although simple and index‑friendly, the organization’s development policy forbids repeated database round‑trips.

OR concatenation : Using MyBatis dynamic SQL, the author builds a single statement with a long series of OR conditions covering all business‑product pairs. This approach also uses indexes and hits the database once, but the generated SQL becomes unwieldy when many units are involved.

Mixed filtering : Another MyBatis dynamic SQL variant concatenates the lists of business_id and ware_inside_code into a combined filter, then applies a post‑query filter to discard excess rows. While still index‑friendly, the result set can be much larger than needed, requiring extra filtering.

Row‑by‑Row comparison (SQL‑92) : The final solution leverages the row‑value comparison feature introduced in SQL‑92, allowing predicates like (business_id, ware_inside_code) IN ((?, ?), (?, ?), ...) . This method remains index‑friendly, executes a single query, and returns exactly the desired rows without the overhead of the previous approaches. Although less commonly used, it is standard across relational databases.

Summary :

Row‑by‑row comparison was chosen as the optimal technique for the requirement.

Multiple implementation options exist; the best choice balances business constraints and technical trade‑offs.

Row‑by‑row comparison is a mature feature from the 1992 SQL‑92 standard, not a new invention.