SQL Server存储过程排序算法

发表于 | 分类于

不用order by，第一次写存储过程，选了简单地选择排序法练练手。不足之处恳请斧正，还望海涵。
思路是输入一串有分隔符的数字字符串，加上id将其存入表中。再根据id循环比较。

1.插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE PROC f_splite
	@s VARCHAR(100),
	@split VARCHAR(10)
AS
	BEGIN
		DECLARE @splitlen INT
		DECLARE @i INT = 1
		SET @splitlen = LEN(@split + 'a') - 2
		WHILE CHARINDEX(@split, @s) > 0
			BEGIN
				INSERT INTO dbo.num_order
				VALUES ( @i,LEFT(@s, CHARINDEX(@split, @s) - 1) )
				SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
					SET @i = @i +1
			END
		--INSERT into dbo.num_order
		--VALUES (@i,@s)
	END
GO

1
2
DECLARE @str NVARCHAR(200)='5,4,3,2,1,'
exec f_splite @s = @str,@split=','

2.选择排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
/*选择排序法*/
IF OBJECT_ID('paixu') IS NOT NULL
	DROP PROC paixu 
GO
CREATE PROC paixu
	 @id int=1
AS
	BEGIN
		DECLARE @len int
		SET @len = (select count(1) from  dbo.num_order)
		DECLARE @i int = 1 --外层循环体
		DECLARE @min_index int --最小值索引
		DECLARE @current_num int --当前的最小值
		DECLARE @next_num int  --与当前值比较的值

		WHILE(@i<@len-1)
			BEGIN
				--print '1i的值是'+convert(varchar,@i)
				SET @min_index = @i
				DECLARE @j int = @i+1
				--print '1j的值是'+convert(varchar,@j)
				WHILE(@j<=@len)
					BEGIN
						SET @current_num = ( SELECT num FROM dbo.num_order WHERE id=@min_index)
						--print '@current_num的值是'+convert(varchar,@current_num)
						SET @next_num = (SELECT num FROM dbo.num_order WHERE id=@j)
						--print '@next_num的值是'+convert(varchar,@next_num)
						if(@current_num>@next_num)
							BEGIN
								SET @min_index = @j
							END
						set @j+=1
						--print '2j的值是'+convert(varchar,@j)
					END
					;with x as (select id,num from num_order where id=@i),y as (select id,num from num_order where id=@min_index) update num_order set num=z.num from (select x.num,y.id from x,y union all select y.num,x.id from x,y)z where num_order.id=z.id
					--select * from dbo.num_order
					set @i+=1
					--print '2i的值是'+convert(varchar,@i)
			END
	END

3.执行

1
2
3
4
5
6
delete from num_order
DECLARE @str NVARCHAR(200)='5,4,3,2,1,'
exec f_splite @s = @str,@split=','
select * from num_order
exec paixu
select * from num_order