sql是这样的: select * from table1 where sftj='2' or (sftj='2' not sftj='3')
select * from table1 where sftj='2' or ( not sftj='2' and sftj='3')
不对,这样还是把sftj='3'的给选出来了
select * from table1 where sftj='2' or (sftj='2' and sftj<>'3')
哦,呵呵你两个条件是针对一个字段嘚.这样试试. SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 1000 1980-12-17 800.10 20 7499 ALLEN SALESMAN 7698 1981-2-20 4 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 10 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 5 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2: 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 10 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 1 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 0: 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 9 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 1 950.00 30 7902 FORD ANALYST 7566 1981-12-3 6 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 9 1300.00 10
14 rows selected
SQL> select * from emp where empno=7369 or ((select count(*) from emp where empno=7369)=0 and empno=7499);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 1000 1980-12-17 800.10 20
SQL> select * from emp where empno=1 or ((select count(*) from emp where empno=1)=0 and empno=7499);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 1981-2-20 4 1600.00 300.00 30
SQL>
select * from table1 where sftj='2' or (sftj='2' and sftj<>'3') 这样如果我sftj全是3,没有2的话,sftj=3就不出来了,不满足“条件一不满足则按照条件二查询”这点啊
select * from table1 where sftj='2' or sftj='3' 这样不就得了
sftj='3'始终存在,sftj='2'不一定始终有。sftj='2'、sftj='3'的数据可能同时存在,但是 当有sftj='2'的数据存在的时候,显示sftj='2'的数据; 如果没有,则显示sftj='3'的数据的数据 用sftj='2' or sftj='3'的话肯定不行,可能我前面没说清楚
select * from table1 where Decode(sftj,'2',1,Decode(sftj,'3',1))=1;
select * from table1 where sftj='2' or ((select count(*) from table1 where sftj='2')=0 and sftj='3')
貌似只能用标量实现. 你的要求相当于 select count(*) into v_num from table where 条件1 if v_num>0 then select * from table where 条件1; else select * from table where 条件2; end if;
嗯,用了minitoy的select * from table1 where sftj='2' or ((select count(*) from table1 where sftj='2')=0 and sftj='3'),可以了,太感谢了啊!
说实话 你给点代码 我们猜是很痛苦的 不过感觉逻辑有点混 试试 select * from table1 where sftj='2' or ((select count(1) from table1 where sftj='2')=0 and sftj='3')
1
----------
SQL> select 1 from dual where 1=2 or (not 1=2 and 3=3);
1
----------
1
SQL>
条件一: a.id=b.id
条件二: a.NAME=b.NAMEWHERE Decode(a.id,b.id,1,Decode(a.NAME,b.NAME,1))=1
条件1 or (条件1 not 条件2)
select * from table1 where sftj='2' or (sftj='2' not sftj='3')
select * from table1 where sftj='2' or (sftj='2' and sftj<>'3')
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 1000 1980-12-17 800.10 20
7499 ALLEN SALESMAN 7698 1981-2-20 4 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 10 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 5 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2: 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 10 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 1 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 0: 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 9 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 1 950.00 30
7902 FORD ANALYST 7566 1981-12-3 6 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 9 1300.00 10
14 rows selected
SQL> select * from emp where empno=7369 or ((select count(*) from emp where empno=7369)=0 and empno=7499);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 1000 1980-12-17 800.10 20
SQL> select * from emp where empno=1 or ((select count(*) from emp where empno=1)=0 and empno=7499);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 4 1600.00 300.00 30
SQL>
这样如果我sftj全是3,没有2的话,sftj=3就不出来了,不满足“条件一不满足则按照条件二查询”这点啊
这样不就得了
当有sftj='2'的数据存在的时候,显示sftj='2'的数据;
如果没有,则显示sftj='3'的数据的数据
用sftj='2' or sftj='3'的话肯定不行,可能我前面没说清楚
select * from table1 where Decode(sftj,'2',1,Decode(sftj,'3',1))=1;
你的要求相当于
select count(*) into v_num from table where 条件1
if v_num>0 then
select * from table where 条件1;
else
select * from table where 条件2;
end if;
试试
select * from table1 where sftj='2' or
((select count(1) from table1 where sftj='2')=0 and sftj='3')
难道所有的东西都必须写道一个SQL里面吗?