select decode(b,20,c,''),decode(b,50,c,'') from t_1 where a=1;
解决方案 »
- sqlplus用不了右键粘贴
- SQL SERVER 链接 oracle 问题 急!
- oracle连接错误
- 新手求教同一数据库下的导出,导入不成功
- 各位大人进来教一下本菜鸟啊!!!关于ORACLE设置
- pb8.0在服务器上能连上数据库,可是到工作站上却连不上,错误如下:
- 用oracle net配置方面(不同网段)的资料?谢谢
- 100分,请问在UNIX下用ODBC连ORACLE怎样配置?怎么写MAKEFILE?
- oracle9i的安装问题
- 怎样才能访问col$表?
- ==Oracle太奇怪了:帮我解释一下一句简单的Sql语句??===
- 关于SQL*Plus里的set timing on命令中显示的时间问题
---------- ---------- --------------------
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
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
40aElapsed: 00:00:00.30
而且补充一下,用的是ORACLE8.05.是不是有关系.
你执行的结果是两条.我想达到这样的效果,
如果两个条件都不满足.就没有检索出任何结果.
只要满足一个或两个.检索出的结果就始终是一行.
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
40aElapsed: 00:00:00.30
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虽然没有满足的记录仍然输出空. 输出结果也是一条记录.
20a 40a
select t1.c as c1,t2.c as c2 from testa t1,testa t2 where t1.b = 20 and t1.b + 30 = t2.b(+);c1 c2
20a
where a=1 and b=40) as c2 from dual;C1 C2
-------------------- --------------------
20a 40aElapsed: 00:00:00.50
SQL> select (select c from testa where a=1 and b=20) as c1 ,(select c from testa
where a=1 and b=50) as c2 from dual;C1 C2
-------------------- --------------------
20aElapsed: 00:00:00.60
但还是要感谢你们.liuhenger(热情) 你少了一个检索条件.a
就当前的数据而言是没有用的,
但实际的情况是a是一个检索条件.就是说,实际的数据a是一个变化的项目.
需要加上检索条件.
如果加上a这个检索条件你给的部分就有问题.
能不能把a也帮我加上去呢.
) and t1.b = 20 and t1.b + 30 = t2.b(+);C1 C2
---------- ----------
20aElapsed: 00:00:00.30
SQL> select t1.c as c1,t2.c as c2 from testa t1,testa t2 where (t1.a=1 or t2.a=1
) and t1.b = 20 and t1.b + 20 = t2.b(+);C1 C2
---------- ----------
20a 40aElapsed: 00:00:00.30
select t1.c as c1,t2.c as c2 from testa t1,testa t2 where t1.a=1 and t1.b = 20 and t1.b + 30 = t2.b(+);c1 c2
20a select t1.c as c1,t2.c as c2 from testa t1,testa t2 where t1.a=1 and t1.b = 20 and t1.b + 20 = t2.b(+);c1 c2
20a 40a
你加一些a为"2"这样的记录进去的时候.在执行一下你的代码看看. zhaoyongzhu(zhaoyongzhu)如果你加一些a为"2"这样的记录在执行,结果就更加奇怪了.
union
select ,c c2 from t_1 where a='1' and '50'
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';
where a=1 and (b='20' or b='50') ;
where a=1 and (b='20' or b='50') ;这样可以吧,跟zhaoyongzhu(zhaoyongzhu)的 很相象
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;
其他的同志也感谢了.问题解决了.