Use inner join in MySQL to implement intersect and set operation
First, business background
We have a table design such as the following:
CREATE TABLE ' user_defined_value ' ( ' resource_id ' varchar) default NULL, ' column_name ' varchar (default) NULL, ' VALUE ' varchar (255) Default NULL, KEY ' id_idx ' (' resource_id ')) Engine=myisam default Charset=utf8;
RESOURCE_ID is a unique identifier for a resource
COLUMN_NAME is a property name for a resource
Value is a property of a resource
Second, demand
Now you need to isolate the attribute a= ' 1 ' from the table and the resource ID of the property b= ' 2 '
Third, analysis
The need to check is that the same time the resource ID of the two properties, assuming or the relationship, then SQL is very easy
SELECT DISTINCT resource_id from User_defined_value WHERE (column_name = ' A ' and VALUE in (' 1 ')) OR (column_name = ' B ') and VALUE in (' 2 '))
When it is a relationship, it is not easy to replace Orkeyword with and. The resources that satisfy each attribute need to be identified first. Re-fetch and set
On Baidu found that SQL Server and Oracle support intersect. Can directly intersect two of the same structure query results, and MySQL is not intersectkeyword. So what can we do?
Iv. using inner JOIN xxx using XXX
Select DISTINCT resource_id from User_defined_value INNER joins (select DISTINCT resource_id from User_defined_value where< c1/> (column_name = ' A ' and VALUE in (' 1 '))] t0 USING (resource_id) INNER JOIN (SELECT DISTINCT resource_id from User_defi Ned_value WHERE (column_name = ' B ' and VALUE in (' 2 '))) T1 USING (resource_id)
The first sentence will identify all the resource IDs in the table.
Second sentence or isolate the resource ID of the attribute a= ' 1 ' and intersect with the query result of the first sentence
The third sentence or the resource ID of the attribute b= ' 2 ' is isolated. and intersect with the previous query results
In this way, you can continue to add other properties
Copy to Google TranslateTranslation Resultsor
Use inner join in MySQL to implement intersect and set operation