有两个表A、BA: id a1 a2 a3
1 1 1 1
2 2 2 2
3 3 3 3B: id b1 b2 b3
1 4 6 8
1 5 7 9
3 6 8 0
我想得到
ida a1 a2 a3 idb b1 b2 b3
1 1 1 1 1 5 7 9
3 3 3 3 3 6 8 0该怎么作阿??
1 1 1 1
2 2 2 2
3 3 3 3B: id b1 b2 b3
1 4 6 8
1 5 7 9
3 6 8 0
我想得到
ida a1 a2 a3 idb b1 b2 b3
1 1 1 1 1 5 7 9
3 3 3 3 3 6 8 0该怎么作阿??
解决方案 »
- c/c++ oci大量数据的插入
- Oracle 11gR2安装后其他机器不能连接到1521端口
- [提问]9i在Linux和Unix下的区别
- ORA-06502: PL/SQL: 数字或值错误,应该不是缓冲区的问题,请帮忙看看,万分感谢...
- 文字字符串过长
- 菜鸟求助:使用Profile限制会话资源
- ora-27100 oracle shared memory realm already exists
- 问一条sql语句,应该不是很难!
- 大G们,我遇到了个存储过程的问题,今天就要交任务了,救救小M啊,在线急等
- 在触发器中分割字符串
- 如何计算数据库占用物理空间
- 请大家讨论oracle9i和10g的区别
ida a1 a2 a3 idb b1 b2 b3
1 1 1 1 1 5 7 9
3 3 3 3 3 6 8 0
为啥没有1 4 6 8 ida a1 a2 a3 idb b1 b2 b3
1 1 1 1 1 5 7 9 1 4 6 8
3 3 3 3 3 6 8 0
不对,b join A也会有3行
---------- ---------- ---------- ----------
1 1 1 1
2 2 2 2
3 3 3 3scott@ORA1> select * from b; ID B1 B2 B3
---------- ---------- ---------- ----------
1 4 6 8
1 5 7 9
3 6 8 0scott@ORA1> select * from a inner join b on a.id = b.id; ID A1 A2 A3 ID B1 B2 B3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 4 6 8
1 1 1 1 1 5 7 9
3 3 3 3 3 6 8 0
-- 或者
select * from a,b where a.id=b.id;
from A,
(select ID, max(B1) as B1, max(B2) as B2, max(B3) as B3
from B
group by ID) C
where A.ID = B.ID
SQL> select a.id ida, a.a1, a.a2, a.a3, t.id idb, t.b1, t.b2, t.b3
2 from a,
3 (select id, b1, b2, b3
4 from (select b.*,
5 row_number() over(partition by id order by b1 desc, b2 desc, b3 desc) rn
6 from b)
7 where rn = 1) t
8 where a.id = t.id;
IDA A1 A2 A3 IDB B1 B2 B3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 5 7 9
3 3 3 3 3 6 8 0
取值较小的那行
SQL> select a.id ida, a.a1, a.a2, a.a3, t.id idb, t.b1, t.b2, t.b3
2 from a,
3 (select id, b1, b2, b3
4 from (select b.*,
5 row_number() over(partition by id order by b1, b2 , b3 ) rn
6 from b)
7 where rn = 1) t
8 where a.id = t.id;
IDA A1 A2 A3 IDB B1 B2 B3
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 4 6 8
3 3 3 3 3 6 8 0
select a.id,a.a1 ,a.a2 ,a.a3,
b.id,sum(b.b1) as b1,sum(b.b2) as b2 ,sum(a.b3) as b3,
from a ,b
wher a.id= b.id
group by b.id
select a.id,a.a1 ,a.a2 ,a.a3,
b.id,sum(b.b1) as b1,sum(b.b2) as b2 ,sum(a.b3) as b3,
from a ,b
wher a.id= b.id
group by b.id