select a.* from DbTemp a, (select StudentID,max(id) as max_id from DbTemp where BelongMonth<='2008-3-1' group by StudentID) as b where a.StudentID=b.StudentID and a.id=b.max_id and a.IsReduce=0
select a.* from DbTemp a, (select StudentID,max(id) as max_id from DbTemp where BelongMonth<='2008-3-1' group by StudentID) as b where a.StudentID=b.StudentID and a.id=b.max_id and a.IsReduce=0
谢了! 解决了. 不过这中间的max(id)要改为max(BelongMonth). 下面做对应更改就行了. 呵呵 select a.* from DbTemp a, (select StudentID,max(BelongMonth) as max_BelongMonth from DbTemp where BelongMonth <='2008-7-1' group by StudentID) as b where a.StudentID=b.StudentID and a.BelongMonth=b.max_BelongMonth and a.IsReduce=0
create function F_Str(@m Datetime) returns nvarchar(100) as begin declare @S nvarchar(100) select @S=isnull(@S+',','')+cast([StudentID]as varchar(10)) from DbTemp where DATEPART(m,[BelongMonth]) <= DATEPART(m,@m) group by StudentID having sum(case IsReduce when 0 then 1 else -1 end)=1 return @S end select distinct convert(varchar(10),[BelongMonth],120), dbo.F_Str([BelongMonth]) from DbTemp/* 2008-03-01 1,2 2008-04-01 2 2008-05-01 1 2008-06-01 1 */
第一楼更正了最后一句SQL语句您可能没注意到.
(select StudentID,max(id) as max_id
from DbTemp
where BelongMonth<='2008-3-1'
group by StudentID) as b
where a.StudentID=b.StudentID and a.id=b.max_id and a.IsReduce=0
(select StudentID,max(id) as max_id
from DbTemp
where BelongMonth<='2008-3-1'
group by StudentID) as b
where a.StudentID=b.StudentID and a.id=b.max_id and a.IsReduce=0
谢了! 解决了. 不过这中间的max(id)要改为max(BelongMonth). 下面做对应更改就行了. 呵呵
select a.* from DbTemp a,
(select StudentID,max(BelongMonth) as max_BelongMonth
from DbTemp
where BelongMonth <='2008-7-1'
group by StudentID) as b
where a.StudentID=b.StudentID and a.BelongMonth=b.max_BelongMonth and a.IsReduce=0
create function F_Str(@m Datetime)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+cast([StudentID]as varchar(10)) from DbTemp
where DATEPART(m,[BelongMonth]) <= DATEPART(m,@m)
group by StudentID
having sum(case IsReduce when 0 then 1 else -1 end)=1
return @S
end select distinct convert(varchar(10),[BelongMonth],120), dbo.F_Str([BelongMonth])
from DbTemp/*
2008-03-01 1,2
2008-04-01 2
2008-05-01 1
2008-06-01 1
*/