如一串数字“3,43,23,64,234,76,5,9,14,27”进行排序输入到空表A中。
要求在表A中的排列方式如下:
num
3
5
9
14
23
27
43
64
76
234这个语句怎样写,谢谢!
忘了讲进行排序,不好意思再发一遍,顺便把别人写好的未排序的代码发下:create table A (num int)
declare @strnum varchar(100)
declare @strsql varchar(1000)
set @strnum='3,43,23,64,234,76,23,5,9,14,27'
set @strsql='insert into A select ' + replace(@Strnum,',',' union all select ')
exec (@Strsql)
select * from A/*
num
-----------
3
43
23
64
234
76
23
5
9
14
27
(所影响的行数为 11 行)
*/
要求在表A中的排列方式如下:
num
3
5
9
14
23
27
43
64
76
234这个语句怎样写,谢谢!
忘了讲进行排序,不好意思再发一遍,顺便把别人写好的未排序的代码发下:create table A (num int)
declare @strnum varchar(100)
declare @strsql varchar(1000)
set @strnum='3,43,23,64,234,76,23,5,9,14,27'
set @strsql='insert into A select ' + replace(@Strnum,',',' union all select ')
exec (@Strsql)
select * from A/*
num
-----------
3
43
23
64
234
76
23
5
9
14
27
(所影响的行数为 11 行)
*/
可以先插入临时表,在将排序后的结果插入A表
create proc wsp
@sql varchar(1000)
as
set @sql='insert into #temp select '+replace(@sql,',',' union all select ')
exec(@sql)
insert into A select * from #temp order by num
exec wsp '3,43,23,64,234,76,23,5,9,14,27'
create table A (num int)
declare @strnum varchar(100)
set @strnum='3,43,23,64,234,76,23,5,9,14,27'
while charindex(',',@strnum)>0
begin
insert into # values (left(@strnum,charindex(',',@strnum)-1))
set @Strnum=stuff(@strnum,1,charindex(',',@strnum),'')
end
insert into # values (@strnum)insert into A select num from # order by num
select * from A
/*
num
-----------
3
5
9
14
23
23
27
43
64
76
234(所影响的行数为 11 行)
*/
不好意思。敲错了create table a(num varchar(10))alter proc wsasdfasdfp
@sql varchar(1000)
as
create table #temp(num int)
set @sql='insert into #temp select '+replace(@sql,',',' d union all select ')
exec(@sql)
select * from #temp order by num
exec wsasdfasdfp '3,43,23,64,234,76,23,5,9,14,27'
exec(@sql)中的临时表与你前面定义的临时表不是一个呀,生存期不一样.
@strnum varchar(100)
) as
declare @strsql varchar(1000)
--set @strnum='3,43,23,64,234,76,23,5,9,14,27'
set @strsql='create table # (num int) insert into # select ' + replace(@Strnum,',',' union all select ')
set @Strsql=@strsql+' insert into A select num from # order by num'
exec (@Strsql)
exec fulla '3,43,23,64,234,76,23,5,9,14,27'num
-----------
3
5
9
14
23
23
27
43
64
76
234
还不行??
先创建存储过程:
create proc wsp
@sql varchar(1000)
as
create table #temp(num int)
set @sql='insert into #temp select '+replace(@sql,',',' d union all select ')
exec(@sql)
insert into a select * from #temp order by num然后调用:
exec wsp'3,43,23,64,234,76,23,5,9,14,27'
不是int型的吧?
学到的东西包括:
1、联想应用order by..;
2、exec函数;
3、SysObjects的学习;
---
看完帖子后,我想到上面的三点东西。
drop table A
create table A (num int)
declare @strnum varchar(100)
declare @strsql varchar(1000)
set @strnum='3,43,23,64,234,76,23,5,9,14,27'
set @strsql='create table B (num int); insert into B select ' + replace(@Strnum, ',', ' union all select ')
exec (@Strsql)
set @strsql='insert into A select * from B order by num; drop table B '
exec (@Strsql)
select * from A
@string varchar(8000),
@divider varchar(255)
)
RETURNS @retParts TABLE (part VARCHAR(7000))
AS
BEGIN DECLARE @p1 int, @p2 int
DECLARE @parts TABLE (part VARCHAR(7000))
SELECT @p1 = 1, @p2 = 1
-- pre-trim
--set @string = replace(@string, ' ', '') WHILE 0 < CHARINDEX(@divider, @string, @p1)
BEGIN
SET @p2 = CHARINDEX(@divider, @string, @p1) INSERT @parts (part)
SELECT substring(@string, @p1, @p2 - @p1) SET @p1 = @p2 + 1
END
-- add the last segment
INSERT @parts (part)
SELECT substring(@string, @p1, len(@string)) INSERT @retParts (part)
SELECT RTRIM(LTRIM(part))
FROM @parts
WHERE len(part) > 0
RETURN
END
GOSELECT part
from dbo.fn_StringSplit('3,43,23,64,234,76,23,5,9,14,27', ',')
declare @strnum varchar(100)
declare @strsql varchar(1000) set @strnum='3,43,23,64,234,76,23,5,9,14,27'
set @strsql= 'select ' + replace(@Strnum,',',' as num union select ') exec ('insert into A ' + @strsql )
只需要数字后面加上一个别名就好了, 这样进行select的时候会进行自动的ASC排序,就可以得到楼主想要的结果,如果想要DESC的排序,把最后一句替换成exec ('insert into A select * from (' + @strsql + ') as tmp order by tmp.num desc')