http://topic.csdn.net/u/20101109/13/6e9b7e81-9b3e-40d9-9288-5f8f71bf78bc.html?seed=282604328&r=69797825#r_69797825
贴一定要结，不给楼上coleling分就对不住你，还想请教，所有的执行结果如何select*into [result]？？
谢谢coleling 的回复，你是强人，最后一个问题请帮忙解决，这是给你开的专帖，答案就写在这里，免得别人误会
贴一定要结，不给楼上coleling分就对不住你，还想请教，所有的执行结果如何select*into [result]？？
谢谢coleling 的回复，你是强人，最后一个问题请帮忙解决，这是给你开的专帖，答案就写在这里，免得别人误会
if object_id('tbtt','U') is not null
drop table tbtt
go--建表,插入演示数据
create table tbtt(a varchar(2), b varchar(2), c varchar(2), d varchar(2), e varchar(2), f varchar(2), g varchar(2), h varchar(2), i varchar(2), j varchar(2))
insert tbtt
select '04', '01', '03', '06', '02', '15', '09', '30', '05', '14' union all
select '11', '08', '13', '07', '12', '32', '16', null, '10', '21' union all
select '19', '17', '18', '23', null, null, null, null, null, '22' union all
select '20', '25', '27', '29', null, null, null, null, null, '26' union all
select '24', '33', null, null, null, null, null, null, null, '31' union all
select '28', null, null, null, null, null, null, null, null, null
GOif object_id('OrderString','FN') is not null
drop function OrderString
go--建立函数，功能是把类似于'2,1,3'的字符串转成'1 2 3'
CREATE FUNCTION OrderString(@s VARCHAR(8000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @ret VARCHAR(1000)
SET @ret = '' DECLARE @tb TABLE(id varchar(10)) INSERT @tb
SELECT SUBSTRING(@s,number,CHARINDEX(',',@s+',',number+1)-number)
FROM master..spt_values
WHERE CHARINDEX(',',','+@s,number) = number AND type = 'P' SELECT @ret = @ret + id + ' '
FROM @tb
ORDER BY id --去掉最后的' '
RETURN RTRIM(@ret)
END
GO--存放最终结果
declare @result table(array varchar(100))declare @tb table(anum int, bnum int, cnum int, dnum int, enum int, fnum int, gnum int, hnum int, inum int, jnum int);with
t1 as(select 1 num union all select 2 union all select 3),
t2 as(select 1 num union all select 2),
t3 as(select 0 num union all select 1),
--t4 as(select 0 num union all select 2),
t4 as(select 0 num union all select 1 union all select 2),
t5 as(select 0 num union all select 1),
t6 as(select 0 num union all select 1),
t7 as(select 0 num union all select 1),
t8 as(select 0 num union all select 1),
t9 as(select 0 num union all select 1),
t10 as(select 0 num union all select 1)insert @tb
select t1.num,t2.num,t3.num,t4.num,t5.num,t6.num,t7.num,t8.num,t9.num,t10.num
from t1,t2,t3,t4,t5,t6,t7,t8,t9,t10
where t1.num+t2.num+t3.num+t4.num+t5.num+t6.num+t7.num+t8.num+t9.num+t10.num = 6declare @anum int, @bnum int, @cnum int, @dnum int, @enum int, @fnum int, @gnum int, @hnum int, @inum int, @jnum int
declare currow cursor for
select anum, bnum, cnum, dnum, enum, fnum, gnum, hnum, inum, jnum from @tb
open currow
fetch next from currow into @anum, @bnum, @cnum, @dnum, @enum, @fnum, @gnum, @hnum, @inum, @jnum
while @@fetch_status = 0
begin
insert @result
select substring(a.array+b.array+c.array+d.array+e.array+f.array+g.array+h.array+i.array+j.array,1,17)
from (
select num=1,array=a.a+',' from tbtt a union all
select num=2,array=a.a+','+b.a+',' from tbtt a,(select a from tbtt) b where a.a > b.a union all
select num=3,array=a.a+','+b.a+','+c.a+',' from tbtt a,(select a from tbtt) b,(select a from tbtt) c where a.a > b.a and b.a > c.a
) a, (
select num=1,array=a.b+',' from tbtt a where a.b is not null union all
select num=2,array=a.b+','+b.b+',' from tbtt a,(select b from tbtt) b where a.b > b.b
) b, (
select num=0,array='' union all
select num=1,array=a.c+',' from tbtt a where a.c is not null
) c, (
select num=0,array='' union all
select num=1,array=a.d+',' from tbtt a where a.d is not null union all
select num=2,array=a.d+','+b.d+',' from tbtt a,(select d from tbtt) b where a.d > b.d
) d, (
select num=0,array='' union all
select num=1,array=a.e+',' from tbtt a where a.e is not null
) e, (
select num=0,array='' union all
select num=1,array=a.f+',' from tbtt a where a.f is not null
) f, (
select num=0,array='' union all
select num=1,array=a.g+',' from tbtt a where a.g is not null
) g, (
select num=0,array='' union all
select num=1,array=a.h+',' from tbtt a where a.h is not null
) h, (
select num=0,array='' union all
select num=1,array=a.i+',' from tbtt a where a.i is not null
) i, (
select num=0,array='' union all
select num=1,array=a.j+',' from tbtt a where a.j is not null
) j
where a.num=@anum and b.num=@bnum and c.num=@cnum and d.num=@dnum and e.num=@enum and
g.num=@gnum and h.num=@hnum and i.num=@inum and f.num=@fnum and j.num=@jnum fetch next from currow into @anum, @bnum, @cnum, @dnum, @enum, @fnum, @gnum, @hnum, @inum, @jnum
end
close currow
deallocate currowselect dbo.OrderString(array) from @result order by dbo.OrderString(array)/*
----------------------------------------------------------------
01 02 03 04 05 06
01 02 03 04 05 07
01 02 03 04 05 08
01 02 03 04 05 09
01 02 03 04 05 11
01 02 03 04 05 14
01 02 03 04 05 15
.........
26 27 28 29 30 33
26 27 28 29 32 33
26 27 28 30 32 33
26 28 29 30 32 33
27 28 29 30 31 33
27 28 29 30 32 33
27 28 29 31 32 33
27 28 30 31 32 33
28 29 30 31 32 33(336870 row(s) affected)
*/
select dbo.OrderString(array) array into result from @result order by dbo.OrderString(array)最后，就可以用select * from result来查看了
结果
(6 行受影响)
消息 1038，级别 15，状态 5，第 75 行
缺少对象或列名，或者对象或列名为空。对于 SELECT INTO 语句，请确保每列均具有名称。对于其他语句，请查找空的别名。不允许使用定义为 "" 或 [] 的别名。请添加名称或单个空格作为别名。
select dbo.OrderString(array) array into result from @result order by dbo.OrderString(array)你一定没把红字部分加上