在T-SQL实现Oracle的MINUS集合运算符

CREATE Procedure sp_MINUS (@query1 varchar(2000),
@query2 varchar(2000))
As
Begin

Declare @buildStmt1 varchar(2000)
Declare @buildStmt2 varchar(2000)
Declare @pos1 int
Declare @pos2 int 

Set nocount on 
Set @pos1 = charindex ('FROM',upper (@query1))
Set @pos2 = charindex ('FROM',upper (@query2))

-- 在语句中输入into 子句

Set @buildStmt1 = SUBSTRING (@query1,1,@pos1-1) + 
' as f into ##t1 ' + 
SUBSTRING (@query1,@pos1,len(@query1) - @pos1 + 1)

Set @buildStmt2 = SUBSTRING (@query2,1,@pos2-1) + 
' as f into ##t2 ' + 
SUBSTRING (@query2,@pos2,len(@query2) - @pos2 + 1)

-- 构建中间全局临时表

EXEC (@buildStmt1)
EXEC (@buildStmt2)


-- 执行MINUS操作

Select f from ##t1 
Where f NOT in (select f from ##t2)

-- 删除表

Drop table ##t1
Drop table ##t2
Set nocount off 
End 

Go