Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql how to find the intersection of two queries, the article is very detailed, has a certain reference value, interested friends must read it!

Mysql method to find the intersection of two queries: first, check the process instance id; that satisfies a single set of variable key-value pairs, and then take each variable key-value pair as a condition to find out the corresponding process instance id;. Finally, take the intersection of the two groups of process instances id, so that the desired result is obtained.

Mysql's method of finding the intersection of two queries:

1. Project requirements:

You need to get an instance id of a process instance that contains some process variables (the number is uncertain and must contain all, not only a few of them)

2. Detailed description:

1. Process variable table act_hi_varinst

Fields: PROC_INST_ID_ (process instance id), NAME_ (variable name), TEXT_ (variable value)

Figure 1

2. Now you need to query the instance id of the corresponding process containing variables formId=142 and approveType=sealApply

As shown in figure 1, as long as the value listed in the red line is found, the final result is

Figure 2

Third, code implementation

1. First of all, we try to use the in method, and the query result is similar to figure 1 (the following query sentence means: find out the instance id of the process that contains the variable value of formId=142 or approveType=sealType, and the line will be taken out as long as one of the variable conditions is satisfied). This method is not feasible.

SELECTahv.PROC_INST_ID_,ahv.NAME_,ahv.TEXT_FROMact_hi_varinst ahvWHERE (ahv.NAME_,ahv.TEXT_) IN (('formId',' 142'), ('approveType',' sealApply'))

2. (feasible scheme) try to use intersection (mysql itself does not have the implementation of intersection, so it can only be simulated by itself)

Query idea: first check the process instance id that satisfies a single set of variable key-value pairs, and take each variable key-value pair as a condition to find the corresponding process instance id (in the current example, there will be two groups of process instance id), and finally take the intersection of the two groups of process instances id, so that the desired result is obtained.

SELECTPROC_INST_ID_FROM (SELECTahv.PROC_INST_ID_FROMact_hi_varinst ahvWHERE (ahv.NAME_, ahv.TEXT_) = ('formId',' 142') UNION ALLSELECTahv.PROC_INST_ID_FROMact_hi_varinst ahvWHERE (ahv.NAME_, ahv.TEXT_) = ('approveType',' sealApply')) tGROUP BYPROC_INST_ID_HAVINGcount (PROC_INST_ID_) = 2; that's all about how mysql finds the intersection of the two queries. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.