获取一组数两两组合情况

数据库 waitig 604℃ 百度已收录 0评论

CREATE function [dbo].[Fun_GetArrange](
@input NVARCHAR(MAX)
)
returns @Arrange table (Val1 VARCHAR(200),Val2 VARCHAR(200))
as 
BEGIN
    DECLARE @FirstVal VARCHAR(50)
DECLARE @SecondVal VARCHAR(50)
    DECLARE @ExcludeValTable TABLE(ExcludeVal VARCHAR(50))
IF(CHARINDEX(‘,’,@input) = 0)
BEGIN
INSERT INTO @Arrange( Val1, Val2 )VALUES(@input,NULL)
RETURN
    END
    
DECLARE My_Cursor CURSOR –定义游标
FOR (SELECT * FROM dbo.Fun_SplitString(@input,’,’)) –查出需要的集合放到游标中
OPEN My_Cursor; –打开游标
FETCH NEXT FROM My_Cursor INTO @FirstVal –读取第一行数据
WHILE @@FETCH_STATUS = 0
    BEGIN
               INSERT INTO @ExcludeValTable (ExcludeVal) VALUES (@FirstVal)
DECLARE My_Cursor1 CURSOR –定义游标
FOR (SELECT * FROM dbo.Fun_SplitString(@input,’,’) WHERE NOT EXISTS(SELECT 1 FROM @ExcludeValTable
WHERE c1  = ExcludeVal)) –查出需要的集合放到游标中
OPEN My_Cursor1; –打开游标
FETCH NEXT FROM My_Cursor1 INTO @SecondVal –读取第一行数据
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Arrange(Val1,Val2)VALUES (@FirstVal,@SecondVal)

FETCH NEXT FROM My_Cursor1 INTO @SecondVal –读取下一行数据
END
CLOSE My_Cursor1; –关闭游标
DEALLOCATE My_Cursor1; –释放游标
    FETCH NEXT FROM My_Cursor INTO @FirstVal –读取下一行数据
    END
CLOSE My_Cursor; –关闭游标
DEALLOCATE My_Cursor; –释放游标
return;
end


本文由【waitig】发表在等英博客
本文固定链接:获取一组数两两组合情况
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)