Querying Multi‑Business‑Unit Product Sales with Row Comparison in MySQL

The author introduces the problem of querying sales data for many business units where each unit can sell multiple products, forming a many‑to‑many relationship, and shows the need to fetch sales figures for dynamic lists of units and products.

Environment preparation includes using MySQL 5.7.20‑log and creating a table t_ware_sale_statistics with columns for IDs, sales metrics, timestamps, and a soft‑delete flag. The DDL is:

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 inserting 769,063 rows, the author describes the requirement: for example, retrieve sales for business 100001 with products 1000,1001,1003; business 100002 with products 1003,1004; etc., which forms a nested list query.

Loop query – a naïve approach where the application loops over each business ID and runs a separate query. Although simple and index‑friendly, it violates departmental coding standards that forbid repeated database calls.

OR concatenation – builds a single SQL statement with many OR conditions for each business‑product pair using MyBatis dynamic SQL. This runs one query and can use indexes, but the statement becomes very long when many units are involved.

Mixed filter – concatenates all business IDs into one IN list and all product codes into another IN list, then filters the result set in application code. While index‑friendly, the result set may contain extra rows that need additional filtering.

Row‑by‑row comparison – leverages the SQL‑92 row‑value comparison feature, allowing a predicate like (business_id, ware_inside_code) IN ((100001,1000),(100001,1001),...) . This approach uses a single query, benefits from indexes, and returns exactly the desired rows without post‑processing. It is supported by all major relational databases.

The author concludes that row‑by‑row comparison is the chosen solution because it satisfies the requirement, respects coding standards, and is efficient. Additional takeaways include the importance of evaluating multiple implementations and recognizing that row comparison is a long‑standing, standards‑based SQL feature.