sql2K 两表相同字段相减问题

[mybelta2]

ck表：
CLID ZD1 ZD2 ZD3 ZD4
813 2 3 6 12
625 1 6 11 8
YL表：
CLCJ CLID ZD1 ZD2 ZD3 ZD4 heji
WANG 813 3 12 5 14 34
FENG 625 2 11 8 4 25
NAI 702 3 7 4 6 20

（可以把CK表看作库存，YL表看作本次要用的，要得出的结果集就相当于不够的需要采购的）
如何得出如下结果：YL表中对应的CLID减CK表对应各个字段的差，在YL中702，而在CK中没有702，则702应该在结果集中
CLCJ CLID ZD1 ZD2 ZD3 ZD4 heji
WANG 813 1 9 2 12
FENG 625 1 5 6
NAI 702 3 7 4 6 20

[you_tube]

heji
12]
6

这怎么出来的？

[lgx0914]

select ...
from yl left join ck on yl.clid=ck.clid
where ....

[昵称被占用了]

select yl.CLCJ,yl.CLID
,case when yl.ZD1 >= isnull(ck.ZD1,0) then yl.ZD1 - isnull(ck.ZD1,0) end as ZD1
,case when yl.ZD2 >= isnull(ck.ZD2,0) then yl.ZD2 - isnull(ck.ZD2,0) end as ZD2
,case when yl.ZD3 >= isnull(ck.ZD3,0) then yl.ZD3 - isnull(ck.ZD3,0) end as ZD3
,case when yl.ZD4 >= isnull(ck.ZD4,0) then yl.ZD4 - isnull(ck.ZD4,0) end as ZD4
,case when yl.ZD1 >= isnull(ck.ZD1,0) then yl.ZD1 - isnull(ck.ZD1,0) else 0 end +
 case when yl.ZD2 >= isnull(ck.ZD2,0) then yl.ZD2 - isnull(ck.ZD2,0) else 0 end +
 case when yl.ZD3 >= isnull(ck.ZD3,0) then yl.ZD3 - isnull(ck.ZD3,0) else 0 end +
 case when yl.ZD4 >= isnull(ck.ZD4,0) then yl.ZD4 - isnull(ck.ZD4,0) else 0 end as heji 
from yl left join ck
on yl.clid=ck.clid

[rmini]

[Quote=引用楼主 mybelta2 的帖子:]
ck表：
CLID ZD1 ZD2 ZD3 ZD4
813 2 3 6 12
625 1 6 11 8
YL表：
CLCJ CLID ZD1 ZD2 ZD3 ZD4 heji
WANG 813 3 12 5 14 34
FENG 625 2 11 8 4 25
NAI 702 3 7 4 6 20

（可以把CK表看作库存，YL表看作本次要用的，要得出的结果集就相当于不够的需要采购的）
如何得出如下结果：YL表中对应的…
[/Quote]
就是ZD1+ZD2+ZD3+ZD4 呀

[rmini]

[Quote=引用 3 楼 Haiwer 的回复:]
SQL codeselect yl.CLCJ,yl.CLID
,case when yl.ZD1 >= isnull(ck.ZD1,0) then yl.ZD1 - isnull(ck.ZD1,0) end as ZD1
,case when yl.ZD2 >= isnull(ck.ZD2,0) then yl.ZD2 - isnull(ck.ZD2,0) end as ZD2
,case when yl.ZD3 >= isnull(ck.ZD3,0) then yl.ZD3 - isnull(ck.ZD3,0) end as ZD3
,case when yl.ZD4 >= isnull(ck.ZD4,0) then yl.ZD4 - isnull(ck.ZD4,0) end as ZD4
,case when yl.ZD1 >= isnull(ck.ZD1,0) then…
[/Quote]

这个结果集中会不会出现 NAI 702 3 7 4 6 20 这条记录呢？（我是需要这条记录出现在结果集中）因为702在YL表中有，而在CK表中没有，我试试看

[水族杰纶]

DECLARE @A TABLE(CLID INT,    ZD1 INT,  ZD2 INT,  ZD3 INT,  ZD4 INT) 
INSERT @A SELECT 813 ,     2,    3,    6,    12 
INSERT @A SELECT 625,      1 ,   6,    11,  8 
DECLARE @B TABLE(CLCJ VARCHAR(10),  CLID INT,    ZD1 INT, ZD2  INT, ZD3 INT,  ZD4 INT   ,   heji INT) 
INSERT @B SELECT 'WANG' ,   813,    3,    12,    5  ,  14,34 
INSERT @B SELECT 'FENG'    ,625,    2 ,   11   , 8    ,4 ,25 
INSERT @B SELECT 'NAI'  ,702 ,   3  ,  7    ,4 ,   6 , 20 
SELECT B. CLID,
ABS(B.ZD1-ISNULL(A.ZD1,0))ZD1,
ABS(B.ZD2-ISNULL(A.ZD2,0))ZD2,
ABS(B.ZD3-ISNULL(A.ZD3,0))ZD3,
ABS(B.ZD4-ISNULL(A.ZD4,0))ZD4,
ABS(B.ZD1-ISNULL(A.ZD1,0))+
ABS(B.ZD2-ISNULL(A.ZD2,0))+
ABS(B.ZD3-ISNULL(A.ZD3,0))+
ABS(B.ZD4-ISNULL(A.ZD4,0))合計
FROM @B B LEFT JOIN @A A ON A.CLID=B.CLID
/*CLID        ZD1         ZD2         ZD3         ZD4         合計          
----------- ----------- ----------- ----------- ----------- ----------- 
813         1           9           1           2           13
625         1           5           3           4           13
702         3           7           4           6           20
*/

[rmini]

6楼的，我要的不是绝对值，要的是如果YL表中的小于CK表中的，则不显示

[水族杰纶]

[Quote=引用 7 楼 forevermini 的回复:]
6楼的，我要的不是绝对值，要的是如果YL表中的小于CK表中的，则不显示
[/Quote]

DECLARE @A TABLE(CLID INT,    ZD1 INT,  ZD2 INT,  ZD3 INT,  ZD4 INT) 
INSERT @A SELECT 813 ,     2,    3,    6,    12 
INSERT @A SELECT 625,      1 ,   6,    11,  8 
DECLARE @B TABLE(CLCJ VARCHAR(10),  CLID INT,    ZD1 INT, ZD2  INT, ZD3 INT,  ZD4 INT   ,   heji INT) 
INSERT @B SELECT 'WANG' ,   813,    3,    12,    5  ,  14,34 
INSERT @B SELECT 'FENG'    ,625,    2 ,   11   , 8    ,4 ,25 
INSERT @B SELECT 'NAI'  ,702 ,   3  ,  7    ,4 ,   6 , 20 
SELECT B. CLID,
CASE WHEN B.ZD1>ISNULL(A.ZD1,0) THEN B.ZD1-ISNULL(A.ZD1,0) ELSE 0 END ZD1,
CASE WHEN B.ZD2>ISNULL(A.ZD2,0) THEN B.ZD2-ISNULL(A.ZD2,0) ELSE 0 END ZD2,
CASE WHEN B.ZD3>ISNULL(A.ZD3,0) THEN B.ZD3-ISNULL(A.ZD3,0) ELSE 0 END ZD3,
CASE WHEN B.ZD4>ISNULL(A.ZD4,0) THEN B.ZD4-ISNULL(A.ZD4,0) ELSE 0 END ZD4,
CASE WHEN B.ZD1>ISNULL(A.ZD1,0) THEN B.ZD1-ISNULL(A.ZD1,0) ELSE 0 END +
CASE WHEN B.ZD2>ISNULL(A.ZD2,0) THEN B.ZD2-ISNULL(A.ZD2,0) ELSE 0 END +
CASE WHEN B.ZD3>ISNULL(A.ZD3,0) THEN B.ZD3-ISNULL(A.ZD3,0) ELSE 0 END+
CASE WHEN B.ZD4>ISNULL(A.ZD4,0) THEN B.ZD4-ISNULL(A.ZD4,0) ELSE 0 END 合計
FROM @B B LEFT JOIN @A A ON A.CLID=B.CLID
/*CLID        ZD1         ZD2         ZD3         ZD4         合計          
----------- ----------- ----------- ----------- ----------- ----------- 
813         1           9           0           2           12
625         1           5           0           0           6
702         3           7           4           6           20
*/

[rmini]

select yl.CLCJ,yl.CLID
,case when yl.ZD1 >= isnull(ck.ZD1,0) then yl.ZD1 - isnull(ck.ZD1,0) end as ZD1
,case when yl.ZD2 >= isnull(ck.ZD2,0) then yl.ZD2 - isnull(ck.ZD2,0) end as ZD2
,case when yl.ZD3 >= isnull(ck.ZD3,0) then yl.ZD3 - isnull(ck.ZD3,0) end as ZD3
,case when yl.ZD4 >= isnull(ck.ZD4,0) then yl.ZD4 - isnull(ck.ZD4,0) end as ZD4
,case when yl.ZD1 >= isnull(ck.ZD1,0) then yl.ZD1 - isnull(ck.ZD1,0) else 0 end +
case when yl.ZD2 >= isnull(ck.ZD2,0) then yl.ZD2 - isnull(ck.ZD2,0) else 0 end +
case when yl.ZD3 >= isnull(ck.ZD3,0) then yl.ZD3 - isnull(ck.ZD3,0) else 0 end +
case when yl.ZD4 >= isnull(ck.ZD4,0) then yl.ZD4 - isnull(ck.ZD4,0) else 0 end as heji
from yl left join ck
on yl.clid=ck.clid

OK