This paper links: www.cnblogs.com/youzhibing/…

Environment to prepare

Database version: MySQL 5.7.20-log

Build table SQL:

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 'bigint ',' ware_inside_code 'bigint(20) NOT NULL COMMENT' bigint ', 'weight_sale_cnt_day' double(16,4) DEFAULT NULL COMMENT ' 'last_thirty_days_sales' double(16,4) DEFAULT NULL COMMENT' last 30 days ', 'last_sixty_days_sales' double(16,4) DEFAULT NULL COMMENT' last 60 days ', 'last_ninety_days_sales' double(16,4) DEFAULT NULL COMMENT' last 90 days ', 'same_period_sale_qty_thirty' double(16,4) DEFAULT NULL COMMENT '2015, 'same_period_sale_qty_sixty' double(16,4) DEFAULT NULL COMMENT '2010 年 60 月 sales ', 'ninety' double(16,4) DEFAULT NULL COMMENT 'ninety ', 'create_user' bigint(20) DEFAULT NULL COMMENT 'create ', 'create_time' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time ', '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 'Whether to delete ', 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=' SQL ';Copy the code

Initialize data

769,063 pieces of data were prepared

Demand background

The same business organization can sell different commodities, and the same commodity can be sold in different business organizations. That is to say, the relationship between business organizations and commodities is many-to-many

Suppose there are n organizations with several products in each organization. How can we find out the sales situation of the products in these stores?

Specifically, something like the following

How to find out the sales situation of commodities 1006, 1008 and 1009 under commodities 1003, 1004 and 100003 under commodities 100001 under 1000, 1001, 1003 and 100002

It is equivalent to a query of a two-tier list (a list of goods in a list of business organizations); Business organization lists and product lists are not fixed, but dynamic

So the problem is: how to query a number of business organizations, the sales of some goods (the problem by my description, may be more vague, we understand the meaning of good!

Cyclic query

This is easy to think of, loop the list of business organizations at the code level, and check the database once for each business organization, pseudo code is as follows:

The specific SQL is similar to the following

SQL can walk index

Easy to implement, easy to understand, SQL can also go to the index, everything seems perfect

However, the reality is: the department development specification constraints, can not loop through the database

Oh huo, this way can only give up, find another way

The OR joining together

Through the dynamic SQL function of MyBatis, SQL splicing is similar to the following

The specific SQL is similar to the following

SQL can also walk indexes

Simple implementation, or understand, SQL can also go to the index, and only query the database once, seems feasible

The only pity is: it is a bit of a fee OR, if the business organization is more, that SQL will be longer

As one of the candidates, let’s move on

Mixed check filter

Also using Mybatis dynamic SQL, put business_id list together, ware_inside_code together, similar to the following

The specific SQL is similar to the following

SQL can also walk indexes

The implementation is simple, easy to understand, SQL can also go to the index, and only query the database once, seems feasible

But: the result set found is greater than or equal to the result set we want, you taste, you fine taste!

Therefore, we also need to filter the result set we find, and filter out the result set we want

As a candidate, let’s move on

Okay to compare

The row-to-row comparison feature was added to SQL-92, so that the arguments to compare the =, <, >, and IN predicates are no longer just scalar values, but lists of values

Of course, you still need to use Mybatis dynamic SQL, similar to the following

The specific SQL is similar to the following

SQL can also move indexes

Simple implementation, SQL can also go index, and only query a database, feel feasible

It’s just: it’s a little hard to understand, because we don’t use it so much, so it seems strange

In addition, row comparison is an SQL specification, not a relational database specification, which means that relational databases should support this writing

conclusion

1, finally choose the line comparison this way to achieve the demand, don’t ask me why, ask is forced high!

2. There are many ways to realize a requirement. We need to take the business and various constraints into consideration and choose the most appropriate one

3, line comparison is introduced in SQL-92, SQL-92 is a specification made in 1992, line comparison is not a new feature, but a very early existence of the basic function!

Recent hot articles recommended:

1.1,000+ Java Interview Questions and Answers (2022 Latest version)

2. Awesome! Java coroutines are coming…

Spring Boot 2.x tutorial, too complete!

4.Spring Boot 2.6 is out with a lot of new features.

5. “Java Development Manual (Songshan version)” the latest release, quick download!

Feel good, don’t forget to click on + forward oh!