表a,字段:code,mend (即材料代码,月末数)
0001 20
0002 30
0005 40
表b,字段:code,min (即材料代码,月入数)
0001 4
0008 6
我想得到:
0001 20 4
0002 30 0
0005 40 0
0008 0 6
0001 20
0002 30
0005 40
表b,字段:code,min (即材料代码,月入数)
0001 4
0008 6
我想得到:
0001 20 4
0002 30 0
0005 40 0
0008 0 6
select a.code ,a.mend ,b.min from a full outer join b on a.code=b.code
where a.code=b.code
From a full join b on a.code=b.code
from a,b
where a.code=b.code
2、将表a所有记录加入表,初始化min值为0
3、Loop表b所有的记录,查看是否在表c存在这样的记录
如果存在修改min的值
如果不存在add记录
FROM a LEFT JOIN
b ON a.code = b.code
UNION
SELECT b.code, a.mend, b.min
FROM b LEFT JOIN
a ON a.code = b.code
FROM a LEFT JOIN
b ON a.code = b.code
UNION
SELECT b.code, a.mend, b.mind
FROM b LEFT JOIN
a ON a.code = b.code
ORDER BY 1
select a.code ,IsNull(a.mend,0) ,IsNull(b.min,0) from a Left outer join b on a.code=b.code
union select b.code ,IsNull(a.mend,0) ,IsNull(b.min,0) from a right outer join b on a.code=b.code
注,在Sql Server下如此,如果是其他数据库你把IsNull函数去掉就是了!
然后加了一点谁都知道加的东西~
惭愧啊惭愧~
应该用case when 可以实现吧
FROM a LEFT JOIN
b ON a.code = b.code
UNION
SELECT b.code, a.mend, b.min
FROM b LEFT JOIN
a ON a.code = b.code
ORDER BY 1
是对的
我测试过
(select code ,0 as min,mend from a
union
select code,min ,0 as mend from b
) tmp
group by code
所有代码的记录均会查出来.
FROM a LEFT JOIN
b ON a.code = b.code
UNION
SELECT b.code, a.mend, b.min
FROM b LEFT JOIN
a ON a.code = b.code
我试了一下,没有出现0
而是变成空格
我自己在我的oracle上調試過select a.code,a.mend,b.min from a,b where a.code=b.code
union
select code,'0',bin from b where code not in (select code from a)union蹤向相加表紀錄,不過紀錄類型相互對應要同樣類型a.mend 試varchar2, '0'
必須是string.一定可以的
FROM a left JOIN b ON [a].[code]=[b].[code]
UNION SELECT [b].[code], IIF(ISNULL([a].[mend]),0,a.mend), [b].[min]
FROM a right JOIN b ON [a].[code]=[b].[code];
UNION select b.code ,Nz(a.mend,0) as Mend ,Nz(b.min) as Min from a right outer join b on a.code=b.code;