Mysql实现交集、差集的方法和sqlserver实现交集、差集的方法对比

本篇文章主要介绍了"Mysql实现交集、差集的方法和sqlserver实现交集、差集的方法对比"，主要涉及到方面的内容，对于SqlServer感兴趣的同学可以参考一下： 第一步：先运行下这个sql文件/* Navicat MySQL Data TransferSource Server : localhost_3...

第一步：先运行下这个sql文件

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50619
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50619
File Encoding         : 65001

Date: 2015-02-01 11:29:20
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno` bigint(20) NOT NULL DEFAULT '0',
  `cname` varchar(255) DEFAULT NULL,
  `cTeacher` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '课程1', '教师1');
INSERT INTO `course` VALUES ('2', '课程2', '教师2');
INSERT INTO `course` VALUES ('3', '课程3', '教师3');
INSERT INTO `course` VALUES ('4', '课程4', '教师4');
INSERT INTO `course` VALUES ('5', '课程5', '教师5');
INSERT INTO `course` VALUES ('6', '课程6', '教师6');

-- ----------------------------
-- Table structure for `sc`
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sno` bigint(20) NOT NULL DEFAULT '0',
  `cno` bigint(20) NOT NULL DEFAULT '0',
  `scgrade` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '80');
INSERT INTO `sc` VALUES ('1', '2', '70');
INSERT INTO `sc` VALUES ('1', '3', '65');
INSERT INTO `sc` VALUES ('2', '1', '56');
INSERT INTO `sc` VALUES ('2', '3', '78');
INSERT INTO `sc` VALUES ('2', '4', '56');
INSERT INTO `sc` VALUES ('3', '1', '78');
INSERT INTO `sc` VALUES ('3', '2', '90');
INSERT INTO `sc` VALUES ('3', '5', '80');
INSERT INTO `sc` VALUES ('4', '1', '56');
INSERT INTO `sc` VALUES ('4', '3', '50');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` bigint(20) NOT NULL DEFAULT '0',
  `sname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'ces1');
INSERT INTO `student` VALUES ('2', 'ces2');
INSERT INTO `student` VALUES ('3', 'ces3');
INSERT INTO `student` VALUES ('4', 'ces4');
INSERT INTO `student` VALUES ('5', 'ces5');
INSERT INTO `student` VALUES ('6', 'ces6');

1.SQL SERVER 2005(含)以上版本提供INTERSECT(交集)

select student.sname
from sc left join student on student.sno = sc.sno
where sc.cno = 1
Intersect  
select student.sname
from sc left join student on student.sno = sc.sno
where sc.cno = 2

2.SQL SERVER 2005(含)以上版本提供except(差集)

select student.sname
from sc left join student on student.sno = sc.sno
where sc.cno = 1
except
select student.sname
from sc left join student on student.sno = sc.sno
where sc.cno = 2

3.Mysql实现交集的方法

select sno
from
(
select student.sno
from sc left join student on student.sno = sc.sno
where sc.cno =1
union all
select student.sno
from sc left join student on student.sno = sc.sno
where sc.cno =2   #并且union all的效率比使用in快
)t
group by sno
having count(1) > 1  #实现交集

4.Mysql实现差集

select *
from
(select student.sno as sno1
from sc left join student on student.sno = sc.sno
where sc.cno =1)t1 left join
(select student.sno as sno2
from sc left join student on student.sno = sc.sno
where sc.cno =2)t2 on  t1.sno1 = t2.sno2  
where t2.sno2 is null or t1.sno1 is null #实现差集

以上就介绍了Mysql实现交集、差集的方法和sqlserver实现交集、差集的方法对比，包括了方面的内容，希望对SqlServer有兴趣的朋友有所帮助。

本文网址链接：http://www.codes51.com/article/detail_108966.html