并且算出先前的那一次时间LastsDate
------------------------------------
实现这点比较难 ,其他的可以这样写:select sDate,SN,RepeatReturnTimes=count(*) ,LastsDate=max(FtLastsDate),ID
from RMADetail t
where sn = 'V000010630'
group by sDate
order by sDate
--其中LastsDate的写法是错的,再想下看
------------------------------------
实现这点比较难 ,其他的可以这样写:select sDate,SN,RepeatReturnTimes=count(*) ,LastsDate=max(FtLastsDate),ID
from RMADetail t
where sn = 'V000010630'
group by sDate
order by sDate
--其中LastsDate的写法是错的,再想下看
create function f_LastsDate(
@id1 int)returns varchar(20)
as
begin
declare @a varchar(20)
declare @d varchar(20)
declare @b int
--下面这句调用了你的自定义函数,用于判断是否需要获得LastsDate,因为当重复次数为0时,时间不显示
select @a=sDate,@b=dbo.FtRepeatReturnTimes(@id1) from RMADetail where id=@id1
if @b<>0
--算法:比当前时间小,但最接近当前时间的那个时间值(也是最大的那个),
--即为先前那一次时间
select @d=max(sDate) from RMADetail where sDate<@a
else
set @d=''
return(@d)
end然后写SQL语句:
select sDate,SN,RepeatReturnTimes=dbo.FtRepeatReturnTim(ID),
LastsDate=dbo.f_LastsDate(ID),ID
from RMADetail t
where sn = 'V000010630'
order by sDate
是的因为我写了半天都没写出来..你能写个函数给我吗??
感谢lsqkeke(可可),我去试试.
到底你原来写的函数dbo.FtRepeatReturnTim(ID)能算出重复次数吗?
a.sDate,
a.SN,
RepeatReturnTimes =cout(b.*),
LastsDate=max(b.sdate),
a.ID
from
RMADetail a
left join RmaDetail b on a.sn=b.sn and a.sdate >b.sdate
where
a.sn = 'V000010630'
group by
a.sn,a.sdate,a.id
order by
a.sDate