现在有表T,字段A~F,其中A~E为主键.现在表内数据如下 :A B C D E F
1 1 1 40 001 00001
1 1 1 40 002 00002
1 1 1 40 003 00003
1 1 1 50 004 00004要求实现查询结果如下:
A B C D E F D2 E2 F2
1 1 1 40 001 00001 50 004 00004
1 1 1 40 002 00002 空 空 空
1 1 1 40 003 00003 空 空 空D字段为'40'的全显示在左,'50'全显示在右!请问,在不添加字段的情况下,这个查询语句如何写才能实现这个结果!万分感谢!
我现在只能实现如下结果
A B C D E F D2 E2 F2
1 1 1 40 001 00001 50 004 00004
1 1 1 40 002 00002 50 004 00004
1 1 1 40 003 00003 50 004 00004
1 1 1 40 001 00001
1 1 1 40 002 00002
1 1 1 40 003 00003
1 1 1 50 004 00004要求实现查询结果如下:
A B C D E F D2 E2 F2
1 1 1 40 001 00001 50 004 00004
1 1 1 40 002 00002 空 空 空
1 1 1 40 003 00003 空 空 空D字段为'40'的全显示在左,'50'全显示在右!请问,在不添加字段的情况下,这个查询语句如何写才能实现这个结果!万分感谢!
我现在只能实现如下结果
A B C D E F D2 E2 F2
1 1 1 40 001 00001 50 004 00004
1 1 1 40 002 00002 50 004 00004
1 1 1 40 003 00003 50 004 00004
解决方案 »
- oracle调用webservice
- 查询结果集中的月份显示不全
- 怎么像sqlserver一样附加数据库呀
- 请教查询几个表的不同字段并在一起,不是关联
- 如何实现这样的分级汇总查询???
- 有关导入与表移动的问题,顶者有分
- 急急???Delphi中Oracle数据库执行问题,在线等!!!!!
- (简单问题)怎么把数据导入oracle
- 某仁兄给我的在PRO*C中按数组往数据库里插入数据的部分代码如下,其中的S和yctrow_count各是什么意思呢?
- 我要将数据库里面的TIME显示为05-SEP-2002转换为2002-09-05,如何做?
- 我是做数据库开发的,请大家给推荐oracle编程的好书,谢谢
- 请教高手:这条SQL怎么写?急!
select t1.* , nvl(t2.d,'空') d2 , nvl(t2.e,'空') e2 , nvl(t2.f,'空') f2 from
(select * from tb where d = 40) t1 left join
(select * from tb where d = 50) t2 on t1.a = t2.a--假设需要A,B,C进行关联
select t1.* , nvl(t2.d,'空') d2 , nvl(t2.e,'空') e2 , nvl(t2.f,'空') f2 from
(select * from tb where d = 40) t1 left join
(select * from tb where d = 50) t2 on t1.a = t2.a and t1.b = t2.b and t1.c = t2.c
(select t.* , row_number() over(order by D) px from tb t where d = 40) t1 left join
(select t.* , row_number() over(order by D) px from tb t where d = 50) t2 on t1.px = t2.px order by t1.a,t1.b,t1.c,t1.d,t1.f
insert into tb values(1 , 1 , 1 , 40 , '001' , '00001')
insert into tb values(1 , 1 , 1 , 40 , '002' , '00002')
insert into tb values(1 , 1 , 1 , 40 , '003' , '00003')
insert into tb values(1 , 1 , 1 , 50 , '004' , '00004')select t1.a , t1.b , t1.c , t1.d , t1.f , t2.d d2 , t2.e e2 , t2.f f2 from
(select t.* , row_number() over(order by D) px from tb t where d = 40) t1 left join
(select t.* , row_number() over(order by D) px from tb t where d = 50) t2 on t1.px = t2.px order by t1.a,t1.b,t1.c,t1.d,t1.fdrop table tb/*
A B C D F D2 E2 F2
---------- ---------- ---------- ---------- ----- ---------- ----- -----
1 1 1 40 00001 50 004 00004
1 1 1 40 00002
1 1 1 40 00003 3 rows selected.
*/
如果50的条数比40的多
我觉得dawugui 的应该这样写select t1.a , t1.b , t1.c , t1.d , t1.f , t2.d d2 , t2.e e2 , t2.f f2 from
(select t.* , row_number() over(order by D) px from tb t where d = 40) t1 left join
(select t.* , row_number() over(order by D) px from tb t where d = 50) t2 on t1.px = t2.px
union
select t1.a , t1.b , t1.c , t1.d , t1.f , t2.d d2 , t2.e e2 , t2.f f2 from
(select t.* , row_number() over(order by D) px from tb t where d = 40) t1 right join
(select t.* , row_number() over(order by D) px from tb t where d = 50) t2 on t1.px = t2.px
--50的条数比40的多的话可以用full join来实现
DROP TABLE TB;
CREATE TABLE TB(A INT, B INT, C INT, D VARCHAR2(5) , E VARCHAR2(5) , F VARCHAR2(5));
INSERT INTO TB VALUES(1 , 1 , 1 , '40' , '001' , '00001');
INSERT INTO TB VALUES(1 , 1 , 1 , '40' , '002' , '00002');
INSERT INTO TB VALUES(1 , 1 , 1 , '40' , '003' , '00003');
INSERT INTO TB VALUES(1 , 1 , 1 , '50' , '004' , '00004');SELECT T1.A,
T1.B,
T1.C,
T1.D,
T1.F,
NVL(T2.D, '空') D2,
NVL(T2.E, '空') E2,
NVL(T2.F, '空') F2
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY D) PX FROM TB T WHERE D = 40) T1
full JOIN (SELECT T.*, ROW_NUMBER() OVER(ORDER BY D) PX
FROM TB T
WHERE D = 50) T2 ON T1.PX = T2.PX
ORDER BY T1.A, T1.B, T1.C, T1.D, T1.F
--运行结果:
A B C D F D2 E2 F2
1 1 1 1 40 00001 50 004 00004
2 1 1 1 40 00002 空 空 空
3 1 1 1 40 00003 空 空 空
如何do it?
如下:SELECT T1.A, T1.B, T1.C, T1.D, T1.E, T1.F, T2.D D2, T2.E E2, T2.F F2
FROM
( SELECT T.*, ROWNUM ID
FROM T
WHERE T.D = 40) T1,
( SELECT T.*, ROWNUM ID
FROM T
WHERE T.D = 50) T2
WHERE T1.ID= T2.ID(+)
UNION
SELECT T1.A, T1.B, T1.C, T1.D, T1.E, T1.F, T2.D D2, T2.E E2, T2.F F2
FROM
( SELECT T.*, ROWNUM ID
FROM T
WHERE T.D = 40) T1,
( SELECT T.*, ROWNUM ID
FROM T
WHERE T.D = 50) T2
WHERE T1.ID(+)= T2.ID
看着用吧。排序根据需要自己增加
SELECT T1.A, T1.B, T1.C, T1.D, T1.E, T1.F, T2.D D2, T2.E E2, T2.F F2
FROM
( SELECT T.*, ROWNUM ID
FROM T
WHERE T.D = 40) T1 FULL JOIN
( SELECT T.*, ROWNUM ID
FROM T
WHERE T.D = 50) T2
ON T1.ID= T2.ID