34,838
社区成员




select *
from a t
where not exists(select 1
from a
where CUS_NO=t.CUS_NO and PRD_NO=t.PRD_NO and s_dd>t.S_DD)
-----看错字段了。晕死.
select * from a t where not exists(select 1 from a where CUS_NO=t.CUS_NO and PRD_NO=t.PRD_NO and s_dd>t.S_DD)
--或者
select * from a t where S_DD in(select max(S_DD) from a where CUS_NO=t.CUS_NO and PRD_NO=t.PRD_NO )
--或者
select * from a t where (select count(distinct S_DD) from a where CUS_NO=t.CUS_NO and PRD_NO=t.PRD_NO and s_dd>t.S_DD)=1
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 a.cus_no=b.cus_no and a.prd_no=b.prd_no and checksum(a.s_dd,a.up)>checksum(b.s_dd,b.up))
order by len(cus_no),cus_no
go
drop table A;
go
(11 行受影响)
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
A BAD001 2008-02-15 00:00:00.000 2.10
A1 BAD002 2008-01-21 00:00:00.000 2.20
A2 BAD002 2008-01-21 00:00:00.000 2.30
A2 BAD002 2008-01-26 00:00:00.000 2.20
A2 BAD002 2008-01-26 00:00:00.000 2.30
B BAD001 2008-01-11 00:00:00.000 2.20
B BAD001 2008-02-22 00:00:00.000 2.30
C BAD001 2008-02-21 00:00:00.000 2.20
C BAD001 2008-02-23 00:00:00.000 2.10
(11 行受影响)
cus_no prd_no s_dd up
-------- ---------- ----------------------- ---------------------------------------
A BAD001 2008-02-15 00:00:00.000 2.10
B BAD001 2008-02-22 00:00:00.000 2.30
C BAD001 2008-02-23 00:00:00.000 2.10
A1 BAD002 2008-01-21 00:00:00.000 2.20
A2 BAD002 2008-01-21 00:00:00.000 2.30
(5 行受影响)
select * from a a1
where a1.s_dd=(select max(a2.s_dd) from a a2 where a2.cus_no=a1.cus_no)
create table tb(CUS_NO CHAR(2),PRD_NO CHAR(6),S_DD DATETIME,UP DECIMAL(10,2))
INSERT INTO TB SELECT 'A' , 'BAD001' , '2007-12-01' , 2.0
INSERT INTO TB SELECT 'A' , 'BAD001' , '2008-01-21' , 2.2
INSERT INTO TB SELECT 'A' , 'BAD001' , '2008-02-15' , 2.1
INSERT INTO TB SELECT 'B' , 'BAD001' , '2008-01-11' , 2.2
INSERT INTO TB SELECT 'B' , 'BAD001' , '2008-02-22' , 2.3
INSERT INTO TB SELECT 'C' , 'BAD001' , '2008-02-21' , 2.2
INSERT INTO TB SELECT 'C' , 'BAD001' , '2008-02-23' , 2.1
INSERT INTO TB SELECT 'A1' , 'BAD002' , '2008-01-21' , 2.2
INSERT INTO TB SELECT 'A2' , 'BAD002' , '2008-01-21' , 2.3
INSERT INTO TB SELECT 'A2' , 'BAD002', '2008-01-26' , 2.3 --多增加一条记录
INSERT INTO TB SELECT 'A2' , 'BAD002', '2008-01-26' , 2.2
SELECT *
FROM tb A
WHERE NOT EXISTS(SELECT 1 FROM tb WHERE CUS_NO=A.CUS_NO AND S_DD>A.S_DD)
DROP TABLE tb
则存在问题