有表如下:
id name
1 a
1 d
3 b
4 c
4 c
4 c
想得到如下的查询:
id name sign
1 a 1
1 d 1
3 b 0
4 c 0
4 c 0
4 c 0
即相同id下, 如果name的字段值都一样的话,该记录的标志字段sign值设为0,否则设为1.
id name
1 a
1 d
3 b
4 c
4 c
4 c
想得到如下的查询:
id name sign
1 a 1
1 d 1
3 b 0
4 c 0
4 c 0
4 c 0
即相同id下, 如果name的字段值都一样的话,该记录的标志字段sign值设为0,否则设为1.
insert into tt select 1,'a' from dual;
insert into tt select 1,'d' from dual;
insert into tt select 3,'b' from dual;
insert into tt select 4,'c' from dual;
insert into tt select 4,'c' from dual;
insert into tt select 4,'c' from dual;
commit;
select a.*,decode(sum(1) over (partition by id,name ),1,1,0) from tt a
insert into tt select 1,'a' from dual;
insert into tt select 1,'d' from dual;
insert into tt select 3,'b' from dual;
insert into tt select 4,'c' from dual;
insert into tt select 4,'c' from dual;
insert into tt select 4,'c' from dual;
commit;
select a.*,decode(sum(1) over (partition by id,name ),1,1,0) from tt a
insert into tt select 1,'a' from dual;
insert into tt select 1,'d' from dual;
insert into tt select 3,'b' from dual;
insert into tt select 4,'c' from dual;
insert into tt select 4,'c' from dual;
insert into tt select 4,'c' from dual;
commit;
select a.*,decode((select count(1) from tt b where a.id=b.id and a.rowid<>b.rowid),1,1,0) from tt a
decode(count(distinct name)over(partition by id),1,0,1)sign
from tt