相减没有对a,b有什么要求吗?比如说时间相等? 是否只要排序号相同?
select * from ((select a.*,a.rownum rn from a a order by time)a ,(select b.*,b.rownum rn from b b order by time) b) where a.rn = b.rn
这样?
select * from ((select a.*,a.rownum rn from a a order by time)a ,(select b.*,b.rownum rn from b b order by time) b) where a.rn = b.rn
这样?
TABLE A
123
234
456
432TABLE B
904
872
834
765
456这样B表减A表怎么操作
select * from
(select a.*,row_number()over(order by 时间) rn from a) a
full join
(select b.*,row_number()over(order by 时间) rn from b) b
on a.rn = b.rn
SQL> select rownum,A from T where rownum<=10 order by A; ROWNUM A
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10已选择10行。SQL> UPDATE T SET A=100-A;已更新99行。SQL> select rownum,A from T where rownum<=10 order by A; ROWNUM A
---------- ----------
10 90
9 91
8 92
7 93
6 94
5 95
4 96
3 97
2 98
1 99已选择10行。
create table a(id int , value int )
create table b(id int , value int )
go
insert into a values(1,100),(3,400),(5,123),(9,593)
insert into b values(1,5) , (2,45) ,(4,22) ,(3,100)
go
with
ca as (select ROW_NUMBER() over (order by id) as rn , id,value from a ) ,
cb as (select ROW_NUMBER() over (order by id) as rn , id,value from b )
select ca.id , ca.value, cb.id,cb.value , ca.value - cb.value as [a-b]
from ca , cb
where ca.rn = cb.rn
(4 行受影响)(4 行受影响)
id value id value a-b
----------- ----------- ----------- ----------- -----------
1 100 1 5 95
3 400 2 45 355
5 123 3 100 23
9 593 4 22 571(4 行受影响)
-- 刚才是按 sql sever 写的,现在改了一下。
create table a(id int , value int );create table b(id int , value int );insert into a values(1,100);
insert into a values(3,400);
insert into a values(5,123);
insert into a values(9,593);
insert into b values(1,5);
insert into b values(2,45) ;
insert into b values(4,22) ;
insert into b values(3,100);with
ca as (select ROW_NUMBER() over (order by id) as rn , id,value from a ) ,
cb as (select ROW_NUMBER() over (order by id) as rn , id,value from b )
select ca.id , ca.value, cb.id,cb.value , ca.value - cb.value as "a-b"
from ca , cb
where ca.rn = cb.rn
8楼是正确的,我想 了以下,想用嵌套子查询来查询,在plsql中操作了一下,楼主代码确实简练,技术太差,没办法写出更简便的方法