MySQL不支持INTERSECT和MINUS,及其替代方法
来源:互联网 发布:触屏标准软件 编辑:程序博客网 时间:2024/06/05 00:14
Doing INTERSECT and MINUS in MySQL
By Carsten | October 3, 2005
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
- MySQL不支持INTERSECT和MINUS,及其替代方法
- MySQL不支持INTERSECT和MINUS,及其替代方法
- MySQL不支持FULL JOIN, INTERSECT和MINUS(except)的替代方法
- MySQL实现Oracle的Intersect 和 Minus
- MySQL实现Oracle的Intersect 和 Minus
- MySQL实现差集(Minus)和交集(Intersect)
- MySQL实现差集(Minus)和交集(Intersect)
- MySQL实现差集(Minus)和交集(Intersect)
- MINUS、INTERSECT 和UNION的用法介绍
- INTERSECT MINUS UNION和UNION ALL
- Oracle运算:intersect和minus运算
- Oracle intersect运算和 minus运算
- SQL中union, intersect 和 minus
- MINUS,INTERSECT和UNION ALL比较
- mysql不支持Intersect交集,改版取交集
- oracle MINUS和NOT EXISTS、EXISTS和INTERSECT
- INTERSECT 与 MINUS
- UNION ,INTERSECT,MINUS
- 固定位置浮动,该死的IE6
- 内联,左外联,右外联,全连接,交叉连接 的区别
- inauguration
- 怎样在visual studio.NET 让C,C++,C#代码自动排齐
- CMD列出目录中的文件
- MySQL不支持INTERSECT和MINUS,及其替代方法
- 一份不错的STM32学习计划~
- linux设备驱动编程-初探(1)--驱动程序概念
- 简单介绍使用MD5加密的方法
- 在js文件中显示jquery的智能提示VS2010
- 美工素材网站集合.
- Hib的检索方式
- python exec和eval
- 在按钮上面添加图片最简单的办法