seelct a.a,a.b,b.c from b right join a on b.a=a.a order by a.a
SELECT A.[a], A.[b],B.[c] FROM A left join B ON A.[a]=B.[a] union SELECT B.[a], A.[b],B.[c] FROM B left join A ON B.[a]=A.[a]
多谢了,但还是存在两个小问题 1 我想把查询结果写入另一个表D中: SELECT A.[a], A.[b],B.[c] INTO D FROM A left join B ON A.[a]=B.[a] union SELECT B.[a], A.[b],B.[c] INTO D FROM B left join A ON B.[a]=A.[a] 这时出错:动作查询不能作为行的来源2 原来没有的数据(如结果第二行的c)在结果中是空值,而不是0这个怎么解决呢
select * into D from (SELECT A.[a], A.[b],iif(B.[c] is null,0,B.[c]) FROM A left join B ON A.[a]=B.[a] UNION SELECT B.[a], iif(A.[b] is null,0,A.[b]),B.[c] FROM B left join A ON B.[a]=A.[a])
太感谢了。我运行了代码,但是报告 iff函数未定义 我应该怎么修改呢
select ISNULL(A.a,B.a),ISNULL(A.b,'0'),ISNULL(B.c,'0') from A full outer join B on A.a=B.a
但是报告 iff函数未定义,haipingma(ToJava) 是ACCESS数据库的.在SQL里用CASE .... WHEN ... END '------------------------------------------------ select * into D from (SELECT A.[a], A.[b],CASE WHEN B.[c] is null THEN 0 ELSE B.[c] END, FROM A left join B ON A.[a]=B.[a] UNION SELECT B.[a], CASE WHEN A.[B] is null THEN 0 ELSE A.[B] END,B.[c] FROM B left join A ON B.[a]=A.[a])
在VB下能使用iif函数 在sql下没有iif函数
实在是太谢谢各位了,但这回还是不能运行 用case...when...else...end时,会出现这个错误: 对象'Open'的方法'_Recordset'失败 用IsNull函数出现这个提示: ISNULL参数的个数不对BTW,我用的是Access+VB6,没用SQL Server
FROM A
left join B ON A.[a]=B.[a]
union
SELECT B.[a], A.[b],B.[c]
FROM B
left join A ON B.[a]=A.[a]
1
我想把查询结果写入另一个表D中:
SELECT A.[a], A.[b],B.[c] INTO D
FROM A
left join B ON A.[a]=B.[a]
union
SELECT B.[a], A.[b],B.[c] INTO D
FROM B
left join A ON B.[a]=A.[a]
这时出错:动作查询不能作为行的来源2
原来没有的数据(如结果第二行的c)在结果中是空值,而不是0这个怎么解决呢
into D
from (SELECT A.[a], A.[b],iif(B.[c] is null,0,B.[c])
FROM A
left join B ON A.[a]=B.[a]
UNION SELECT B.[a], iif(A.[b] is null,0,A.[b]),B.[c]
FROM B
left join A ON B.[a]=A.[a])
我应该怎么修改呢
from A full outer join B
on A.a=B.a
'0' 可替换为你需要的字符
不加空值判断没有的字段为NULL
ISNULL(A.b,'0'),ISNULL(B.c,'0')
'------------------------------------------------
select *
into D
from (SELECT A.[a], A.[b],CASE WHEN B.[c] is null THEN 0 ELSE B.[c] END,
FROM A
left join B ON A.[a]=B.[a]
UNION SELECT B.[a], CASE WHEN A.[B] is null THEN 0 ELSE A.[B] END,B.[c]
FROM B
left join A ON B.[a]=A.[a])
在sql下没有iif函数
用case...when...else...end时,会出现这个错误:
对象'Open'的方法'_Recordset'失败
用IsNull函数出现这个提示:
ISNULL参数的个数不对BTW,我用的是Access+VB6,没用SQL Server