联表查询的问题

[ysycysyc]

有表A、B,均有4个字段:a,b,c,d, 表B记录较多，表A只有一条记录，现在想从表B得到查询结果，条件如下：

A.a-A.b-A.c=B.a-B.b-B.c OR
A.a-A.b-A.d=B.a-B.b-B.d OR
A.a-A.c-A.d=B.a-B.c-B.d OR
A.b-A.c-A.d=B.b-B.c-B.d

即：表A任一字段减后面任意两位字段的差，与表B同位字段的计算结果相同。

用下面的语句好象会过滤一些结果，比如 A.a-A.b-A.c=B.a-B.b-B.c 和 A.b-A.c-A.d=B.b-B.c-B.d都满足条件，好象只会显示一次，而想要的结果是显示两次

WITH b(t,n)AS( --计算昨天数据和值
SELECT bc.t,bc.n FROM SSZT AS b
CROSS APPLY(VALUES('A',A),('B',B),('C',C),('D',D)) bc(t,n)
),b_com(f,v)AS(
SELECT b1.t+'-'+b2.t+'-'+b3.t, b1.n-b2.n-b3.n FROM b AS b1,b AS b2,b AS b3
)
,a(ID,t,n)AS( --计算原底数据和值
SELECT a.ID,ac.t,ac.n FROM SSYD1 AS a
CROSS APPLY(VALUES('A',A),('B',B),('C',C),('D',D)) ac(t,n)

),a_com(ID,f,v)AS(
SELECT a1.ID,a1.t+'-'+a2.t+'-'+a3.t, a1.n-a2.n-a3.n FROM a AS a1
INNER JOIN a AS a2 on a1.ID=a2.ID
INNER JOIN a AS a3 on a2.ID=a3.ID
WHERE a1.t>a2.t AND a2.t>a3.t
)

请大虾指点。

[ysycysyc]

各位大虾，昨天上传时忘改了，下面是改过的语句，请大虾们帮看一下： WITH b(t,n)AS( SELECT ac.t,ac.n FROM A AS a CROSS APPLY(VALUES('A',A),('B',B),('C',C),('D',D)) ac(t,n) ),a_com(f,v)AS( SELECT a1.t+'-'+a2.t+'-'+a3.t, a1.n-a2.n-a3.n FROM a AS a1,a AS a2,a AS a3 ) ,a(ID,t,n)AS( SELECT b.ID,bc.t,bc.n FROM B AS b CROSS APPLY(VALUES('A',A),('B',B),('C',C),('D',D)) bc(t,n) ),b_com(ID,f,v)AS( SELECT b1.ID,b1.t+'-'+b2.t+'-'+b3.t, b1.n-b2.n-b3.n FROM b AS b1 INNER JOIN a AS a2 on a1.ID=a2.ID INNER JOIN a AS a3 on a2.ID=a3.ID WHERE a1.t>a2.t AND a2.t>a3.t )

[二月十六]

楼主可以把原始数据和要读取那些数据结果说说，从半路看不太好弄

[ysycysyc]

比如，表A的记录为 1，2，3，4 a-b-c=1-2-3=-4 a-c-d=1-3-4=-6 表B的记录为 7， 2， 9， 10 11，12，3，14 5，32，41，52 这三条记录中，第一条a-b-c与表A的相同，均为-4 第二条a-b-c和a-c-d均与表A相同，分别为-4和-6 第三条没有 这样，如果其它的差再没有相同的，则希望得到的结果是： 7， 2， 9， 10 11，12，3，14 11，12，3，14 第二条记录因为有两个条件都符合，所以出现两次。

[听雨停了]

;WITH taba AS (
SELECT 1 AS a,2 AS b,3 AS c,4 AS d
),tabb AS (
SELECT 7 AS a, 2 AS b, 9 AS c, 10 AS d UNION ALL
select 11,12,3,14 UNION ALL
SELECT  5,32,41,52
)
--测试数据结束

SELECT b.*,'条件1' as [条件] FROM taba AS a
INNER JOIN tabb AS b ON A.a-A.b-A.c=B.a-B.b-B.c
UNION ALL
SELECT b.*,'条件2' as [条件] FROM taba AS a
INNER JOIN tabb AS b ON A.a-A.b-A.d=B.a-B.b-B.d
UNION ALL
SELECT b.*,'条件3' as [条件] FROM taba AS a
INNER JOIN tabb AS b ON A.a-A.c-A.d=B.a-B.c-B.d
UNION ALL
SELECT b.*,'条件4' as [条件] FROM taba AS a
INNER JOIN tabb AS b ON A.b-A.c-A.d=B.b-B.c-B.d


a           b           c           d           条件
----------- ----------- ----------- ----------- -----
7           2           9           10          条件1
11          12          3           14          条件1
7           2           9           10          条件2
11          12          3           14          条件3
11          12          3           14          条件4

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([a] int,[b] int,[c] int,[d] int)
Insert #A
select 1,2,3,4
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([a] int,[b] int,[c] int,[d] int)
Insert #B
select 7,2,9,10 union all
select 11,12,3,14 union all
select 5,32,41,52
Go
--测试数据结束
SELECT DISTINCT
    t2.a,
    t2.b,
    t2.c,
    t2.d,
    t2.rn
FROM
(
    SELECT *
    FROM #A A
        CROSS APPLY
    (
        VALUES
            (A.a - A.b - A.c),
            (A.a - A.b - A.d),
            (A.a - A.c - A.d),
            (A.b - A.c - A.d)
    ) t (rn)
) t1
    JOIN
    (
        SELECT *
        FROM #B B
            CROSS APPLY
        (
            VALUES
                (B.a - B.b - B.c),
                (B.a - B.b - B.d),
                (B.a - B.c - B.d),
                (B.b - B.c - B.d)
        ) t (rn)
    ) t2
        ON t2.rn = t1.rn;

[ysycysyc]

谢谢大虾！！