用SQL取差集--5月11日的一次任务

VAC用WINDOWS。 CR+LF

任务材料 ：

数据1：20W的数据 all_num.txt

格式：13020451023,32523,9135044120,20111101133450,0,0,20120501010101

数据2：2W的数据 filter_num.txt

格式：13020451023

任务目标：

要将20W条数据中剔除掉2W个数据。

步骤1：建表（10‘）

A_TEMP_123 存20W， A存 2W

CREATE TABLE A_TEMP_123
(
A1 VARCHAR2(30),
A2 VARCHAR2(30),
A3 VARCHAR2(30),
A4 VARCHAR2(30),
A5 VARCHAR2(30),
A6 VARCHAR2(30),
A7 VARCHAR2(30)
) ;

CREATE TABLE A

(

i VARCHAR2(30)

）;

步骤2：建索引 （5’）

（A_TEMP_123.A1 与A.I存号码）

CREATE INDEX IDX_A1 ON A_TEMP_123 (A1);

CREATE INDEX IDX_A2 ON A (I);

步骤3：文本格式整理(5‘)

dos2unix *.txt

步骤3：sqlldr 导入(10‘)

sqlldr 账号/密码 control=s1.ctl data=all_num.txt log=s1.log errors=10000

s1 内容：

load data
replace into table A_TEMP_123
fields terminated by ','(a1,a2,a3,a4,a5,a6,a7)

sqlldr 账号/密码 control=s2.ctl data=filter_num.txt log=s2.log errors=10000

s2 内容：

load data
replace into table A
fields terminated by ','(i)

步骤4：SQL查询（10’）

2B方法：（半天出不了结果）

select count(1) from A_TEMP_123 t1 where t1.a1 not in (select t2.i from a t2 );

有四种：

1.用 in 关联子查询方法 ：（比较慢）

SQL> select count(1) from A_TEMP_123 t1 where t1.a1 not in (select t2.i from a t2 where t2.i=t1.a1);

COUNT(1)
----------
203192

如何计算此查询：SQL Server 通过将每一行的值代入内部查询，考虑 A_TEMP_123 T1 表中的每一行是否都包括在结果中。
例如，
1:如果 SQL Server 首先检查 13020451023,32523,9135044120,20111101133450,0,0,20120501010101  行，那么变量 T1.A1 将取值 13020451023，
2:SQL Server 将该值代入内部查询。select t2.i from a t2 where t2.i=13020451023，结果为13020451023，
3:因此外部查询计算为：select count(1) from A_TEMP_123 t1 where 13020451023 not in （13020451023），结果为假，因此不列入生成的新表。

由此可见，加上与不加 where t2.i=t1.a1 的2B算法 ，差别就在于where 13020451023 not in （x1,x2,x3...........xn）的Xn的个数差别，加上条件（关联查询）只有一两个，不加的话在in中有2W个，运算量差了4个数量级啊！

2.用 exists 关联子查询方法 ：
SQL> select count(1) from A_TEMP_123 t1 where not exists (select 1 from a t2 where t2.i=t1.a1);

COUNT(1)
----------
203192

同理，用exists就更快一点，因为在select 1 from a t2 where t2.i=13020451023时，返回1，在外部查询时：select count(1) from A_TEMP_123 t1 where not exists （1），由于使用not exists ，所以存在1 就不被插入新表。

3.用 MINUS方法：

SQL> select count(1) from a_temp_123 t1 where t1.A1 in (select A1 from a_temp_123 minus select i from a);

COUNT(1)
----------
203192

4.用left join方法：

SQL> select count(1) from A_TEMP_123 t1 left join a t2 on t1.a1=t2.i where t2.i is null;

COUNT(1)
----------
203192

步骤5：脚本导出数据(5‘)

#!/bin/sh

sqlplus-s xx/xx<<EOF >/dev/null

set trimspoolon headingoffnewpagenone feedofftermoff

spool after_filter.txt

select a1||','||a2||','||a3||','||a4||','||a5||','||a6||','||a7froma_temp_123 T1

where T1.A1in(select A1froma_temp_123minusselect ifroma);

spooloff

exit;

EOF

步骤6：压缩

zip after_filter.zipafter_filter.txt

步骤7：下载到本机

解压，转换成windows格式！

这次数据提取，让我懂得2B的SQL语句的执行效率差别有多大，愣是执行了1小时没出数据，今后要多加强SQL的学习啊。

 

相关子查询

许多查询都可以通过执行一次子查询并将得到的值代入外部查询的 WHERE 子句中进行计算。在包括相关子查询（也称为重复子查询）的查询中，子查询依靠外部查询获得值。这意味着子查询是重复执行的，为外部查询可能选择的每一行均执行一次。

此查询在 SalesPerson 表中检索奖金为 5000 且雇员标识号与 Employee 和 SalesPerson 表中的标识号相匹配的雇员的名和姓的一个实例。

USE AdventureWorks2008R2;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID 
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID 
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

下面是结果集：

LastName FirstName BusinessEntityID

-------------------------- ---------- ------------

Ansman-Wolfe Pamela 280

Saraiva José 282

(2 row(s) affected)

该语句中前面的子查询无法独立于外部查询进行计算。它需要 Employee.BusinessEntityID 值，但是此值随 SQL Server 检查Employee 中的不同行而改变。

下面准确说明了如何计算此查询：SQL Server 通过将每一行的值代入内部查询，考虑Employee 表中的每一行是否都包括在结果中。例如，如果 SQL Server 首先检查 Syed Abbas 行，那么变量Employee.BusinessEntityID 将取值 285，SQL Server 将该值代入内部查询。

USE AdventureWorks2008R2;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;

结果为 0（Syed Abbas 没有收到奖金，因为他不是销售人员），因此外部查询计算为：

USE AdventureWorks2008R2;
GO
SELECT LastName, FirstName
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID 
WHERE 5000 IN (0.00)

由于这是假的，因此 Syed Abbas 行不包括在结果中。对 Pamela Ansman-Wolfe 行运行相同的过程，您会发现此行没有包括在结果中。

通过在外部查询中引用表中的列作为表值函数的参数，相关子查询也可以在 FROM 子句中包含表值函数。在这种情况下，对于外部查询的每一行，将根据子查询计算表值函数。

------------------------------------------------------------------------------------------------------------