大家帮忙看一下这个where语句怎么写,谢谢!! 我想实现这样目的:当输入id时显示对就id的内容,不输入时全部显示如下表达只能显示输入对应的值,不输入时报错Select * from a where id=decode(&id,null,id,&id)谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 sql没办法直接控制吧,你用程序可以控制啊没输入id 时,就执行 select * from t有id 时,就执行 select * from t where id = id 用decode就能实现,有人知道怎么写吗? 不知道行不行,你试试Select * from a where id=decode('&id',null,id,'&id'); Select * from a where id=decode(&id,null,id,&id)这要写输入值时可以,为空时执行报错 Select * from a where id like nvl('&id','%'); Select * from a where id=nvl('%s',a.id)行不? Select * from a where id = &id or (&id is null); -- 有点笨的方法: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 = 2SQL> 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'); oracle如何重载操作符 调用一个javaSource的问题 rman备份/恢复Oracle10g数据库的问题 oracle自带备份机制 oracle 10g OEM 打开后输入用户名密码提示错误但在sql/plus中可以使用 ? 为什么我用导出时的作业状态是失败? Happy New Year! 请教一条两个表中的SQL语句 怎么解决(817和9i的)这个兼容性问题? 关于plsql,怎么都想不明白,各位多多指教了 急急 我想实现这样目的:当输入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');