MySQL中使用INNER JOIN来实现Intersect并集操作

一、业务背景

我们有张表设计如下：

CREATE TABLE `user_defined_value` (
  `RESOURCE_ID` varchar(20) DEFAULT NULL,
  `COLUMN_NAME` varchar(20) DEFAULT NULL,
  `VALUE` varchar(255) DEFAULT NULL,
  KEY `ID_IDX` (`RESOURCE_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

COLUMN_NAME是资源的一个属性名称

VALUE是资源的属性值

二、需求

现在需要从表中查出属性A=‘1‘且属性B=‘2‘的资源ID

三、分析

这里需要查的是同时满足两个属性的资源ID，如果是或的关系，那么SQL会很简单

SELECT DISTINCT RESOURCE_ID FROM USER_DEFINED_VALUE 
WHERE (COLUMN_NAME = 'A' AND VALUE in ('1'))  
OR (COLUMN_NAME = 'B' AND VALUE in ('2'))

在百度上查到SQL Server和Oracle都支持Intersect，可以直接将两个相同结构的查询结果取交集，而MySQL并没有Intersect关键字，那么我们可以如何实现呢?

四、使用INNER JOIN xxx USING xxx

SELECT DISTINCT RESOURCE_ID FROM USER_DEFINED_VALUE 
INNER JOIN (SELECT DISTINCT RESOURCE_ID FROM USER_DEFINED_VALUE WHERE  (COLUMN_NAME = 'A' AND VALUE in ('1')) ) t0 USING (RESOURCE_ID) 
INNER JOIN (SELECT DISTINCT RESOURCE_ID FROM USER_DEFINED_VALUE WHERE  (COLUMN_NAME = 'B' AND VALUE in ('2')) ) t1 USING (RESOURCE_ID)

第二句话或查出属性A=‘1‘的资源ID，并和第一句话的查询结果取交集

第三句话或查出属性B=‘2‘的资源ID，并和前面的查询结果取交集

这样，还可以继续加其他的属性