Querying Multi‑Business Unit Sales Statistics Using Row Comparison in MySQL

The author first shares a light‑hearted anecdote before diving into the technical content, which focuses on building a MySQL table to store product sales statistics and exploring various query strategies for retrieving sales data across multiple business units.

Environment Preparation : The required MySQL version is 5.7.20‑log. The table t_ware_sale_statistics is created with columns such as id , business_id , ware_inside_code , daily and period sales metrics, and audit fields. The full CREATE TABLE statement is provided in the source.

Requirement Background : Business units sell multiple products, and each product can be sold by multiple units, forming a many‑to‑many relationship. The goal is to query sales figures for specific products under specific business units, with both the unit list and product list being dynamic.

Loop Query : A straightforward approach is to iterate over each business unit in application code and execute a separate query per unit. While simple and index‑friendly, this method violates departmental rules that prohibit looping over the database.

OR Concatenation : Using MyBatis dynamic SQL, the author concatenates multiple OR conditions into a single query. This reduces database round‑trips and still uses indexes, but the resulting SQL can become excessively long when many units are involved.

Mixed Filtering : Another MyBatis dynamic SQL technique concatenates lists of business_id and ware_inside_code into IN clauses. Although index‑friendly and single‑query, the result set is larger than needed and requires an additional filtering step.

Row‑Comparison : Leveraging the SQL‑92 row‑comparison feature, the author constructs a query that matches pairs of (business_id, ware_inside_code) against a set of value tuples. This approach is also index‑friendly, executes a single query, and returns exactly the desired rows, albeit with a syntax that may be unfamiliar to many developers.

Conclusion : After evaluating the alternatives, the row‑comparison method was chosen as the optimal solution. The author emphasizes that a single requirement can have multiple implementations, and the best choice depends on business constraints, performance considerations, and code maintainability. Additionally, row‑comparison is a long‑standing SQL‑92 feature, not a new database‑specific extension.