高分求助SQL语句.100分.在线等待.

[xiaobeibei]

假设表如下:t_1(主键:a,b)
a b c

1 20 "20a"
1 30 "30a"
1 40 "40a"
有这样的3条数据.
用一条SQL语句(必须是一条)实现以下的功能:

1) 检索 a = 1 b =20 的记录 取出c 另表示为 "C1"
select c as C1 from t_1 where a = '1' and b = '20'

2) 检索 a = 1 b =50 的记录 取出c 另表示为 "C2"
select c as C2 from t_1 where a = '1' and b = '50'

按照现在数据库的记录,1)是存在的.2)不存在.
我想达到这样的效果: C1 和 C2 都检索出来.
C1 C2
"20a" <- 空
如果1)和2)都有记录就都显示出来.
用一条SQL语句能够实现吗?

[jlandzpa]

如果你的值只是数字是可以作到的.

[xuedscn]

有意义吗??????????

[johnzhuking]

select DECODE( name,'20', dept) as c1,DECODE( name,'50',dept) as c2 from a
where id=1 and (name='20' or name='50') ;

//select dept as c1 from a where id=1 and name='20'
// union
//select DECODE(count(dept),null,'') as c2 from a where id=1 and name='50';

[johnzhuking]

select DECODE( b,'20', c) as c1,DECODE( b,'50',c) as c2 from a
where a=1 and (b='20' or b='50') ;

[johnzhuking]

select DECODE( b,'20', c) as c1,DECODE( b,'50',c) as c2 from t_1
where a=1 and (b='20' or b='50') ;

这样可以吧,跟zhaoyongzhu(zhaoyongzhu)的 很相象

[qiuyang_wang]

方法虽然笨点，但绝对正确：
select a.c1,b.c2 from (select '1' as bz ,c as c1 from testa where a = 1 and b = 20) a,
(select '1' as bz ,c as c2 from testa where a = 1 and b = 50) b
where a.bz = b.bz(+) union
select a.c1,b.c2 from (select '1' as bz ,c as c1 from testa where a = 1 and b = 20) a,
(select '1' as bz ,c as c2 from testa where a = 1 and b = 50) b
where a.bz(+) = b.bz;

[zhaoyongzhu]

select decode(b,20,c,''),decode(b,50,c,'') from t_1 where a=1;

[zhaoyongzhu]

select decode(b,20,c,'') as c1,decode(b,50,c,'') as c2 from t_1 where a=1;

[xiaobeibei]

我试验过了,如果满足上面两个条件检索出的记录是两条.
应该是一条才对.

[zhaoyongzhu]

SQL> create table testa (a number,b number,c varchar2(10));

Table created.

SQL> insert into testa values (1,20,'20a');

1 row created.

SQL> insert into testa values (1,30,'30a');

1 row created.

SQL> insert into testa values (1,40,'40a');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testa;

A B C
---------- ---------- --------------------
1 20 20a
1 30 30a
1 40 40a


SQL> select decode(b,20,c,'') as c1,decode(b,50,c,'') as c2 from testa where a=1
;

C1 C2
-------------------- --------------------
20a

[luckysxn]

同意

[xiaobeibei]

如果按照
select decode(b,20,c,'') as c1,decode(b,40,c,'') as c2 from testa where a=1
检索出的是两行.我想达到这样的效果,
除非两个条件都不满足.要不检索出的结果始终是一行.

在一次的感谢!!

[zhaoyongzhu]

SQL> select decode(b,20,c,'') as c1,decode(b,40,c,'') as c2 from testa where a=
and (b=20 or b=40);

C1 C2
-------------------- --------------------
20a
40a

Elapsed: 00:00:00.30

[xiaobeibei]

还是不行呀,a= and 这里好象有问题把.
而且补充一下,用的是ORACLE8.05.是不是有关系.

[bzszp]

a= 1吧

[xiaobeibei]

zhaoyongzhu(zhaoyongzhu)从上面你的结果好象也能看出来.
你执行的结果是两条.

我想达到这样的效果,
如果两个条件都不满足.就没有检索出任何结果.
只要满足一个或两个.检索出的结果就始终是一行.

[zhaoyongzhu]

sorry：
SQL> select decode(b,20,c,'') as c1,decode(b,40,c,'') as c2 from testa where a=1
and (b=20 or b=40);

C1 C2
-------------------- --------------------
20a
40a

Elapsed: 00:00:00.30

[bzszp]

结果就始终是一行?
什么意思?

[xiaobeibei]

如果按照 select t1.c as c1 , t2.c as c2 from testa t1, testa t2
where t1.a = '1' and t1.b = '20' and t2.a = '1' and t2.b = '40'
来检索.
根据现在的记录.
结果是:
c1 c2
20a 40a 输出结果是一条记录.

如果改变了上面的一个检索条件: 比如:t2.b = '50'
按照上面那句SQL应该是一条记录也没有检索出来.
我想改成这样的效果:

c1 c2
20a "" c2虽然没有满足的记录仍然输出空. 输出结果也是一条记录.

[xiaobeibei]

qiuyang_wang(小数点)又一次的佩服你了.
其他的同志也感谢了.问题解决了.