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

表结构

订单表 order_info

id	order_no	price	quality
1	PO1001	100.0	10
2	PO1002	200.0	20
3	PO1003	100.0	10

订单扩展表 order_ext

id	order_id	key	value
101	1	seNumber	se1001
102	1	seLineNumber	seLine1001
103	2	seNumber	se1001
104	2	seLineNumber	seLine1001
105	3	seNumber	se1001
106	3	seLineNumber	seLine1002

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

现在想要根据用户自定义字段的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)