ALTER FUNCTION [dbo].[GetHistory]
(
@IssueNum int,@Name varchar(100),@NumSum int,@Result varchar(50),@History varchar(100)
)
RETURNS varchar(1024)
AS
BEGIN
DECLARE @tempID int
select top 1 @tempID=IssueNum from NumsData where IssueNum<@IssueNum and [Name]=@Name and NumSum =@NumSum and Result=@Result and History=@History order by IssueNum desc
set @History=''
if @tempID>0
begin
select top 3 @History=@History+R1 from NumsData where IssueNum<=@tempID and [Name]=@Name order by IssueNum desc
end
return @History
END现在的返回值是@History=@History+R1 我需要得到的返回值是
select top 1 @tempID=IssueNum from NumsData where IssueNum<@IssueNum and [Name]=@Name and NumSum =@NumSum and Result=@Result and History=@History order by IssueNum desc
这条数据的IssueNum 后的三条数据的R1字段的值 比如上面top1得到的IssueNum 是15 那我要得到的是 16 17 18的这三条数据的R1字段的值 且name相同
因此最终的返回值@History=16的R1+17的R1+18的R1 R1不是数字
(
@IssueNum int,@Name varchar(100),@NumSum int,@Result varchar(50),@History varchar(100)
)
RETURNS varchar(1024)
AS
BEGIN
DECLARE @tempID int
select top 1 @tempID=IssueNum from NumsData where IssueNum<@IssueNum and [Name]=@Name and NumSum =@NumSum and Result=@Result and History=@History order by IssueNum desc
set @History=''
if @tempID>0
begin
select top 3 @History=@History+R1 from NumsData where IssueNum<=@tempID and [Name]=@Name order by IssueNum desc
end
return @History
END现在的返回值是@History=@History+R1 我需要得到的返回值是
select top 1 @tempID=IssueNum from NumsData where IssueNum<@IssueNum and [Name]=@Name and NumSum =@NumSum and Result=@Result and History=@History order by IssueNum desc
这条数据的IssueNum 后的三条数据的R1字段的值 比如上面top1得到的IssueNum 是15 那我要得到的是 16 17 18的这三条数据的R1字段的值 且name相同
因此最终的返回值@History=16的R1+17的R1+18的R1 R1不是数字
-- try
ALTER FUNCTION [dbo].[GetHistory](
@IssueNum int,
@Name varchar(100),
@NumSum int,
@Result varchar(50),
@History varchar(100)
)
RETURNS varchar(1024)
AS
BEGIN
DECLARE @tempID int,
@retValue varchar(100)
;with t as (
select row_number() over (order by IssueNum desc ) as Row, IssueNum ,R1
from NumsData
where IssueNum<@IssueNum and [Name]=@Name and NumSum =@NumSum and Result=@Result and History=@History
order by IssueNum desc
)
select @retValue=@retValue+R1
from t where row >=2 and Row <=4
return @retValue
END
select top 1 @tempID=IssueNum from NumsData where IssueNum<@IssueNum and [Name]=@Name and NumSum =@NumSum and Result=@Result and History=@History order by IssueNum desc
搜索得到编号issuenum
修改这个搜索条件
select top 3 @History=@History+R1 from NumsData where IssueNum<=@tempID and [Name]=@Name order by IssueNum desc
需要得到的最终的返回值应该是编号issuenum接连三条数据的R1字段的值 比如得编号issuenum是15 那就是要取16,17,18的R1字段的值做为返回值
(
@IssueNum int,@Name varchar(100),@NumSum int,@Result varchar(50),@History varchar(100)
)
RETURNS varchar(1024)
AS
BEGIN
DECLARE @tempID int
select top 1 @tempID=IssueNum from NumsData where IssueNum<@IssueNum and [Name]=@Name and NumSum =@NumSum and Result=@Result and History=@History order by IssueNum desc
set @History=''
if @tempID>0
begin
select @History=@History+R1 FROM (SELECT TOP 3 R1 FROM NumsData where IssueNum>@tempID and [Name]=@Name order by IssueNum) T
--select top 3 @History=@History+R1 from NumsData where IssueNum<=@tempID and [Name]=@Name order by IssueNum desc
end
return @History
END
这个15只是找出来的以前的一条数据是15 和他对比的这条数据可能90也可能是100
根据条件找出来符合条件的数据的编号是15 后面当然就会有16 17 18
select top 3 @History=@History+R1 from NumsData where IssueNum<=@tempID and [Name]=@Name order by IssueNum desc你这条语句能找比15大的?
因此最终的返回值@History=16的R1+17的R1+18的R1 R1不是数字红色部分,得到 16 17 18 就这三个具体值而言是哪个字段的? IssueNum ?
如果真的是 IssueNum 这个字段,那后边不能写为 IssueNum <= @tempID 这不是找后边三条数据,是找对应字段符合条件的。
我也知道 IssueNum <= @tempID 这不是找后边三条数据 就是不知道该怎么写去找后面三条数据的语句啊