【Mysql】取两个查询语句结果的交集

表结构

订单表 order_info

订单扩展表 order_ext

订单表是主表，订单扩展表是存储订单的用户自定义字段的记录表

现在想要根据用户自定义字段的key seNumber和seLineNumber以及主表中quality字段查询订单的信息。

查询seNumber=se1001 且 seLineNumber=seLine1001 且 quality=10的订单信息

通过JOIN操作取交集

SELECT
	orderInfo.* 
FROM
	order_info orderInfo 
WHERE
	orderInfo.ID IN (
	SELECT
		table1.* 
	FROM
		order_ext table1 
	WHERE
		table1.`key` = seNumber 
		AND table1.`value` = se1001
	JOIN SELECT
		table2.* 
	FROM
		order_ext table2 
	WHERE
		table2.`key` = seLineNumber 
		AND table2.`value` = seLine1001 ON table1.order_id = table2.otder_id 
	) 
	AND orderInfo.quality = 10

通过UNION ALL来实现

SELECT
	orderInfo.* 
FROM
	order_info orderInfo 
WHERE
	orderInfo.ID IN (
	SELECT
		a.* 
	FROM
		(
		SELECT
			table1.order_id 
		FROM
			order_ext table1 
		WHERE
			table1.`key` = seNumber 
			AND table1.`value` = se1001 UNION ALL
		SELECT
			table2.order_id 
		FROM
			order_ext table2 
		WHERE
			table2.`key` = seLineNumber 
			AND table2.`value` = seLine1001 
		) a 
	GROUP BY
		a.order_id 
HAVING
	COUNT(*)= 2)