List之Union(),Intersect(),Except() 即并集，交集，差集运算。

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
  
namespace Sample2  
{  
    class Program  
    {  
        static void Main(string[] args)  
        {  
            //List之Union(),Intersect(),Except() 即并集，交集，差集运算  
            IList<Student> Students1 = new List<Student>();  
            Students1.Add(new Student(1, false, "张三", "深圳"));  
            Students1.Add(new Student(2, false, "李四", "广州"));  
            Students1.Add(new Student(3, false, "王五", "珠海"));  
            Students1.Add(new Student(4, false, "赵六", "东莞"));  
  
            IList<Student> Students2 = new List<Student>();  
            Students2.Add(new Student(1, false, "张三", "深圳"));  
            Students2.Add(new Student(5, false, "李七", "广州"));  
            Students2.Add(new Student(6, false, "孙八", "深圳"));  
            Students2.Add(new Student(7, true, "赵燕", "深圳"));  
  
            //自定义比较规则  
            var bingji = Students1.Union(Students2, new StudentListEquality()).ToList();//并集（AB集合所有项）  
            var jiaoji = Students1.Intersect(Students2, new StudentListEquality()).ToList();//交集 (AB集合共同项)  
            var chaji = Students1.Except(Students2, new StudentListEquality()).ToList();//差集（A集合有,B没有）  
  
            Console.WriteLine("以下是并集的结果");  
            bingji.ForEach(x =>  
            {  
                Console.WriteLine(x.Id.ToString() + "    " + x.Sex.ToString() + "   " + x.Name.ToString() + " " + x.Address.ToString());  
  
            });  
  
            Console.WriteLine("以下是交集的结果");  
            jiaoji.ForEach(x =>  
            {  
                Console.WriteLine(x.Id.ToString() + "    " + x.Sex.ToString() + "   " + x.Name.ToString() + " " + x.Address.ToString());  
  
            });  
  
            Console.WriteLine("以下是差集的结果");  
            chaji.ForEach(x =>  
            {  
                Console.WriteLine(x.Id.ToString() + "    " + x.Sex.ToString() + "   " + x.Name.ToString() + " " + x.Address.ToString());  
  
            });  
            Console.ReadKey();  
        }  
    }  
  
    public class Student  
    {  
        public Student(int id, bool sex, String name, String address)  
        {  
            this.Id = id;  
            this.Sex = sex;  
            this.Name = name;  
            this.Address = address;  
        }  
        public int Id { get; set; }  
        public bool Sex { get; set; }  
        public String Name { get; set; }  
        public String Address { get; set; }  
  
    }  
  
    public class StudentListEquality : IEqualityComparer<Student>  
    {  
        public bool Equals(Student x, Student y)  
        {  
            return x.Id == y.Id && x.Name == y.Name && x.Address == y.Address && x.Sex == y.Sex;  
        }  
        public int GetHashCode(Student obj)  
        {  
            return (obj == null) ? 0 : obj.ToString().GetHashCode();  
        }  
    }  
}  

SQLServer中通过intersect,union,except和三个关键字对应交、并、差三种集合运算。

他们的对应关系可以参考下面图示

测试示例：

构造A,B两个数据集

[sql]  view plain  copy

1. A:1,2,3,4  
2. B:1,2,5  
3. WITH A AS  
4. (SELECT '1' tno  
5. UNION ALL SELECT  '2' UNION ALL SELECT  '3' UNION ALL SELECT  '4'   
6. ),  
7. B AS(SELECT '1' tno  
8. UNION ALL SELECT  '2' UNION ALL SELECT  '5')  

查询示例：

1 Union 取合集并过滤重复数据

[sql]  view plain  copy

1. --1 Union 取合集并过滤重复数据  
2. --结果显示: 1,2,3,4,5  
3. SELECT * FROM A  
4. UNION     
5. SELECT * FROM B;  

2 Union all 取合集不过滤重复数据

[sql]  view plain  copy

1. --2 Union all 取合集不过滤重复数据  
2. --结果显示:1,2,3,4,1,2,5  
3. SELECT * FROM A  
4. UNION  all  
5. SELECT * FROM B;  

3 Intersect 取交集(两个表中都有数据)

[sql]  view plain  copy

1. --3 Intersect 取交集  
2. --结果显示:1,2  
3. SELECT * FROM A  
4. Intersect    
5. SELECT * FROM B;  

4 except 取差集(取A-B的记录)

[sql]  view plain  copy

1. --4 except 取差集  
2. --结果显示:3,4  
3. SELECT * FROM A  
4. except    
5. SELECT * FROM B;