怎样去掉重复的记录

经整理，现已经发现几种可以做到"去掉listNO 字段重复的记录,并且只留重复中的
readtime时间最新的那一条,而且command226要等于C" 的方法，共三种：

 

use winccsql

1：
select * from MainPic_view_eq m
where not exists(select * from MainPic_view_eq where  listNO_1=m.listNO_1 and readtime>m.readtime)and command226='c'
order by readtime

2：
select * from MainPic_view_eq a
where readtime = (select max(readtime) from MainPic_view_eq where listNO_1 = a.listNO_1)
and command226='c'
order by readtime

3：
select * from MainPic_view_eq a
where a.readtime=(select max(readtime) from MainPic_view_eq b where b.listNO_1=a.listNO_1 and b.command226='c')
order by readtime

以下为CSDN回复----------------------------------------------------------------------------------------------

 表MainPic:

path  readtime                  listNO        command226
1     2007-04-03 15:18:22.000   PONU8207601      c
1     2007-04-03 15:18:22.000   PONU8207601      p
2     2007-04-03 15:19:01.000   PONU8207601      c
2     2007-04-03 15:30:14.000   CCLU6587690      c
1     2007-04-03 15:31:21.000   CCLU6587690      c
3     2007-04-03 16:18:49.000   ECMU9644320      c

从数据库里取数据时,怎样去掉listNO 字段重复的记录,并且只留重复中的
readtime时间最新的那一条,而且command226要等于C

path  readtime                  listNO        command226
2     2007-04-03 15:19:01.000   PONU8207601      c
1     2007-04-03 15:31:21.000   CCLU6587690      c
3     2007-04-03 16:18:49.000   ECMU9644320      c

roy_88(中国风_燃烧你的激情！！！) ( ) 信誉：100    Blog   加为好友

2007-4-25 17:45:42

得分: 12

select * from MainPic m
where not exists(select 1 from MainPic where listNO=m.listNO and readtime>m.readtime)

gahade(与君共勉) ( ) 信誉：100    Blog   加为好友

2007-4-25 17:47:32

得分: 8

create table MainPic(path int,readtime datetime,listNO varchar(20),command226 char(1))
insert into MainPic
select 1,'2007-04-03 15:18:22.000','PONU8207601','c'
union all select 1,'2007-04-03 15:18:22.000','PONU8207601','p'
union all select 2,'2007-04-03 15:19:01.000','PONU8207601','c'
union all select 2,'2007-04-03 15:30:14.000','CCLU6587690','c'
union all select 1,'2007-04-03 15:31:21.000','CCLU6587690','c'
union all select 3,'2007-04-03 16:18:49.000','ECMU9644320','c'

select * from MainPic a
where a.readtime=(select max(readtime) from MainPic b where b.command226='c' and a.listNO=b.listNO)
/*
path        readtime                                               listNO               command226
----------- ------------------------------------------------------ -------------------- ----------
2           2007-04-03 15:19:01.000                                PONU8207601          c
1           2007-04-03 15:31:21.000                                CCLU6587690          c
3           2007-04-03 16:18:49.000                                ECMU9644320          c

（所影响的行数为 3 行）
*/

roy_88(中国风_燃烧你的激情！！！) ( ) 信誉：100    Blog   加为好友

2007-4-25 17:47:42

加条件取法
--方法1
select n.*
from MainPic n
join
(select listNO,readtime=max(readtime)
from MainPic where command226='c'--取条件为C
group by listNO)m
on n.listNO=m.listNO and n.readtime=m.readtime
--方法2
select * from MainPic m
where command226='c'
and
not exists(select 1 from MainPic where listNO=m.listNO and command226=m.command226
and readtime>m.readtime)

fanmb(我要努力) ( ) 信誉：100    Blog   加为好友

2007-4-26 8:52:35

select max(readtime) as readtime,listno,'c' as command 226
  from
 (select readtime,listno,command226 from mainpic where command226 = 'c' )
 group by listno