建表: create table SIZES(
ID NUMBER,
NAME VARCHAR2(100),
VALUE VARCHAR2(10),
OWN VARCHAR2(10)
)插入语句:
insert into SIZES (ID, NAME, VALUE, OWN) values (1, 'a', '----', 'f0');
insert into SIZES (ID, NAME, VALUE, OWN) values (2, 'b', '23', 'f0');
insert into SIZES (ID, NAME, VALUE, OWN) values (3, 'c', '-', 'f0');
insert into SIZES (ID, NAME, VALUE, OWN) values (4, 'd', '1231', 'f0');
insert into SIZES (ID, NAME, VALUE, OWN) values (1, 'a', '-', 'f1');
insert into SIZES (ID, NAME, VALUE, OWN) values (2, 'b', '45', 'f1');
insert into SIZES (ID, NAME, VALUE, OWN) values (3, 'c', '-', 'f1');
insert into SIZES (ID, NAME, VALUE, OWN) values (4, 'd', '56', 'f1');
insert into SIZES (ID, NAME, VALUE, OWN) values (5, 'f', '-', 'f1');此时执行
select
max(decode(s.own,'f0',s.value,0)) "f0",
max(decode(s.own,'f1',s.value,0)) "f1"
from sizes s group by s.id order by s.id
显示结果为:
f0 f1
--------
0 0
23 45
0 0
1231 56
0 -
为什么结果不是下面这样呢。。,可是把类似----换成其他符号可以呢,比如“|| \ | ! ”呢
f0 f1
--------
---- -
23 45
- -
1231 56
0 -
但是在表中在添加一条记录insert into SIZES (ID, NAME, VALUE, OWN) values (5, 'f', '-', 'f0');时,
原来查询结果中的0 - 也变成了 0 0 呢?大家遇到过这样的情况吗? 请教了,谢谢
ID NUMBER,
NAME VARCHAR2(100),
VALUE VARCHAR2(10),
OWN VARCHAR2(10)
)插入语句:
insert into SIZES (ID, NAME, VALUE, OWN) values (1, 'a', '----', 'f0');
insert into SIZES (ID, NAME, VALUE, OWN) values (2, 'b', '23', 'f0');
insert into SIZES (ID, NAME, VALUE, OWN) values (3, 'c', '-', 'f0');
insert into SIZES (ID, NAME, VALUE, OWN) values (4, 'd', '1231', 'f0');
insert into SIZES (ID, NAME, VALUE, OWN) values (1, 'a', '-', 'f1');
insert into SIZES (ID, NAME, VALUE, OWN) values (2, 'b', '45', 'f1');
insert into SIZES (ID, NAME, VALUE, OWN) values (3, 'c', '-', 'f1');
insert into SIZES (ID, NAME, VALUE, OWN) values (4, 'd', '56', 'f1');
insert into SIZES (ID, NAME, VALUE, OWN) values (5, 'f', '-', 'f1');此时执行
select
max(decode(s.own,'f0',s.value,0)) "f0",
max(decode(s.own,'f1',s.value,0)) "f1"
from sizes s group by s.id order by s.id
显示结果为:
f0 f1
--------
0 0
23 45
0 0
1231 56
0 -
为什么结果不是下面这样呢。。,可是把类似----换成其他符号可以呢,比如“|| \ | ! ”呢
f0 f1
--------
---- -
23 45
- -
1231 56
0 -
但是在表中在添加一条记录insert into SIZES (ID, NAME, VALUE, OWN) values (5, 'f', '-', 'f0');时,
原来查询结果中的0 - 也变成了 0 0 呢?大家遇到过这样的情况吗? 请教了,谢谢
max(decode(s.own,'f0',s.value,null)) "f0",
max(decode(s.own,'f1',s.value,null)) "f1"
from sizes s group by s.id order by s.id
SQL> edi
已写入 file afiedt.buf 1 select id,
2 max(decode(s.own,'f0',s.value,null)) f0,
3 max(decode(s.own,'f1',s.value,null)) f1
4 from sizes s
5 group by s.id
6* order by s.id
SQL> / ID F0 F1
---------- ---------- ----------
1 ---- -
2 23 45
3 - -
4 1231 56
5 -
所以取max的时候会选'0'.如果换成其他符号,如'|',由于'|'的ASCII码是124,比'0'要大。
所以取MAX的时候会取'|'.