SQL> create table A(a number primary key,b number);Table created.SQL> create table b(a number,c number,constraint fk_b_a foreign key (a) references a(a));Table created.SQL> insert into a values(&a,&b);
Enter value for a: 1
Enter value for b: 1
old 1: insert into a values(&a,&b)
new 1: insert into a values(1,1)1 row created.SQL> /
Enter value for a: 2
Enter value for b: 2
old 1: insert into a values(&a,&b)
new 1: insert into a values(2,2)1 row created.SQL> commit;Commit complete.SQL> select * from a; A B
---------- ----------
1 1
2 2SQL> insert into b values(&a,&c);
Enter value for a: 1
Enter value for c: 11
old 1: insert into b values(&a,&c)
new 1: insert into b values(1,11)1 row created.SQL> /
Enter value for a: 1
Enter value for c: 12
old 1: insert into b values(&a,&c)
new 1: insert into b values(1,12)1 row created.SQL> /
Enter value for a: 2
Enter value for c: 21
old 1: insert into b values(&a,&c)
new 1: insert into b values(2,21)1 row created.SQL> /
Enter value for a: 2
Enter value for c: 22
old 1: insert into b values(&a,&c)
new 1: insert into b values(2,22)1 row created.SQL> commit;Commit complete.SQL> select * from b; A C
---------- ----------
1 11
1 12
2 21
2 22SQL> select a.a,b.a,b.c
2 from a,b
3 where a.a(+)=b.a; A A C
---------- ---------- ----------
1 1 11
1 1 12
2 2 21
2 2 22
Enter value for a: 1
Enter value for b: 1
old 1: insert into a values(&a,&b)
new 1: insert into a values(1,1)1 row created.SQL> /
Enter value for a: 2
Enter value for b: 2
old 1: insert into a values(&a,&b)
new 1: insert into a values(2,2)1 row created.SQL> commit;Commit complete.SQL> select * from a; A B
---------- ----------
1 1
2 2SQL> insert into b values(&a,&c);
Enter value for a: 1
Enter value for c: 11
old 1: insert into b values(&a,&c)
new 1: insert into b values(1,11)1 row created.SQL> /
Enter value for a: 1
Enter value for c: 12
old 1: insert into b values(&a,&c)
new 1: insert into b values(1,12)1 row created.SQL> /
Enter value for a: 2
Enter value for c: 21
old 1: insert into b values(&a,&c)
new 1: insert into b values(2,21)1 row created.SQL> /
Enter value for a: 2
Enter value for c: 22
old 1: insert into b values(&a,&c)
new 1: insert into b values(2,22)1 row created.SQL> commit;Commit complete.SQL> select * from b; A C
---------- ----------
1 11
1 12
2 21
2 22SQL> select a.a,b.a,b.c
2 from a,b
3 where a.a(+)=b.a; A A C
---------- ---------- ----------
1 1 11
1 1 12
2 2 21
2 2 22
我想要的结果就是,比如说,
A 表中字段值 ID1,A1
对应
B表中字段 ID1,B1
ID1,B2我想通过一条SELECT语句,显示结果 ID1,A1,B1,B2
不知道如何写请指教一下!
你想要的结果就是:比如说,
A 表中字段值 ID1,A1
对应
B表中字段 ID1,B1
ID1,B2你想通过一条SELECT语句,显示结果 ID1,A1,B1,B2你想,显示的结果随着B的数据不同而不同,没有确定,那么我怎样来显示结果集呢?
比如还有字段
ID1,B3
ID1,B4
……
ID1,Bn
ID2,B1
ID2,B2
……
ID2,Bm
那么我该显示什么呢?
所以你的题目有问题!
select a.id,a.a1 from A_table a,(select id,subbfield(B) sumb from B_table group by id) b where a.id=b.id;
函数subbfield已经有人写过,在此论坛可以找到。
姓名 工资项 工资
张三 基本工资 1000
张三 岗位工资 2000
张三 效益工资 200
李四 基本工资 1000
李四 效益工资 1000
.......我需要用一个SQL语句得到如下所示的结果:
姓名 基本工资 岗位工资 效益工资 .....
张三 1000 2000 200 .....
李四 1000 0 1000 .....
....
select 姓名, sum(decode(工资项,'基本工资',工资,0)) 基本工资,
sum(decode(工资项,'岗位工资',工资,0)) 岗位工资, ....
from yourtable
group by 姓名
CREATE OR REPLACE FUNCTION get_b(tmp_id NUMBER)
RETURN VARCHAR2
IS
Col_b VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT b FROM B_table WHERE id=tmp_id) LOOP
Col_b := Col_b||cur.b;
END LOOP;
Col_b:= rtrim(Col_b,1);
RETURN Col_b;
END;
/select a.id,a.a1,b.sumb from A_table a,(select distinct id,get_b(id) sumb from B_table) b where a.id=b.id;