sqlserver没有INTERSECT select * from (Select * From table1 Where (一堆)In(又一堆Select从句) ) t1 where exists(select 1 from (Select * From table1 Where (一堆)In(又一堆Select从句) t2 where t1.c1=t2.c1 and t1.c2=t2.c2)
其实别说用复杂的几个Select来Intersect了,用很简单的 Select name from tab1 where ID<10 Intersect Select name from tab1 where ID>5都不能执行!!!为什么,Intersect 到底怎么用????怎么取交集! 我不要用IN或Exist子句。
并运算 select c1,c2 from t1 union all select c1,c2 from t2差: c1-c2:select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)c2-c1:select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)交: select * from t1 where exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)
可是SQL里说有intersect啊,是关键字呢。
Select * From table1 Where (一堆)In(又一堆Select从句) and exists (Select 1 From table1 Where (一堆)In(又一堆Select从句) t2 and t1.c1=t2.c1)
5个列吗??Select * From table1 Where (一堆)In(又一堆Select从句) and exists (Select 1 From table1 Where (一堆)In(又一堆Select从句) t2 and t1.列1=t2.列1 and t1.列2=t2.列2 and t1.列3=t2.列3 and t1.列4=t2.列4 and t1.列5=t2.列5)
举例:create table #t1 ( i1 int , c1 char( 1 ) , vc1 varchar( 10 ) ) create table #t2 ( i2 int , c2 char( 1 ) , vc2 varchar( 10 ) ) goinsert into #t1 values( 1 , '1' , '1111' ); insert into #t1 values( 11 , 'a' , 'aaaa' ); insert into #t2 values( 1 , '1' , '1111' ); insert into #t2 values( 2 , '2' , '2222' ); go -- Orphans in #t1 only select i1 , c1 , vc1 from ( select * , checksum( * ) as chksum1 from #t1 ) as t1 where not exists ( select * from ( select * , checksum( * ) as chksum2 from #t2 ) as t2 where chksum1 = chksum2 );
不好,你说的这个我也想过了,但要是不定多个列呢?而且我本身第一个和第二个Selcet * from table1的Where子句就是程序生成的已经有一堆In啊Exist了,但是都返回相同的列,只是选中的记录数不一样!我现在就想取一下两个选中的记录的交集? 大力的方法是不是有点………?有别的法子吗?
大力:非常感谢你。能不能不改变第一个Select * From Table的Where子句?当然也不要改第二个的,因为它们的Where子句我已经用来实现比较复杂的查询,已经联了好几个主表、从表的。我现在只想得到这两个查询结果的交集。不要改变它们的Where子句。可行吗?应该不会不能吧?
select * from (Select * From table1 Where (一堆)In(又一堆Select从句)
) t1 where exists(select 1 from (Select * From table1 Where (一堆)In(又一堆Select从句) t2 where t1.c1=t2.c1 and t1.c2=t2.c2)
Select name from tab1 where ID<10
Intersect
Select name from tab1 where ID>5都不能执行!!!为什么,Intersect 到底怎么用????怎么取交集!
我不要用IN或Exist子句。
select c1,c2 from t1
union all
select c1,c2 from t2差:
c1-c2:select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)c2-c1:select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)交:
select * from t1 where exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)
exists (Select 1 From table1 Where (一堆)In(又一堆Select从句) t2 and t1.c1=t2.c1)
但是我想要这两个记录集的交集!难道不是用INTERSECT吗?上面的回答好像有点晕,差太远。
exists (Select 1 From table1 Where (一堆)In(又一堆Select从句) t2 and t1.列1=t2.列1 and t1.列2=t2.列2 and t1.列3=t2.列3 and t1.列4=t2.列4 and t1.列5=t2.列5)
i1 int , c1 char( 1 ) , vc1 varchar( 10 )
)
create table #t2 (
i2 int , c2 char( 1 ) , vc2 varchar( 10 )
)
goinsert into #t1 values( 1 , '1' , '1111' );
insert into #t1 values( 11 , 'a' , 'aaaa' );
insert into #t2 values( 1 , '1' , '1111' );
insert into #t2 values( 2 , '2' , '2222' );
go
-- Orphans in #t1 only
select i1 , c1 , vc1 from
(
select * , checksum( * ) as chksum1 from #t1
) as t1
where not exists ( select * from (
select * , checksum( * ) as chksum2 from #t2
) as t2
where chksum1 = chksum2
);
Intersect({[1994], [1995], [1996]}, {[1995], [1996], [1997]})
出现以下错误:[Microsoft][ODBC SQL Server Driver]语法错误或违反访问规则
?????????????????????????????????????
总之就是Intersect不能用,再想别的办法吧!结贴了……!