首先看图片:
相同的单号:A0001 不同的Flag组 1和2
---下面是游标(其实游标就和我们写代码差不多,它主要像我们循环dataTable,在进行判断差不多)
-- select * from FX_Get_O_OrderContent_Flag_OrderData('A0001')
ALTER function [dbo].[FX_Get_O_OrderContent_Flag_OrderData]
(
@OrderNO nvarchar(20)
)
returns @OrderContentOrdrNODatat TABLE
(
[OrderNo] nvarchar(20),
[Content] nvarchar(1000),
[Flag] int
)
as
begin
DECLARE @ContentString nvarchar(1000)
set @ContentString=''
DECLARE @Content nvarchar(20) --创建变量
DECLARE @TypeEnglish nvarchar(1000) --创建变量
DECLARE @Percentage float --创建变量
DECLARE @Flag int
DECLARE @NFlag int
DECLARE myCursor CURSOR FOR select distinct [Flag] from O_OrderContent where [OrderNo]= @OrderNO ---读取外层游标
open myCursor --打开游标
fetch next from myCursor into @Flag --读取外层游标
WHILE @@FETCH_STATUS=0
BEGIN
Declare MyInsideCursor CURSOR FOR select [Percentage],[Content],[TypeEnglish],[Flag] from O_OrderContent where [OrderNo]= @OrderNO --声明内层游标
open MyInsideCursor --打开内层游标
fetch next from MyInsideCursor into @Percentage, @Content,@TypeEnglish,@NFlag
while @@FETCH_STATUS=0
BEGIN
if @Flag=@NFlag --外Flag等于内NFlag
set @ContentString=@ContentString+@TypeEnglish+' '+ convert(nvarchar(20),@Percentage)+'%'+' '+@Content +'<br/>'
fetch next from MyInsideCursor into @Percentage, @Content,@TypeEnglish,@NFlag
END
insert into @OrderContentOrdrNODatat([OrderNo],[Content],[Flag])values(@OrderNO,@ContentString,@Flag)
set @ContentString='' --设置为空
CLOSE MyInsideCursor --关闭内层
DEALLOCATE MyInsideCursor --撤销内层游标
fetch next from myCursor into @Flag --读取外层游标-外层游标继续向下移动一行
End
close myCursor --关闭游标
deallocate myCursor --撤销游标
RETURN
end
---得到效果如下:
本想用SQL语句来写的,搞了一下还是没搞出来,项目需要,先就用游标写了(性能当然不是很好)!