-- 不知道这样行不行: select a, abs(count(decode(a,1,1,0))-count(decode(a,2,1,0))) as cnt from table_name where a=1 or a=2 group by a;
--只想到这样: SELECT Abs(rn1-rn2) FROM (SELECT ROWNUM rn1,t.* FROM t) t1, (SELECT ROWNUM rn2,t.* FROM t)t2 WHERE t1.a=1 AND t2.a=2;
你这样试试:select sum(decode(empno,7369,1,7654,-1,0)) from emp WHERE empno IN(7369,7654);结果是0,而我这里行号相减是是4 SELECT Abs(rn1-rn2) FROM (SELECT ROWNUM rn1,emp.* FROM emp ) a, (SELECT ROWNUM rn2,emp.* FROM emp ) b WHERE a.empno=7369 AND b.empno=7654;
SQL> select count(*) from emp where deptno=10;
COUNT(*) ---------- 3
SQL> select count(*) from emp where deptno=20;
COUNT(*) ---------- 5
SQL> select sum(decode(deptno,20,1,10,-1,0)) from emp;
-- 不知道这样行不行:
select a, abs(count(decode(a,1,1,0))-count(decode(a,2,1,0))) as cnt
from table_name
where a=1 or a=2
group by a;
--只想到这样:
SELECT Abs(rn1-rn2) FROM
(SELECT ROWNUM rn1,t.* FROM t) t1,
(SELECT ROWNUM rn2,t.* FROM t)t2
WHERE t1.a=1 AND t2.a=2;
SELECT Abs(rn1-rn2) FROM
(SELECT ROWNUM rn1,emp.* FROM emp ) a,
(SELECT ROWNUM rn2,emp.* FROM emp ) b
WHERE a.empno=7369 AND b.empno=7654;
COUNT(*)
----------
3
SQL> select count(*) from emp where deptno=20;
COUNT(*)
----------
5
SQL> select sum(decode(deptno,20,1,10,-1,0)) from emp;
SUM(DECODE(DEPTNO,20,1,10,-1,0
------------------------------
2
SQL>
该喝点维他命ABCD了
⊙﹏⊙b汗