cust_id | cust_name | cust_address | cust_city | cust_state | cust_country | cust_contact | cust_email |
---|---|---|---|---|---|---|---|
1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | USA | John Smith | sales@villagetoys.com |
1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | USA | Michelle Green | NULL |
1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | USA | Jim Jones | jjones@fun4all.com |
1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | USA | Denise L. Stephens | dstephens@fun4all.com |
1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | USA | Kim Howard | NULL |
--过滤出列表中不存在于数据库中的项 --返回结果为['1000000006','1000000007'] SELECT [Id] AS [cust_id] FROM ( VALUES('1000000004'),('1000000005'),('1000000006'),('1000000007') ) dt ([Id]) EXCEPT SELECT [cust_id] FROM [Customers] --过滤出列表中存在于数据库中的项 --返回结果为['1000000004','1000000005'] SELECT [Id] AS [cust_id] FROM ( VALUES('1000000004'),('1000000005'),('1000000006'),('1000000007') ) dt ([Id]) INTERSECT SELECT [cust_id] FROM [Customers]
--对于SQLServer 2008以前的版本 SELECT [Id] AS [cust_id] FROM ( SELECT '1000000004' UNION ALL SELECT '1000000005' UNION ALL SELECT '1000000006' UNION ALL SELECT '1000000007' ) dt ([Id]) INTERSECT --EXCEPT SELECT [cust_id] FROM [Customers]
//使用C#动态生成SQL语句 var list = new Liststring>(){"1000000004","1000000005","1000000006","1000000007"}; string sqlQuery = string.Format($@" SELECT [Id] AS [cust_id] FROM ( VALUES('{string.Join("'),('", list)}') ) dt ([Id] INTERSECT --EXCEPT SELECT [cust_id] FROM [Customers]" );
更多参考
Set Operators - EXCEPT and INTERSECT
Set Operators - UNION
到此这篇关于SQL Server中的集合运算: UNION, EXCEPT和INTERSECT的文章就介绍到这了,更多相关SQL Server中的集合运算内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!