sky_yang_sky 发表于 2008-4-25 14:24

怎么消除重复值

以上三个栏位分别为fdate,fscstockid,fitemid,现在要实现如果fscstockid,fitemid两个栏位的数据没有重复,就取出来,如果是重复的,就取时间最近的一笔,请问怎么实现?
2008-02-20 00:00:00.000        130        512
2008-01-20 00:00:00.000        130        568
2008-01-01 00:00:00.000        132        1040
2008-01-07 00:00:00.000        132        1040
2008-01-09 00:00:00.000        132        1040
2008-01-10 00:00:00.000        132        1040
2008-01-07 00:00:00.000        132        1042
2008-01-09 00:00:00.000        132        1042
2008-01-10 00:00:00.000        132        1042
要成为如下
2008-02-20 00:00:00.000        130        512
2008-01-20 00:00:00.000        130        568
2008-01-10 00:00:00.000        132        1040
2008-01-10 00:00:00.000        132        1042

shezhenhui1989 发表于 2008-4-25 14:44

DISTINCT可以消除重复行

lff642 发表于 2008-4-25 14:48

试一下

create table tb(fdate datetime,fscstockid int,fitemid int)
insert into tb select '2008-02-20 00:00:00.000',130,512
union all select '2008-01-20 00:00:00.000',130,568
union all select '2008-01-01 00:00:00.000',132,1040
union all select '2008-01-07 00:00:00.000',132,1040
union all select '2008-01-09 00:00:00.000',132,1040
union all select '2008-01-10 00:00:00.000',132,1040
union all select '2008-01-07 00:00:00.000',132,1042
union all select '2008-01-09 00:00:00.000',132,1042
union all select '2008-01-10 00:00:00.000',132,1042

select * from tb

select A.* from tb A where not exists(select 1 from tb where fscstockid=A.fscstockid and fitemid=A.fitemid and fdate > A.fdate)

页: [1]

编程论坛