Shulou(Shulou.com)05/31 Report--

How to use oracle to achieve one-to-many data paging query filtering? In response to this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more small partners who want to solve this problem find a simpler and easier way.

Let's see how SQL was written for the first time.

After the query to do paging outside, very normal logic, but we have found that this is a multi-table query, and is a one-to-many relationship, which is a bit of a problem

Let's look at a picture first.

Where did the problem arise?

1. You need to query the paging data of the main table, such as:

limit 1, 10 or SELECT * FROM (SELECT A.* ,ROWNUM R FROM (select _ from car) A WHERE ROWNUM = ${limitStart} ]

The above is to do data statistics on the above table, and then pagination,

2. Filter according to the incoming fields, such as the number of seats in the vehicle, displacement,

the problems

Because of the huge business data, one-to-many relationship data redundancy occurs, resulting in data offset.

The main solutions are as follows

Well, let's get a picture down

1. Merge sub-tables and convert rows to columns. 2. When paging filters are performed on the main table, it will not appear because of the redundancy of one-to-many relationship data, resulting in data offset.

SELECT * FROM (SELECT A.* ,ROWNUM R FROM ( select T_CAR. "ID" as car_ID , T_CAR. "CAR_NAME" as car_CAR_NAME , T_CAR. "VIN_NUMBER" as car_VIN_NUMBER ,car_label.label_ids FROM T_CAR left join (select CAR_ID,wm_concat(LABLE_ID) as label_ids from T_Car_label group by CAR_ID) car_label on car_label.CAR_ID = T_CAR.ID where FIND_IN_SET('4aa06d2b9e904fe8bfeba3505c5dad6a',label_ids)=1 ) A WHERE ROWNUM =

FIND_IN_SET: Because the filter written in sql is very cumbersome, this method is a storage function. This implementation is not very good.

This function is defined under mysql, but there are some internal changes here because it is business-related

The specific modification is that conditional filtering can also be made when data in the format {1, 2, 3, 4} is transferred

create or replace FUNCTION FIND_IN_SET (div_str1 varchar2, div_str2 varchar2, p_sep varchar2 :=',')RETURN NUMBER IS l_idx_a number:=0; --position l_idx_b number:=0 for calculating delimiters in div_str1; --position str_a varchar2(4000) for calculating delimiters in div_str2; --substring str_b varchar2(4000) truncated from delimiters;--Substring piv_str_a varchar2(4000) := piv_str1 according to delimiter truncation; --Assign piv_str1 to piv_str_a piv_str_b varchar2(4000) := piv_str2; --Assign piv_str2 to piv_str_b res number:=0;--Return Result BEGIN--If piv_str_a has no delimiter, directly loop to determine whether piv_str_a and piv_str_b are equal, equal res=1IF instr(piv_str_a, p_sep, 1) = 0 THEN --If piv_str2 has no delimiter, directly determine whether piv_str1 and piv_str2 are equal, equal res=1 IF instr(piv_str_b, p_sep, 1) = 0 THEN IF piv_str_a = piv_str_b THEN res:= 1; END IF; ELSE --Loop intercepts piv_str_b by delimiter LOOP l_idx_b := instr(piv_str_b,p_sep); --when there are delimiters in piv_str IF l_idx_b > 0 THEN --Intercept the field str before the first delimiter str_a:= substr(piv_str_b,1,l_idx_b-1); --Determine whether str and piv_str_a are equal, equal res=1 and end the loop IF str_a = piv_str_a THEN res:= 1; EXIT; END IF; piv_str_b := substr(piv_str_b,l_idx_b+length(p_sep)); ELSE --When there is no separator in the truncated piv_str, judge whether piv_str and piv_str1 are equal, equal res=1 IF piv_str_a = piv_str_b THEN res:= 1; END IF; --Whether or not the final equality, jump out of the loop EXIT; END IF; END LOOP; --End cycle END IF;ELSE--Loop intercepts piv_str_aLOOP l_idx_a by delimiter:= instr(piv_str_a,p_sep);--If there are delimiters in piv_str_a IF l_idx_a > 0 THEN --intercepts the field str before the first delimiter str_a:= substr(piv_str_a,1,l_idx_a-1); --If piv_str_b does not have a separator, directly determine whether piv_str1 and piv_str are equal, equal res=1 IF instr(piv_str_b, p_sep, 1) = 0 THEN --Determine if str_a and piv_str_b are equal, equals res=1 and ends the loop IF str_a = piv_str_b THEN res:= 1; EXIT; END IF; ELSE --Loop intercepts piv_str_b by delimiter LOOP l_idx_b := instr(piv_str_b,p_sep); --when there are delimiters in piv_str IF l_idx_b > 0 THEN --Intercept the field str before the first delimiter str_b:= substr(piv_str_b,1,l_idx_b-1); --Judge whether str and piv_str1 are equal, equal res=1 and end the loop judgment IF str_b = str_a THEN res:= 1; EXIT; END IF; piv_str_b := substr(piv_str_b,l_idx_b+length(p_sep)); ELSE --When there is no separator in the truncated piv_str, judge whether piv_str and piv_str1 are equal, equal res=1 IF piv_str_a = piv_str_b THEN res:= 1; END IF; --Whether or not the final equality, jump out of the loop EXIT; END IF; END LOOP; --End cycle END IF; piv_str_a := substr(piv_str_a,l_idx_a+length(p_sep)); ELSE --When there is no separator in the truncated piv_str, judge whether piv_str and piv_str1 are equal, equal res=1 IF piv_str_a = piv_str_b THEN res:= 1; END IF;--Regardless of whether the final is equal, all jump out of the loop EXIT; END IF;END LOOP;--End loop END IF;--Return to resRETURN res;END FIND_IN_SET; How to use oracle to achieve one-to-many data paging query filtering questions The answer is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts, you can pay attention to the industry information channel for more relevant knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.