我想实现这样目的:当输入id时显示对就id的内容,不输入时全部显示
如下表达只能显示输入对应的值,不输入时报错
Select * from a where id=decode(&id,null,id,&id)
谢谢!
如下表达只能显示输入对应的值,不输入时报错
Select * from a where id=decode(&id,null,id,&id)
谢谢!
解决方案 »
- 在线等——如何获取两个时间段间的所有月份的最后一天?
- 请问oracle精简版+PL DEVELOPER如何创建数据库?
- Report Builder列印時報錯:REP-3300
- 分享:proc预处理c++程序standards.h出错解决方法
- ************这个动态SQL如何执行?***********
- 批處理 調用pck文件 怎么寫~~ 求教~~ 謝謝~
- 【数据库设计】请教高手!!!三张表关联与两张表关联
- Oracle如何在sql语句中where子句上加if else判断
- drop public synonym tablename 是什么意思呀?
- 请问oracle9i中service名和SID的区别
- 急急
- 我想实现这样目的:当输入id时显示对就id的内容,不输入时全部显示
有id 时,就执行 select * from t where id = id
这要写输入值时可以,为空时执行报错
SQL> SELECT * FROM TEST_22 A; SID SNAME CODE CHENGJI
---------- -------------------- ---------- ----------
1 ANDY 101 90
2 ANDY 101 70
3 ANDY 100 70
4 ZHANG 101 70
5 ZHANG 101 80-- SID = 2
SQL> SELECT DISTINCT SID, SNAME, CODE, CHENGJI
2 FROM (SELECT B.*,
3 COUNT(SID) OVER() COUNTS,
4 COUNT(DISTINCT SID) OVER() DIS_COUNTS
5 FROM (SELECT A.*, 1 SIGN
6 FROM TEST_22 A
7 WHERE SID = &ID
8 UNION ALL
9 SELECT A.*, 2 SIGN FROM TEST_22 A) B
10 ORDER BY SIGN) C
11 WHERE ROWNUM <= DECODE(SIGN(COUNTS - DIS_COUNTS), 1, 1, COUNTS); SID SNAME CODE CHENGJI
---------- -------------------- ---------- ----------
2 ANDY 101 70-- SID = 6
SQL> SELECT DISTINCT SID, SNAME, CODE, CHENGJI
2 FROM (SELECT B.*,
3 COUNT(SID) OVER() COUNTS,
4 COUNT(DISTINCT SID) OVER() DIS_COUNTS
5 FROM (SELECT A.*, 1 SIGN
6 FROM TEST_22 A
7 WHERE SID = &ID
8 UNION ALL
9 SELECT A.*, 2 SIGN FROM TEST_22 A) B
10 ORDER BY SIGN) C
11 WHERE ROWNUM <= DECODE(SIGN(COUNTS - DIS_COUNTS), 1, 1, COUNTS); SID SNAME CODE CHENGJI
---------- -------------------- ---------- ----------
1 ANDY 101 90
2 ANDY 101 70
3 ANDY 100 70
4 ZHANG 101 70
5 ZHANG 101 80
这样还不能用&,只会替代一次,用&&才行可以替代多次。如下
SQL> select count(*) from t1; COUNT(*)
----------
8
没有条件时,总共8条记录。SQL> select count(*) from t1 where name=decode('&&name1', null, name, '&&name1');
输入 name1 的值: (\ddd)111111
原值 1: select count(*) from t1 where name=decode('&&name1', null, name, '&&name1')
新值 1: select count(*) from t1 where name=decode('(\ddd)111111', null, name, '(\ddd)111111') COUNT(*)
----------
1输入了值,则为 where name=decode('(\ddd)111111', null, name, '(\ddd)111111')SQL> undefine name1;
SQL> select count(*) from t1 where name=decode('&&name1', null, name, '&&name1');
输入 name1 的值:
原值 1: select count(*) from t1 where name=decode('&&name1', null, name, '&&name1')
新值 1: select count(*) from t1 where name=decode('', null, name, '') COUNT(*)
----------
8没有输入值,则为 where name=decode('', null, name, '') 等价于 name=name 所以8条记录
Select * from a where id=decode('&id',null,id,'&id');