How to find the intersection of two queries in mysql

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; then use each variable key-value pair as a condition to find the corresponding process instance ID; finally Take the intersection of these two sets of process instance IDs to get the desired result.

##More related free learning recommendations: mysql tutorial(Video)

Mysql method to find the intersection of two queries:

1. Project requirements:

Need to obtain the instance id of a process instance that contains some process variables (the number is uncertain and must be included, not just some 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 we need to query the instance id of the corresponding process containing the variables formId=142 and approveType=sealApply

As shown in Figure 1, just find the value in the red line box , the final result is

Figure 2

3. Code implementation

#1. First tried to use in The query result is the same as Figure 1 (the following query statement means: find the instance id of the process containing the variable value formId=142 or approveType=sealType. As long as one of the multiple variable conditions is satisfied, the row will be Take it out), this method is not feasible

SELECT
ahv.PROC_INST_ID_,
ahv.NAME_,
ahv.TEXT_
FROM
act_hi_varinst ahv
WHERE
(ahv.NAME_, ahv.TEXT_) IN (
('formId', '142'),
('approveType', 'sealApply')
);

2. (Feasible solution) Try to use intersection (mysql itself does not have an implementation of intersection, so you can only simulate it yourself)

Query ideas: Check first For the process instance ID that satisfies a single set of variable key-value pairs, each variable key-value pair is used as a condition to find out the corresponding process instance ID (in the current example, there will be two sets of process instance IDs), and finally for the two sets of processes Take the intersection of instance ids, so you get the desired result

SELECT
PROC_INST_ID_
FROM
(
SELECT
ahv.PROC_INST_ID_
FROM
act_hi_varinst ahv
WHERE
(ahv.NAME_, ahv.TEXT_) = ('formId', '142')
UNION ALL
SELECT
ahv.PROC_INST_ID_
FROM
act_hi_varinst ahv
WHERE
(ahv.NAME_, ahv.TEXT_) = ('approveType', 'sealApply')
) t
GROUP BY
PROC_INST_ID_
HAVING
count(PROC_INST_ID_) = 2;

The above is the detailed content of How to find the intersection of two queries in mysql. For more information, please follow other related articles on the PHP Chinese website!