sql server 写法:
select case when a.no is null then b.no else a.no end as no,
a.data as data1,b.data as data2
from table_1 a full join table_2 b
on a.no=b.no
select case when a.no is null then b.no else a.no end as no,
a.data as data1,b.data as data2
from table_1 a full join table_2 b
on a.no=b.no
解决方案 »
- sql查询语句
- sql2005中的clr,安全性问题,用过的请进!
- sp_addumpdevice的问题!
- 不明白,xml为何现在这么火,我理解这种格式不就是文本文件吗?感觉回归原始了。不解?
- 求一个函数实现如下功能:把192.168.1.1转换成192.168.001.001,谢谢!!!
- 一個很難的問題!
- SQL数据库字符类型字段写入的是数字,我想取出现有的小于十位数的数值最大的数。有更好的办法?
- 根据数据库文件(.mdf)(.ldf)还原数据库的问题?
- 求助!服务器突然断电,SQL Server居然不能开动事务机制了
- 怎样查询WMS系统特定库位(Location)
- 能不能用sql查到当前有哪些表被哪些用户锁住了?
- 高分救急!一个关于SQL的问题!
select iif(a.no is null,b.no,a.no) as no,
a.data as data1,b.data as data2
from table_1 a full join table_2 b
on a.no=b.no
select decode(a.no,null,b.no,a.no) as no,
a.data as data1,b.data as data2
from table_1 a full outer join table_2 b
on a.no=b.no
我现在就是找不到完全外连接怎么用
a.data as data1,b.data as data2
from table_1 a left join table_2 b
on a.no=b.no
union
select b.no,
a.data as data1,b.data as data2
from table_1 a right join table_2 b
on a.no=b.no
where a.no is null
select e,nvl(sum(b),0),nvl(sum(d),0) from (select decode(a,null,c,a) e,b,d from (select table_1.n
o a,table_1.data b,table_2.no c,table_2.data d from table_1,table_2 where table_1.no=table_2.no(
+) and table_1.data=table_2.data(+) and table_2.data is null union select table_1.no a,table_1.da
ta b,table_2.no c,table_2.data d from table_1,table_2 where table_1.no(+)=table_2.no and table_1
.data(+)=table_2.data and table_1.data is null)) group by(e);该语句实现了table_1和table_2中数据的校核,并查处所有不一止的记录.数据为空
就为零.
SQL> select * from table_1; NO DATA
---------- ----------
1 100
2 200
5 500SQL> select * from table_2
2 ; NO DATA
---------- ----------
1 100
2 300
6 600SQL> start test.sql E NVL(SUM(B),0) NVL(SUM(D),0)
---------- ------------- -------------
2 200 300
5 500 0
6 0 600
select a.no,a.data,b.data from table1 a,table2 b
where a.no=b.no(+)
union
select b.no,a.data,b.data from table1 a,table2 b
where b.no=a.no(+) and a.no is null
我还要进行整理.就是我开始提出的问题.我用的就是这种办法
生成table_1,table_2.我要在把table_1,table_2整理
成table_3.用我上面的方法可以实现,不知各位还有没有
更好的方法.我感觉我仅仅是实现,效率方面还不知怎样
a.data as data1,nvl(b.data,0) as data2
from table_1 a,table_2 b
on a.no=b.no(+)
where a.data<>b.data
union
select b.no,
nvl(a.data,0) as data1,b.data as data2
from table_1 a right join table_2 b
on a.no=b.no
where a.no is null
and a.data<>b.data
select a.no,
a.data as data1,nvl(b.data,0) as data2
from table_1 a,table_2 b
on a.no=b.no(+)
where a.data<>b.data
union
select b.no,
nvl(a.data,0) as data1,b.data as data2
from table_1 a,table_2 b
where b.no=a.no(+)
and a.no is null
and a.data<>b.data
on a.no=b.no(+)的写法
a.data as data1,nvl(b.data,0) as data2
from table_1 a,table_2 b
where a.no=b.no(+)
and a.data<>b.data
union
select b.no,
nvl(a.data,0) as data1,b.data as data2
from table_1 a,table_2 b
where b.no=a.no(+)
and a.no is null
and a.data<>b.data
---------- ----------
1 100
2 200
5 500SQL> select * from table_2; NO DATA
---------- ----------
1 100
2 300
6 600SQL> select a.no,
a.data as data1,nvl(b.data,0) as data2
from table_1 a,table_2 b
2 3 4 where a.no=b.no(+)
and a.data<>b.data
union
select b.no, 5 6 7
nvl(a.data,0) as data1,b.data as data2
from table_1 a,table_2 b
8 9 10 where b.no=a.no(+)
and a.no is null
and a.data<>b.data 11 12 ; NO DATA1 DATA2
---------- ---------- ----------
2 200 300
这是你的语句运行的结果,好像不太对吧
select * from
(select a.no,
a.data data1,nvl(b.data,0) data2
from table_1 a,table_2 b
where a.no=b.no(+)
union
select b.no,
nvl(a.data,0) as data1,b.data as data2
from table_1 a,table_2 b
where b.no=a.no(+))
where data1<>data2
1 100 100
我这里没有环境测试:
看看下一句:select a.no,
a.data as data1,nvl(b.data,0) as data2
from table_1 a,table_2 b
where a.no=b.no(+)
and a.data<>nvl(b.data,0)
union
select b.no,
nvl(a.data,0) as data1,b.data as data2
from table_1 a,table_2 b
where b.no=a.no(+)
and a.no is null
a.data as data1,nvl(b.data,0) as data2
from table_1 a,table_2 b
where a.no=b.no(+)
and a.data<>nvl(b.data,0) 结果应该:
2 200 300
5 500 0select b.no,
nvl(a.data,0) as data1,b.data as data2
from table_1 a,table_2 b
where b.no=a.no(+)
and a.no is null
结果应该:
6 0 600
SQL> select a.no,
a.data as data1,nvl(b.data,0) as data2
from table_1 a,table_2 b
where a.no=b.no(+)
and a.data<>nvl(b.data,0)
union 2 3 4 5 6
7 select b.no,
nvl(a.data,0) as data1,b.data as data2
from table_1 a,table_2 b
where b.no=a.no(+)
8 9 10 and a.no is null 11 ; NO DATA1 DATA2
---------- ---------- ----------
2 200 300
5 500 0
6 0 600SQL> select * from
(select a.no,
a.data data1,nvl(b.data,0) data2
from table_1 a,table_2 b
where a.no=b.no(+)
union
select b.no, 2 3 4 5 6 7
8 nvl(a.data,0) as data1,b.data as data2
from table_1 a,table_2 b
where b.no=a.no(+)) 9 10
where data1<>data2 11 ; NO DATA1 DATA2
---------- ---------- ----------
2 200 300
5 500 0
6 0 600
这语句比我的简单多了,好了,给分了