try: Select a.* from 表 a join (select VNumber,Max(SubmitTime) as SubmitTime from 表 group by VNumber) b on a.VNumber = b.VNumber and a.SubmitTime = b.SubmitTime join (select VNumber,SubmitTime,Max(taskID) as taskID from 表 group by VNumber,SubmitTime) c on a.VNumber = c.VNumber and a.SubmitTime = c.SubmitTime and a.taskID = c.taskID join (select VNumber,submitTime,taskID,Max(EndTime) as EndTime from 表 group by VNumber,submitTime,taskID) d on a.VNumber = d.VNumber and a.SubmitTime = d.SubmitTime and a.taskID = d.taskID and a.EndTime = d.EndTime where a.submitTime<'2002.10.11' and a.BookID = 40
select * from tableA A where SubmitTime<'2002.10.11' and BookID =40 and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime>A.SubmitTime )and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID>A.taskID )and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID=A.taskID and EditTime>A.EditTime )
select * from a where SubmitTime<2002.10.11 and BookID =40 group by VNumber,SubmitTime,taskID,EditTime order by EditTime,taskID,SubmitTime,VNumber desc
select * from tableA A where SubmitTime<'2002.10.11' and BookID =40 and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime>A.SubmitTime and bookid=A.bookid )and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID>A.taskID and bookid=A.bookid )and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID=A.taskID and EditTime>A.EditTime and bookid=A.bookid )
select * from tableA A where SubmitTime<'2002.10.11' and BookID =40 and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime>A.SubmitTime and bookid=A.bookid and SubmitTime<'2002.10.11' )and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID>A.taskID and bookid=A.bookid and SubmitTime<'2002.10.11' )and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID=A.taskID and EditTime>A.EditTime and bookid=A.bookid and SubmitTime<'2002.10.11' )
这样就应该可以了。Select a.* from 表 a join (select VNumber,submitTime,taskID,Max(EndTime) as EndTime from 表 group by VNumber,submitTime,taskID) d on a.VNumber = d.VNumber and a.SubmitTime = d.SubmitTime and a.taskID = d.taskID and a.EndTime = d.EndTime where a.submitTime<'2002.10.11' and a.BookID = 40
如果EditTime也相同呢?取MAX(VID) select * from tableA A where SubmitTime<'2002.10.11' and BookID =40 and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime>A.SubmitTime and bookid=A.bookid and SubmitTime<'2002.10.11' )and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID>A.taskID and bookid=A.bookid and SubmitTime<'2002.10.11' )and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID=A.taskID and EditTime>A.EditTime and bookid=A.bookid and SubmitTime<'2002.10.11' ) and not exists ( select 1 from tableA where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID=A.taskID and EditTime=A.EditTime and VID>A.VID and bookid=A.bookid and SubmitTime<'2002.10.11' )
同意txlicenhe(马可) !!!
VID is PK:select * from tableA where vid= (select top 1 vid where SubmitTime<'2002.10.11' and BookID =40 order by VNumber desc,SubmitTime desc,taskID desc,EditTime desc)
VID is PK: or try :select * from tableA where vid in (select max(vid) vid where SubmitTime<'2002.10.11' and BookID =40 group by by VNumber desc,SubmitTime desc,taskID desc,EditTime desc)
To: j9988(j9988) ( ) VID 是 guid.txlicenhe(马可) 也不正确阿teaism() 要下班了,明天试一下,再回复。 ----------------------------------大概我的描述有混淆,我希望我的描述大家可以理解: 1. SubmitTime<2002.10.11 2. BookID =40 所有记录里: 这些记录可能会有重复的VNumber,而我希望如果VNumber有重复,就取一个。 取得条件依次判断submittime,taskid,edittime。 比如:如果Vnumber相同,判断submittime,如果vnumber,submittime都相同,就继续判断taskid,……
我做错了,对不起。下午没细想,顺着马可的思路就出来了。 同意teaism的。
declare @a table(VID int identity, VNumber int, SubmitTime datetime, taskID int, EditTime datetime, BookID int) insert @a select 1,'2002-01-02',1,'2002-01-01',40 union all select 1,'2002-01-01',2,'2002-01-02',40 union all select 1,'2002-01-02',3,'2002-01-03',40 union all select 1,'2002-01-02',3,'2002-01-04',40 union all select 1,'2002-01-02',4,'2002-01-04',40 union all select 1,'2002-01-02',4,'2002-01-05',40 union all select 1,'2002-01-02',4,'2002-01-06',40 union all select 1,'2002-01-02',4,'2002-01-05',40 union all select 1,'2002-01-02',4,'2002-01-08',40 union all select 2,'2002-01-03',1,'2002-01-01',40 union all select 2,'2002-01-03',1,'2002-01-01',40 union all select 3,'2002-01-03',1,'2002-01-03',40 union all select 3,'2002-01-03',2,'2002-01-01',40 insert @a select 1,'2002-11-02',1,'2002-01-01',40 union all select 1,'2002-11-01',2,'2002-01-02',40 union all select 1,'2002-11-02',3,'2002-01-03',40 union all select 1,'2002-11-02',3,'2002-01-04',40 union all select 1,'2002-11-02',4,'2002-01-04',40 union all select 1,'2002-11-02',4,'2002-01-05',40 union all select 1,'2002-11-02',4,'2002-01-05',40 union all select 1,'2002-11-02',4,'2002-01-05',40 union all select 1,'2002-11-02',4,'2002-01-05',40 union all select 2,'2002-11-03',1,'2002-01-01',40 union all select 2,'2002-11-03',1,'2002-01-01',40 union all select 3,'2002-11-03',1,'2002-01-01',40 union all select 3,'2002-11-03',1,'2002-01-01',40 select * from @a A where SubmitTime<'2002.10.11' and BookID =40 and not exists ( select 1 from @a where (case when VNumber=A.VNumber then (case when SubmitTime>A.SubmitTime then 1 when SubmitTime=A.SubmitTime then (case when taskID>A.taskID then 1 when taskID=A.taskID then (case when EditTime>A.EditTime then 1 when EditTime=A.EditTime then (case when VID>A.VID then 1 else 0 end) else 0 end) else 0 end) else 0 end) else 0 end)=1 and bookid=A.bookid and SubmitTime<'2002.10.11') select * from @a A where SubmitTime<'2002.10.11' and BookID =40 and not exists ( select 1 from @a where VNumber=A.VNumber and SubmitTime>A.SubmitTime and bookid=A.bookid and SubmitTime<'2002.10.11' )and not exists ( select 1 from @a where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID>A.taskID and bookid=A.bookid and SubmitTime<'2002.10.11' )and not exists ( select 1 from @a where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID=A.taskID and EditTime>A.EditTime and bookid=A.bookid and SubmitTime<'2002.10.11' )and not exists ( select 1 from @a where VNumber=A.VNumber and SubmitTime=A.SubmitTime and taskID=A.taskID and EditTime=A.EditTime and VID>A.VID and bookid=A.bookid and SubmitTime<'2002.10.11' )
上面两句的结果是一样的,但第一句用CASE效率要高。
--第一句用CASE效率要高,想不出更好办法了。select * from @a A where SubmitTime<'2002.10.11' and BookID =40 and not exists ( select 1 from @a where (case when VNumber=A.VNumber then (case when SubmitTime>A.SubmitTime then 1 when SubmitTime=A.SubmitTime then (case when taskID>A.taskID then 1 when taskID=A.taskID then (case when EditTime>A.EditTime then 1 when EditTime=A.EditTime then (case when VID>A.VID then 1 else 0 end) else 0 end) else 0 end) else 0 end) else 0 end)=1 and bookid=A.bookid and SubmitTime<'2002.10.11') VID VNumber SubmitTime taskID EditTime BookID ----------- ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ ----------- 9 1 2002-01-02 00:00:00.000 4 2002-01-08 00:00:00.000 40 11 2 2002-01-03 00:00:00.000 1 2002-01-01 00:00:00.000 40 13 3 2002-01-03 00:00:00.000 2 2002-01-01 00:00:00.000 40
Select a.* from 表 a
join (select VNumber,Max(SubmitTime) as SubmitTime from 表 group by VNumber) b
on a.VNumber = b.VNumber and a.SubmitTime = b.SubmitTime
join (select VNumber,SubmitTime,Max(taskID) as taskID from 表 group by VNumber,SubmitTime) c
on a.VNumber = c.VNumber and a.SubmitTime = c.SubmitTime and a.taskID = c.taskID
join (select VNumber,submitTime,taskID,Max(EndTime) as EndTime from 表 group by VNumber,submitTime,taskID) d
on a.VNumber = d.VNumber and a.SubmitTime = d.SubmitTime and a.taskID = d.taskID and a.EndTime = d.EndTime
where a.submitTime<'2002.10.11'
and a.BookID = 40
and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime>A.SubmitTime
)and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID>A.taskID
)and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID=A.taskID
and EditTime>A.EditTime
)
where SubmitTime<2002.10.11 and BookID =40
group by VNumber,SubmitTime,taskID,EditTime
order by EditTime,taskID,SubmitTime,VNumber desc
and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime>A.SubmitTime
and bookid=A.bookid
)and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID>A.taskID
and bookid=A.bookid
)and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID=A.taskID
and EditTime>A.EditTime
and bookid=A.bookid
)
and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime>A.SubmitTime
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID>A.taskID
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID=A.taskID
and EditTime>A.EditTime
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)
我解释一下:如果EditTime也相同呢?
--这个应该不会出现。
你需要的结果集应该出现几条记录?
--结果因该很多。简单的说就在满足:
1. SubmitTime<2002.10.11
2. BookID =40
所有记录里,取得那些VNumber都是唯一的记录。如何取得就是要判断submittime,taskid,edittime 3个条件。
join (select VNumber,submitTime,taskID,Max(EndTime) as EndTime from 表 group by VNumber,submitTime,taskID) d
on a.VNumber = d.VNumber and a.SubmitTime = d.SubmitTime and a.taskID = d.taskID and a.EndTime = d.EndTime
where a.submitTime<'2002.10.11'
and a.BookID = 40
select * from tableA A where SubmitTime<'2002.10.11' and BookID =40
and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime>A.SubmitTime
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID>A.taskID
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID=A.taskID
and EditTime>A.EditTime
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)
and not exists
(
select 1 from tableA
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID=A.taskID
and EditTime=A.EditTime
and VID>A.VID
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)
(select top 1 vid where SubmitTime<'2002.10.11' and BookID =40
order by VNumber desc,SubmitTime desc,taskID desc,EditTime desc)
(select max(vid) vid where SubmitTime<'2002.10.11' and BookID =40
group by by VNumber desc,SubmitTime desc,taskID desc,EditTime desc)
VID 是 guid.txlicenhe(马可)
也不正确阿teaism()
要下班了,明天试一下,再回复。
----------------------------------大概我的描述有混淆,我希望我的描述大家可以理解:
1. SubmitTime<2002.10.11
2. BookID =40
所有记录里:
这些记录可能会有重复的VNumber,而我希望如果VNumber有重复,就取一个。
取得条件依次判断submittime,taskid,edittime。
比如:如果Vnumber相同,判断submittime,如果vnumber,submittime都相同,就继续判断taskid,……
同意teaism的。
VNumber int,
SubmitTime datetime,
taskID int,
EditTime datetime,
BookID int)
insert @a select 1,'2002-01-02',1,'2002-01-01',40
union all select 1,'2002-01-01',2,'2002-01-02',40
union all select 1,'2002-01-02',3,'2002-01-03',40
union all select 1,'2002-01-02',3,'2002-01-04',40
union all select 1,'2002-01-02',4,'2002-01-04',40
union all select 1,'2002-01-02',4,'2002-01-05',40
union all select 1,'2002-01-02',4,'2002-01-06',40
union all select 1,'2002-01-02',4,'2002-01-05',40
union all select 1,'2002-01-02',4,'2002-01-08',40
union all select 2,'2002-01-03',1,'2002-01-01',40
union all select 2,'2002-01-03',1,'2002-01-01',40
union all select 3,'2002-01-03',1,'2002-01-03',40
union all select 3,'2002-01-03',2,'2002-01-01',40 insert @a select 1,'2002-11-02',1,'2002-01-01',40
union all select 1,'2002-11-01',2,'2002-01-02',40
union all select 1,'2002-11-02',3,'2002-01-03',40
union all select 1,'2002-11-02',3,'2002-01-04',40
union all select 1,'2002-11-02',4,'2002-01-04',40
union all select 1,'2002-11-02',4,'2002-01-05',40
union all select 1,'2002-11-02',4,'2002-01-05',40
union all select 1,'2002-11-02',4,'2002-01-05',40
union all select 1,'2002-11-02',4,'2002-01-05',40
union all select 2,'2002-11-03',1,'2002-01-01',40
union all select 2,'2002-11-03',1,'2002-01-01',40
union all select 3,'2002-11-03',1,'2002-01-01',40
union all select 3,'2002-11-03',1,'2002-01-01',40 select * from @a A where SubmitTime<'2002.10.11' and BookID =40
and not exists
(
select 1 from @a
where (case when VNumber=A.VNumber then
(case when SubmitTime>A.SubmitTime then 1
when SubmitTime=A.SubmitTime then
(case when taskID>A.taskID then 1
when taskID=A.taskID then
(case when EditTime>A.EditTime then 1
when EditTime=A.EditTime then
(case when VID>A.VID then 1
else 0 end)
else 0 end)
else 0 end)
else 0 end)
else 0 end)=1
and bookid=A.bookid
and SubmitTime<'2002.10.11')
select * from @a A where SubmitTime<'2002.10.11' and BookID =40
and not exists
(
select 1 from @a
where VNumber=A.VNumber
and SubmitTime>A.SubmitTime
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)and not exists
(
select 1 from @a
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID>A.taskID
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)and not exists
(
select 1 from @a
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID=A.taskID
and EditTime>A.EditTime
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)and not exists
(
select 1 from @a
where VNumber=A.VNumber
and SubmitTime=A.SubmitTime
and taskID=A.taskID
and EditTime=A.EditTime
and VID>A.VID
and bookid=A.bookid
and SubmitTime<'2002.10.11'
)
and not exists
(
select 1 from @a
where (case when VNumber=A.VNumber then
(case when SubmitTime>A.SubmitTime then 1
when SubmitTime=A.SubmitTime then
(case when taskID>A.taskID then 1
when taskID=A.taskID then
(case when EditTime>A.EditTime then 1
when EditTime=A.EditTime then
(case when VID>A.VID then 1
else 0 end)
else 0 end)
else 0 end)
else 0 end)
else 0 end)=1
and bookid=A.bookid
and SubmitTime<'2002.10.11')
VID VNumber SubmitTime taskID EditTime BookID
----------- ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ -----------
9 1 2002-01-02 00:00:00.000 4 2002-01-08 00:00:00.000 40
11 2 2002-01-03 00:00:00.000 1 2002-01-01 00:00:00.000 40
13 3 2002-01-03 00:00:00.000 2 2002-01-01 00:00:00.000 40
teaism()给的store procedure解决了问题。