正好需要查表,涉及到跨库、多表的查询,就干脆更新了,花了两天的时间找资料试错,总算查到我想要的效果了,不容易呀,因为不懂专业术语,使用白话来百度也是找了好久才找到合适的语句
跨库查询
select 列 from [目标服务器].[库].[dbo].[表]
交集运算 - 返回相同结果-intersect
select *from 表 intersect select * from 表2
并集运算 - 返回不重复的结果-union
select *from 表 union select * from 表2
示例:筛选出表b\c\d\e的四个表不重复的数据,然后筛选出表a的数据与表b\c\d\e的结果集相同的数据
select a.CUST_CODE from [10.x.x.x].[kbss].[dbo].[CUST_CODE] a intersect (select b.fundid from custcommissiom a union select c.fundid from custcommissiom_new c union select d.custid from specialcommission d union select e.fundid from customer e);
三表以上联查 - join(不排重)
第三个表开始需要使用cross join,否则会报错
select a.fundid,b.fundid,c.custid from custcommission a jion custcommission_new b on a.fundid = b,fundid cross join specialcummissiom c
三表以上联查 - union all(不排重)
select a.fundid from custcommissiom a union all select b.fundid from custcommissiom_new union all select c.cusatid from specialcommission c union all select d.fundid from customer d
PS:union all通过以行形式合并获取数据,join是通过按列的拼接方式获取数据,jion的处理会相对慢一些,数据大的话会报 out of memory(如果遇到这种情况,可以在select 后加 "top 100" 来限制查询的数量)
过滤重复 - distinct(排重)
select distinct a.fundid,b.fundid from custcommissiom a join custcommissiom_new on a.fundid = b.fundid