在SQLPLUS下这样sql>set numwidth 30然后执行这个语句看一下,肯定值不一样 select (select C2 from T1 where C1= 'A'), (select C2 from T1 where C1= 'G'), (select C2 from T1 where C1= 'A') - (select C2 from T1 where C1= 'G') from dual
SQL> with t1 as ( 2 select 'A' c1, 100 c2 from dual union all 3 select 'B' c1, 900 c2 from dual union all 4 select 'C' c1, 2589 c2 from dual union all 5 select 'D' c1, 123.6598 c2 from dual union all 6 select 'E' c1, 900 c2 from dual union all 7 select 'F' c1, 120 c2 from dual union all 8 select 'G' c1, 100 c2 from dual 9 ) 10 select C1,C2, dense_rank() over (order by C2 DESC) MC FROM T1 11 /
C1 C2 MC -- ---------- ---------- C 2589 1 E 900 2 B 900 2 D 123.6598 3 F 120 4 A 100 5 G 100 5
7 rows selected
没有发现类似的情况 create table t1(c1 varchar2(10),c2 number); insert into t1 select 'A',100 from dual; insert into t1 select 'B',900 from dual; insert into t1 select 'C',2589 from dual; insert into t1 select 'D',123.6598 from dual; insert into t1 select 'E',900 from dual; insert into t1 select 'F',120 from dual; insert into t1 select 'G',100 from dual;select c1,c2,dense_rank()over(order by c2 desc) from t1C 2589 1 E 900 2 B 900 2 D 123.6598 3 F 120 4 A 100 5 G 100 5
select (select C2 from T1 where C1= 'A'),
(select C2 from T1 where C1= 'G'),
(select C2 from T1 where C1= 'A') -
(select C2 from T1 where C1= 'G')
from dual
说明A 100 5
G 100 6根本就不是100,你的计算也证明了
和在SQLPLUS下这样sql>set numwidth 30没有任何关系,你设成set numwidth 2结果也是一样的,sqlplus客户端命令只会改变显示效果,不会改变SQL的执行结果
2 select 'A' c1, 100 c2 from dual union all
3 select 'B' c1, 900 c2 from dual union all
4 select 'C' c1, 2589 c2 from dual union all
5 select 'D' c1, 123.6598 c2 from dual union all
6 select 'E' c1, 900 c2 from dual union all
7 select 'F' c1, 120 c2 from dual union all
8 select 'G' c1, 100 c2 from dual
9 )
10 select C1,C2, dense_rank() over (order by C2 DESC) MC FROM T1
11 /
C1 C2 MC
-- ---------- ----------
C 2589 1
E 900 2
B 900 2
D 123.6598 3
F 120 4
A 100 5
G 100 5
7 rows selected
create table t1(c1 varchar2(10),c2 number);
insert into t1 select 'A',100 from dual;
insert into t1 select 'B',900 from dual;
insert into t1 select 'C',2589 from dual;
insert into t1 select 'D',123.6598 from dual;
insert into t1 select 'E',900 from dual;
insert into t1 select 'F',120 from dual;
insert into t1 select 'G',100 from dual;select c1,c2,dense_rank()over(order by c2 desc) from t1C 2589 1
E 900 2
B 900 2
D 123.6598 3
F 120 4
A 100 5
G 100 5