主查询: select A, B, Count(C) from [subquery] group by A,B having Count(C)=1子查询:(把肯定不符合条件的记录去条) select A, B, C, D from table where D>A and D>B and D>C and C<>0将子查询代入主查询就可以了: select A, B, Count(C) from (select A, B, C, D from table where D>A and D>B and D>C and C<>0) group by A,B having Count(C)=1
自顶一下,现在前一个问题已经解决了,但后一个“确保每个AIC064不为0时,该记录的年度必为同人同险种的记录中的年度的最大值”还没有解决,写了个sql但效率太低。select * from ic02 a,(select aac001,aae140 from ic02 group by aac001,aae140) b where (a.aac001=b.aac001 and a.aae140=b.aae140 and a.aic064 <> 0 and a.aae001 < (select max(aae001) from ic02 c where a.aac001=c.aac001 and a.aae140=c.aae140))
select A, B, Count(C) from [subquery]
group by A,B
having Count(C)=1子查询:(把肯定不符合条件的记录去条)
select A, B, C, D from table
where D>A
and D>B
and D>C
and C<>0将子查询代入主查询就可以了:
select A, B, Count(C) from
(select A, B, C, D from table
where D>A
and D>B
and D>C
and C<>0)
group by A,B
having Count(C)=1
------ ------------ -------- ------- ----------------------------------------
AAC001 VARCHAR2(10) 个人编号
AAE001 NUMBER(4) 年度
AAE140 VARCHAR2(3) 险种类型
AIC064 VARCHAR2(3) 个人账户状态 对每个人参加的每个险种(AAC001-AAE140),由于年度不同会有多条记录,但只能有一条记录的AIC064<>0,且此条记录的年度必须为同人同险种的最大值现在要找出不符合这个要求的所有数据不知道这次我说清楚了没有?
创建表
create table TT
(
AAC001 VARCHAR2(6),
AAE140 VARCHAR2(3),
AIC064 VARCHAR2(3),
AAE001 VARCHAR2(4)
)
插入数据.
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('11111', '11', '0', '2005');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('11111', '11', '0', '2004');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('11111', '11', '0', '2003');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('11111', '11', '1', '2002');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('22222', '22', '1', '2006');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('22222', '22', '0', '2005');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('11111', '11', '1', '2006');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('22222', '22', '1', '2004');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('11111', '22', '1', '2006');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('11111', '22', '0', '2005');
insert into TT (AAC001, AAE140, AIC064, AAE001)
values ('11111', '22', '0', '2004');
commit;
查询数据
AAC001 AAE140 AIC064 AAE001
------ ------ ------ ------
11111 11 1 2002
11111 11 0 2003
11111 11 0 2004
11111 11 0 2005
11111 11 1 2006
11111 22 0 2004
11111 22 0 2005
11111 22 1 2006
22222 22 1 2004
22222 22 0 2005
22222 22 1 2006执行语句:
select b.* from
(select aic064,aac001 ,aae140, nvl(AAE001,0000) bb
from tt where
tt.aic064='1' )
b,
(select * from tt a
where a.aae001=(select max(tt.aae001) from tt
where tt.aac001=a.aac001
and tt.aae140=a.aae140))
c
where b.aac001=c.aac001 and b.aae140=c.aae140 and b.bb<c.aae001
执行结果:::
AIC064 AAC001 AAE140 BB
------ ------ ------ ----
1 11111 11 2002
1 22222 22 2004这两条记录不符合条件.