MySQL不支持INTERSECT和MINUS及其替代方法
这篇文章主要介绍了MySQL不支持INTERSECT和MINUS情况下的替代方法,需要的朋友可以参考下
Doing INTERSECT and MINUS in MySQL
Doing an INTERSECT
An INTERSECT is simply an inner join where we compare the tuples of one table with those of the other, and select those that appear in both while weeding out duplicates. So
SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b
can simply be rewritten to
SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)
Performing a MINUS
To transform the statement
SELECT member_id, name FROM a
MINUS
SELECT member_id, name FROM b
into something that MySQL can process, we can utilize subqueries (available from MySQL 4.1 onward). The easy-to-understand transformation is:
SELECT DISTINCT member_id, name
FROM a
WHERE (member_id, name) NOT IN
(SELECT member_id, name FROM table2);
Of course, to any long-time MySQL user, this is immediately obvious as the classical use-left-join-to-find-what-isn't-in-the-other-table:
SELECT DISTINCT a.member_id, a.name
FROM a LEFT JOIN b USING (member_id, name)
WHERE b.member_id IS NULL
Doing an INTERSECT
An INTERSECT is simply an inner join where we compare the tuples of one table with those of the other, and select those that appear in both while weeding out duplicates. So
代码如下:
SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b
can simply be rewritten to
代码如下:
SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)
Performing a MINUS
To transform the statement
代码如下:
SELECT member_id, name FROM a
MINUS
SELECT member_id, name FROM b
into something that MySQL can process, we can utilize subqueries (available from MySQL 4.1 onward). The easy-to-understand transformation is:
代码如下:
SELECT DISTINCT member_id, name
FROM a
WHERE (member_id, name) NOT IN
(SELECT member_id, name FROM table2);
Of course, to any long-time MySQL user, this is immediately obvious as the classical use-left-join-to-find-what-isn't-in-the-other-table:
代码如下:
SELECT DISTINCT a.member_id, a.name
FROM a LEFT JOIN b USING (member_id, name)
WHERE b.member_id IS NULL
收藏文章
热评话题
- Android定时器实现的几种方式整理及removeCallbacks失效问题解决_Android开发_龙盟编程网
- 在linux中导入sql文件的方法分享(使用命令行转移mysql数据库)_MySQL 技术_龙盟编程网
- 如何应用PHP函数imagettftext处理图片_php编程_个人技术分享
- 在ASP.Net中应用Javascript_Javascript编程_龙盟编程网
- 查询反向链接中link和domain的区别_SEO优化_龙盟编程网
- sql 查询本年、本月、本日记录的语句,附SQL日期函数_Sql Server开发_龙盟编程网
- 如何解决C语言,函数名与宏冲突_C/C++开发_龙盟编程网
精彩图集
精彩文章
