create table A(CUS_NO varchar(8),PRD_NO varchar(10),S_DD datetime,UP decimal(9,2))
go
insert into A
select 'A','BAD001','2007-12-01',2.0
union select 'A','BAD001','2008-01-21',2.2
union select 'A','BAD001','2008-02-15',2.1
union select 'B','BAD001','2008-01-11',2.2
union select 'B','BAD001','2008-02-22',2.3
union select 'C','BAD001','2008-02-21',2.2
union select 'C','BAD001','2008-02-23',2.1
union select 'A1','BAD002','2008-01-21',2.2
union select 'A2','BAD002','2008-01-21',2.3
union select 'A2','BAD002','2008-01-26',2.3
union select 'A2','BAD002','2008-01-26',2.2
go
select * from A
select cus_no,prd_no,s_dd,up
from A
where not exists(select *
from A b
where datepart(yy,a.S_DD)= datepart(yy,b.S_DD) and a.cus_no=b.cus_no and a.prd_no=b.prd_no and a.s_dd > b.s_dd )
and cus_no in (select cus_no from A group by cus_no having count(1) > 1)
order by len(cus_no),cus_no
go
drop table A;
go
/*
cus_no prd_no s_dd up
-------- ---------- ------------------------------------------------------ -----------
A BAD001 2007-12-01 00:00:00.000 2.00
A BAD001 2008-01-21 00:00:00.000 2.20
B BAD001 2008-01-11 00:00:00.000 2.20
C BAD001 2008-02-21 00:00:00.000 2.20
A2 BAD002 2008-01-21 00:00:00.000 2.30
（所影响的行数为 5 行）
*/
