是的,通过单一的SQL是取不出来的..RepeatReturnTimes,LastsDate是通过函数取出来的其中传去函数为ID.
像这样:
select sDate,SN,dbo.FtRepeatReturnTimes(ID)as RepeatReturnTimes,dbo.FtLastsDate(ID) as LastsDate,ID from RMADetail where sn = 'V000010630' order by sDate
我就是取出的RepeatReturnTimes,LastsDate不对,不知道如何取出来????
像这样:
select sDate,SN,dbo.FtRepeatReturnTimes(ID)as RepeatReturnTimes,dbo.FtLastsDate(ID) as LastsDate,ID from RMADetail where sn = 'V000010630' order by sDate
我就是取出的RepeatReturnTimes,LastsDate不对,不知道如何取出来????
这个是用来计算重复次数的函数吧？既然ID是主键，那就不能重复，那你这个函数怎么算重复次数？
dbo.FtRepeatReturnTimes函数应该将'V000010630'作为参数,怎么写这个函数???
insert into @t
select '2005-01-03','V000010630','12' union all
select '2005-08-01','V000010630 ','13' union all
select '2005-08-01','V000010630 ','14' union all
select '2005-10-01','V000010630 ','15' union all
select '2005-10-01','V000010630 ','16' union all
select '2005-10-01','V000010630 ','17' union all
select '2005-12-01','V000010630 ','18' union all
select '2005-12-01','V000010630 ','19' union all
select '2005-12-01','V000010630 ','20' union all
select '2005-12-01','V000010630 ','21'select identity(int,1,1) as line,* into # from (
select sDate ,SN,count(*)-1 as RepeatReturnTimes
from @t group by sDate,SN having count(1)>1
union
select sDate ,SN,count(*)-1 as RepeatReturnTimes
from @t group by sDate,SN having count(1)<=1) tselect sDate,SN,RepeatReturnTimes,LastsDate=case RepeatReturnTimes
when 0 then null else (select sDate from # t1 where t1.line=t2.line-1)
end,line as ID from # t2
--select * from #
drop table #
----------------------------------------------------
/*2005-01-03 00:00:00.000 V000010630 0 NULL 1
2005-08-01 00:00:00.000 V000010630 1 2005-01-03 00:00:00.000 2
2005-10-01 00:00:00.000 V000010630 2 2005-08-01 00:00:00.000 3
2005-12-01 00:00:00.000 V000010630 3 2005-10-01 00:00:00.000 4
*/
create table TestTable(id int,sDate datetime,sn varchar(20))
insert into TestTable
select 1,'2005-01-03 9:00:00.000','V000010628'union all
select 2,'2005-01-03 10:00:00.000','V000010630'union all
select 3,'2005-01-03 11:00:00.000','V000010631'union all
select 4,'2005-01-03 12:00:00.000','V000010630'union all
select 5,'2005-01-03 13:00:00.000','V000010630'union all
select 6,'2005-01-04 14:00:00.000','V000010627'union all
select 7,'2005-01-04 15:00:00.000','V000010626'union all
select 8,'2005-01-04 16:00:00.000','V000010630'union all
select 9,'2005-01-04 17:00:00.000','V000010630'union all
select 10,'2005-01-04 18:00:00.000','V000010630'union all
select 11,'2005-01-05 19:00:00.000','V000010621'union all
select 12,'2005-01-05 20:00:00.000','V000010622'union all
select 13,'2005-01-05 21:00:00.000','V000010630'union all
select 14,'2005-01-05 22:00:00.000','V000010623'union all
select 15,'2005-01-05 23:00:00.000','V000010630'
go--创建自定义函数
create function dbo.RepeatReturnTimesLastsDate(@sn varchar(20)
)
RETURNS @re TABLE(sDate datetime,sn varchar(20),RepeatReturnTimes int,LastsDate datetime,[ID] int)
AS
BEGIN
insert into @re
select t.*,u.id from(
select convert(char(10),sDate,120)as sDate,sn,
RepeatReturnTimes=count(sn),
LastsDate=max(sDate)
from TestTable
where sn = @sn
group by convert(char(10),sDate,120),sn
)t left join TestTable u
on t.LastsDate=u.sDate
return
END
go--执行语句
select * from dbo.RepeatReturnTimesLastsDate('V000010630')
--删除测试表和自定义函数
drop table TestTable
drop function dbo.RepeatReturnTimesLastsDate
/*
sDate sn RepeatReturnTimes LastsDate ID
------------------------------------------------------ -------------------- ----------------- ------------------------------------------------------ -----------
2005-01-03 00:00:00.000 V000010630 3 2005-01-03 13:00:00.000 5
2005-01-04 00:00:00.000 V000010630 3 2005-01-04 18:00:00.000 10
2005-01-05 00:00:00.000 V000010630 2 2005-01-05 23:00:00.000 15（所影响的行数为 3 行）
*/
------------------------------
select
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