求助，高手帮帮忙。

Question

有如下表：A,   
CUS_NO            PRD_NO                S_DD                    UP   
======           =======            ==========                 =====   
A                BAD001             2007-12-01                  2.0   
A                BAD001             2008-01-21                  2.2   
A                BAD001             2008-02-15                  2.1   
B                BAD001             2008-01-11                  2.2   
B                BAD001             2008-02-22                  2.3   
C                BAD001             2008-02-21                  2.2   
C                BAD001             2008-02-23                  2.1   
A1               BAD002             2008-01-21                  2.2   
A2               BAD002             2008-01-21                  2.3   
A2               BAD002             2008-01-26                  2.2   
...              ...                   ...                      ...   
希望查询的结果如下：
A                BAD001             2007-12-01                  2.0     
A                BAD001             2008-01-21                  2.2   
B                BAD001             2008-01-11                  2.2 
C                BAD001             2008-02-21                  2.2 
A2               BAD002             2008-01-21                  2.3 

Answer 1

---这个可以了啊。。select * from ta a where  exists
(select 1 from ta where CUS_NO=a.CUS_NO and PRD_NO=a.PRD_NO and S_DD>a.S_DD)

Answer 2

create table tA(CUS_NO varchar(8),PRD_NO varchar(10),S_DD datetime,UP decimal(9,2))
go
insert into tA
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
goselect * from ta a where  exists
(select 1 from ta where CUS_NO=a.CUS_NO and PRD_NO=a.PRD_NO and S_DD>a.S_DD)

Answer 3

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_nogo
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 行）
*/

Answer 4

happyflagstone 兄：
我用你的语句确实有问题。