经整理,现已经发现几种可以做到"去掉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