Querying Multi‑Business Unit Product Sales Statistics with MySQL and MyBatis Dynamic SQL

The author, a senior architect, presents a practical problem: a many‑to‑many relationship between business units and products requires querying average daily sales, recent 30/60/90‑day sales, and year‑over‑year figures stored in a MySQL table t_ware_sale_statistics . The article walks through the environment setup, SQL schema, and several query strategies.

Environment Preparation

Database version: MySQL 5.7.20‑log.

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

Requirement Background

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

Loop Query (Naïve Approach)

Iterate over the business‑unit list in application code and execute a separate SQL statement for each unit. The pseudo‑code is shown in an image. This method is simple and index‑friendly but violates the department rule that forbids looping over the database.

OR Concatenation

Use MyBatis dynamic SQL to build a single statement with a long series of OR conditions, one for each business_id ‑ ware_inside_code pair. The resulting query still uses indexes, but the SQL length grows quickly with the number of units.

Mixed Filter

Combine the two lists separately: one IN clause for all business_id values and another IN clause for all ware_inside_code values, then filter the result set in application code. The query remains index‑friendly, yet the returned rows are larger than needed, requiring an extra filtering step.

Row‑Comparison (SQL‑92 Feature)

Leverage the row‑comparison syntax introduced in SQL‑92, allowing a predicate such as (business_id, ware_inside_code) IN ((?, ?), (?, ?), ...) . MyBatis dynamic SQL can generate this construct. The query uses indexes, returns exactly the desired rows, and executes only once.

Conclusion

The final chosen solution is the row‑comparison method because it satisfies the no‑loop restriction, keeps the query index‑friendly, and returns the precise result set without post‑processing. The article also reminds readers that many implementation options exist and the best choice depends on business constraints and performance considerations.