导出筛选数据

[ysycysyc]

各位大虾，在道素大虾的帮助下我解决了一个难题，可是因为是新手，对给出的语句不能区分对应的是哪一个条件，即我不知哪些语句是解决第一个条件的，我想把第一个条件筛选出的数据导入一个新表TMC中，还请各位大虾再给予指点，下面是原来问题的地址http://bbs.csdn.net/topics/392172213

[二月十六]

第一个条件是什么？

[ysycysyc]

数据库TMA、TMB都有6个INT字段：A、B、C、D、E、F,其中TMA记录很多，而TMB只有1条记录，想从TMA中提取数据，条件是： 1、TMA中任意两个字段的差与TMB中同位字段的差相等，并且这种相等的差只能有一组，比如，TMB中A-B=10,那么从TMA中提取的数据必须是A-B也等于10，并且其他两个字段的差都不相等。 2、TMA中的数据包含TMB中的1-2个。 道素大虾给的语句是 with b(t,n)AS( select bc.t,bc.n from #tmb as b cross apply(values('A',A),('B',B),('C',C),('D',D),('E',E),('F',F)) bc(t,n) ),b_com(f,v)AS( select b1.t+'-'+b2.t, b1.n-b2.n from b as b1,b as b2 where b1.t>b2.t ) ,a(ID,t,n)AS( select a.ID,ac.t,ac.n from #tma as a cross apply(values('A',A),('B',B),('C',C),('D',D),('E',E),('F',F)) ac(t,n) ),a_com(ID,f,v)AS( select a1.ID,a1.t+'-'+a2.t, a1.n-a2.n from a as a1 inner join a as a2 on a1.ID=a2.ID where a1.t>a2.t ) ,s1 AS ( select distinct a.id from a inner join b on a.n=b.n ),s2 as( select ID from a_com inner join b_com on a_com.f=b_com.f and a_com.v=b_com.v group by ID having count(0)=1 ) select * from #tma as t where exists(select 0 from s1 where s1.ID=t.ID) and exists(select 0 from s2 where s2.ID=t.ID)

[ysycysyc]

就是上面的第一个条件

[ysycysyc]

if object_id('tempdb..#tmb') is not null drop table #tmb if object_id('tempdb..#tma') is not null drop table #tma create table #tma(ID int,A int,B int,C int,D int,E int,F int) create table #tmb(A int,B int,C int,D int,E int,F int) insert into #tmb select 1110,1120,1130,1200,1240,1310 insert into #tma select 1,1130,1140,1180,1190,1240,1280 union select 2,1130,1310,1380,1390,1410,1540 union select 3,1130,1310,1380,1390,1410,1500 -------------------------------------------------------------------------------- 这是道素大虾实验数据

[ysycysyc]

谢谢大虾

[道素]

我进一步对我给你的语句分拆解释下：

------- 测试数据
if object_id('tempdb..#tmb') is not null drop table #tmb
if object_id('tempdb..#tma') is not null drop table #tma
create table #tma(ID int,A int,B int,C int,D int,E int,F int)
create table #tmb(A int,B int,C int,D int,E int,F int)
insert into #tmb
select 1110,1120,1130,1200,1240,1310
 
insert into #tma
select 1,1130,1140,1180,1190,1240,1280 union
select 2,1130,1310,1380,1390,1410,1540 union
select 3,1130,1310,1380,1390,1410,1500

----------------------计算B表总所有组合的差值;with b(t,n)AS( select bc.t,bc.n from #tmb as b cross apply(values('A',A),('B',B),('C',C),('D',D),('E',E),('F',F)) bc(t,n) ),b_com(f,v)AS( select b1.t+'-'+b2.t, b1.n-b2.n from b as b1,b as b2 where b1.t>b2.t ) SELECT * FROM b_com [/code]

f    v
---- -----------
B-A  10
C-A  20
C-B  10
D-A  90
D-B  80
D-C  70
E-A  130
E-B  120
E-C  110
E-D  40
F-A  200
F-B  190
F-C  180
F-D  110
F-E  70

------------------- 计算A表中每行数据两两组合的差值，这里的数据就会很多了

,a(ID,t,n)AS(
  select a.ID,ac.t,ac.n from #tma as a
  cross apply(values('A',A),('B',B),('C',C),('D',D),('E',E),('F',F)) ac(t,n)
 
),a_com(ID,f,v)AS(
   select a1.ID,a1.t+'-'+a2.t, a1.n-a2.n from a as a1 inner join a as a2 on a1.ID=a2.ID where a1.t>a2.t
)
SELECT * FROM a_com

ID          f    v
----------- ---- -----------
1           B-A  10
1           C-A  50
1           C-B  40
1           D-A  60
1           D-B  50
1           D-C  10
1           E-A  110
1           E-B  100
1           E-C  60
1           E-D  50
1           F-A  150
1           F-B  140
1           F-C  100
1           F-D  90
1           F-E  40
2           B-A  180
2           C-A  250
2           C-B  70
2           D-A  260
2           D-B  80
2           D-C  10
2           E-A  280
2           E-B  100
2           E-C  30
2           E-D  20
2           F-A  410
2           F-B  230
2           F-C  160
2           F-D  150
2           F-E  130
3           B-A  180
3           C-A  250
3           C-B  70
3           D-A  260
3           D-B  80
3           D-C  10
3           E-A  280
3           E-B  100
3           E-C  30
3           E-D  20
3           F-A  370
3           F-B  190
3           F-C  120
3           F-D  110
3           F-E  90

以上都算准备数据，还没加入条件过滤

---返回A表包含B中元素的行（这里的Inner join起到的作用和EXISTS 一样  ，这是你说的条件 2、TMA中的数据包含TMB中的1-2个。
s1 AS (
    select distinct a.id from a inner join b on a.n=b.n
)

结果是A的三行中都包含有A的元素

ID          t    n           t    n
----------- ---- ----------- ---- -----------
1           A    1130        C    1130
1           E    1240        E    1240
2           A    1130        C    1130
2           B    1310        F    1310
3           A    1130        C    1130
3           B    1310        F    1310

剩下的s2就是条件1，如果我把s1和s2名字对调可能更好

s2 as(
   select ID from a_com inner join b_com on a_com.f=b_com.f and a_com.v=b_com.v group by ID having count(0)=1
)

a_com.f=b_com.f 表示两个相减的元素相同，比如B是 B-A，那么A中也要是B-A a_com.v=b_com.v 就是对应的差值相同 having count(0)=1 表示同一个ID值（也就是B中同一行）仅包含一种，多的就不算了

select * from #tma as t 
where exists(select 0 from s1 where s1.ID=t.ID)
and exists(select 0 from s2 where s2.ID=t.ID)

总结： exists(select 0 from s1 where s1.ID=t.ID) 是应用条件2 exists(select 0 from s2 where s2.ID=t.ID)是应用条件1 如果不想应用的条件去掉就行了