现在已知表【bt】里有以下记录A列：04 11 19 20 24 28 取1-3 个数
B列：01 08 17 25 33 取1-2 个数
C列：03 13 18 27 取0-1 个数
D列：06 07 23 29 取0-2 个数
E列：02 12 取0-1 个数
F列：15 32 取0-1 个数
G列：09 16 取0-1 个数
H列：30 取0-1 个数
I列：05 10 取0-1 个数
J列：14 21 22 26 31 取0-1 个数用SQL如何完成6个数的所有组合，并建立【6组合表】记录这些组合数 例如‘04 08 14 25 28 32’就是从
A列：04 28 取2个数
B列：08 25 取2个数
F列：32 取1个数
J列：14 取1个数完成的组合
B列：01 08 17 25 33 取1-2 个数
C列：03 13 18 27 取0-1 个数
D列：06 07 23 29 取0-2 个数
E列：02 12 取0-1 个数
F列：15 32 取0-1 个数
G列：09 16 取0-1 个数
H列：30 取0-1 个数
I列：05 10 取0-1 个数
J列：14 21 22 26 31 取0-1 个数用SQL如何完成6个数的所有组合，并建立【6组合表】记录这些组合数 例如‘04 08 14 25 28 32’就是从
A列：04 28 取2个数
B列：08 25 取2个数
F列：32 取1个数
J列：14 取1个数完成的组合
B列：01 08 17 25 33 可以取1-2 个数
C列：03 13 18 27 可以取0-1 个数
D列：06 07 23 29 可以取0-2 个数
E列：02 12 可以取0-1 个数
F列：15 32 可以取0-1 个数
G列：09 16 可以取0-1 个数
H列：30 可以取0-1 个数
I列：05 10 可以取0-1 个数
J列：14 21 22 26 31 可以取0-1 个数取这些列的个数的总和不能超过6个数
a b c d e f g h i j
----- ----- ----- ----- ----- ----- ----- ----- ----- -----
04 01 03 06 02 15 09 30 05 14
11 08 13 07 12 32 16 10 21
19 17 18 23 22
20 25 27 29 26
24 33 31
28 如果要求每个列取的个数不一样，这个难度很大吗
--建表,插入演示数据
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 --存放最终结果
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),
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 a.array+b.array+c.array+d.array+e.array+f.array+g.array+h.array+i.array+a.array
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 * from @result/*
array
------------------------------------------
19,11,04,08,01,03
19,11,04,08,01,13
19,11,04,08,01,18
19,11,04,08,01,27
19,11,04,17,01,03
19,11,04,17,01,13
19,11,04,17,01,18
19,11,04,17,01,27
19,11,04,17,08,03
......
28,33,09,30,10,31
28,33,16,30,05,31
28,33,16,30,10,31(183370 行受影响)
*/
执行后，出现了这样的结果
array
---------------------------
19,11,04,08,01,03,19,11,04,
19,11,04,08,01,13,19,11,04,
19,11,04,08,01,18,19,11,04,
19,11,04,08,01,27,19,11,04,
19,11,04,17,01,03,19,11,04,
19,11,04,17,01,13,19,11,04,
19,11,04,17,01,18,19,11,04,
......
04,33,17,13,29,06,04,
04,33,17,18,29,06,04,
04,33,17,27,29,06,04,
04,33,25,03,29,06,04,
04,33,25,13,29,06,04,
04,33,25,18,29,06,04,
......
19,11,04,17,03,12,19,11,04,
19,11,04,17,13,02,19,11,04,
19,11,04,17,13,12,19,11,04,
19,11,04,17,18,02,19,11,04,
19,11,04,17,18,12,19,11,04,
19,11,04,17,27,02,19,11,04,
......
19,11,17,01,18,12,19,11,
19,11,17,01,27,02,19,11,
19,11,17,01,27,12,19,11,
19,11,17,08,03,02,19,11,
......
(183370 行受影响)可仅仅需要6位数的结果，而且要求按照从小到大的升序打印结果.....
---------------------------
19,11,04,08,01,03,19,11,04, ----去掉重复的数‘19,11,04,’
19,11,04,08,01,13,19,11,04,
19,11,04,08,01,18,19,11,04,
19,11,04,08,01,27,19,11,04,
19,11,04,17,01,03,19,11,04,
19,11,04,17,01,13,19,11,04,
19,11,04,17,01,18,19,11,04,
......
04,33,17,13,29,06,04,----去掉重复的数‘04,’
04,33,17,18,29,06,04,
04,33,17,27,29,06,04,
04,33,25,03,29,06,04,
04,33,25,13,29,06,04,
04,33,25,18,29,06,04,
......
19,11,04,17,03,12,19,11,04,----去掉重复的数‘19,11,04,’
19,11,04,17,13,02,19,11,04,
19,11,04,17,13,12,19,11,04,
19,11,04,17,18,02,19,11,04,
19,11,04,17,18,12,19,11,04,
19,11,04,17,27,02,19,11,04,
......
19,11,17,01,18,12,19,11,----去掉重复的数‘19,11,’
19,11,17,01,27,02,19,11,
19,11,17,01,27,12,19,11,
19,11,17,08,03,02,19,11,
......
(183370 行受影响)
01 02 03 04 05 06
01 02 05 06 10 29
01 02 07 15 21 31
01 04 08 17 25 33
02 06 08 11 12 25
02 09 12 16 25 27
03 13 18 20 27 28
04 08 14 25 28 32
04 11 19 20 24 28
05 06 15 16 19 26
06 07 13 23 28 29
06 12 15 18 29 32
08 11 13 18 25 30
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 --建立函数，功能是把类似于'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)
*/
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
go
declare @n int
declare @a int,@a1 int,@a2 int,@a3 int --@a为取了几个数,@an为所取的第n个数在哪一行,下同
declare @b int,@b1 int,@b2 int
declare @c int,@c1 int
declare @d int,@d1 int,@d2 int
declare @e int,@e1 int,@f int,@f1 int,@g int,@g1 int,@h int,@i int,@i1 int,@j int,@j1 int
create table #t(id int identity(1,1),x varchar(2))
create table #answer(x1 varchar(2),x2 varchar(2),x3 varchar(2),x4 varchar(2),x5 varchar(2),x6 varchar(2))
set @a=3
set @n=0
while @a>0
begin
set @a1=1
while @a1<=7-@a
begin
set @a2=@a1+1
if @a<2 set @a2=7
while @a2<=8-@a
begin
set @a3=@a2+1
if @a<3 set @a3=9-@a
while @a3<=9-@a
begin
set @b=2
while @b>0
begin
set @b1=1
while @b1<=6-@b
begin
set @b2=@b1+1
if @b<2 set @b2=6
while @b2<=7-@b
begin
set @c=1
while @c>=0
begin
set @c1=1
while @c1<=4
begin
if @c=0 set @c1=5
set @d=6-@a-@b-@c
if @d>2 set @d=2
while @d>=0
begin
set @d1=1
while @d1<=5-@d
begin
if @d=0 set @d1=5
set @d2=@d1+1
while @d2<=6-@d
begin
if @a+@b+@c+2-@d>=5 set @d2=6
set @e=1-(@a+@b+@c+@d)/6
while @e>=0
begin
set @e1=1
while @e1<=3-@e
begin
if @e=0 set @e1=3
set @f=1-(@a+@b+@c+@d+@e)/6
while @f>=0
begin
set @f1=1
while @f1<=3-@f
begin
if @f=0 set @f1=3
set @g=1-(@a+@b+@c+@d+@e+@f)/6
while @g>=0
begin
set @g1=1
while @g1<=3-@g
begin
if @g=0 set @g1=3
set @h=1-(@a+@b+@c+@d+@e+@f+@g)/6
while @h>=0
begin
set @i=1-(@a+@b+@c+@d+@e+@f+@g+@h)/6
while @i>=060多万条数据,运行时间太长了.
set @i1=1
while @i1<=3-@i
begin
if @i=0 set @i1=3
set @j=1-(@a+@b+@c+@d+@e+@f+@g+@h+@i)/6
while @j>=0
begin
set @j1=1
while @j1<=6-@j
begin
if @j=0 set @j1=6
--set @n=@n+1
insert into #t select a from tbtt where id=@a1 and a is not null
union all select a from tbtt where id=@a2 and a is not null
union all select a from tbtt where id=@a3 and a is not null
union all select b from tbtt where id=@b1 and b is not null
union all select b from tbtt where id=@b2 and b is not null
union all select c from tbtt where id=@c1 and c is not null
union all select d from tbtt where id=@d1 and d is not null
union all select d from tbtt where id=@d2 and d is not null
union all select e from tbtt where id=@e1 and e is not null
union all select f from tbtt where id=@f1 and f is not null
union all select g from tbtt where id=@g1 and g is not null
union all select h from tbtt where id=@h and h is not null
union all select i from tbtt where id=@i1 and i is not null
union all select j from tbtt where id=@j1 and j is not null
insert into #answer
select distinct
(select x from #t where id=1),
(select x from #t where id=2),
(select x from #t where id=3),
(select x from #t where id=4),
(select x from #t where id=5),
(select x from #t where id=6)
from #t where exists(select 1 from #t having count(*)=6)
TRUNCATE TABLE #t
set @j1=@j1+1
end
set @j=@j-1
end
set @i1=@i1+1
end
set @i=@i-1
end
set @h=@h-1
end
set @g1=@g1+1
end
set @g=@g-1
end
set @f1=@f1+1
end
set @f=@f-1
end
set @e1=@e1+1
end
set @e=@e-1
end
set @d2=@d2+1
end
set @d1=@d1+1
end
set @d=@d-1
end
set @c1=@c1+1
end
set @c=@c-1
end
set @b2=@b2+1
end
set @b1=@b1+1
end
set @b=@b-1
end
set @a3=@a3+1
end
set @a2=@a2+1
end
set @a1=@a1+1
end
set @a=@a-1
end
--select @n
select * from #answer
go
drop table tbtt,#t,#answer
执行结果消息 111，级别 15，状态 1，第 12 行
'CREATE FUNCTION' 必须是查询批次中的第一个语句。
消息 137，级别 15，状态 2，第 22 行
必须声明标量变量 "@s"。
消息 178，级别 15，状态 1，第 31 行
在此上下文中不能使用带有返回值的 RETURN 语句。无法执行代码，请教指正
你在这一句CREATE FUNCTION OrderString(@s VARCHAR(8000))的上面加一个GO
存在以下不足：
1、把这6位数要求按照从小到大的升序排列，如结果中的04 11 19 01 08 03要的是这样的01 03 04 08 11 193、打印结果格式为(升序)
x1 x2 x3 x4 x5 x6
---- ---- ---- ---- ---- ----
04 11 19 01 08 03 ---每一行的6位数要求按照从小到大的升序排列，以下同此要求
04 11 19 01 08 13
04 11 19 01 08 18
......
24 28 33 30 10 21
24 28 33 30 10 22
24 28 33 30 10 26
24 28 33 30 10 31
04 01 08 03 06 07 ----所有行的打印结果为升序排列
04 01 08 03 06 23
04 01 08 03 06 29
04 01 08 03 07 23
04 01 08 03 07 29谢谢！毕竟迈了很大一步，有了18楼和19楼的大力帮助，胜利在望，不足之处还望修改
假如对k列设定取不同的个数，编写语句如下，请批改
取0-1个数
select num=0,array='' union all
select num=1,array=a.k+',' from tbtt a where a.k is not null取0-2个数
select num=0,array='' union all
select num=1,array=a.k+',' from tbtt a where a.k is not null union all
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k > b.k取1-2个数
select num=1,array=a.k+',' from tbtt a where a.k is not null union all
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k > b.k取2-3个数
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k > b.k is not null union all
select num=3,array=a.k+','+b.k+','+c.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c where a.k > b.k and b.k > c.k
取1-3个数
select num=1,array=a.k+',' from tbtt a union all
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k > b.k union all
select num=3,array=a.k+','+b.k+','+c.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c where a.k > b.a and b.k > c.k上面的语句对吗？若取2-5个数的语句是这样的吗，学习中，请赐教取2-5个数
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k > b.k union all
select num=3,array=a.k+','+b.k+','+c.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c where a.k > b.a and b.k > c.k
select num=4,array=a.k+','+b.k+','+c.k+','+d.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c ,(select k from tbtt) d where a.k > b.a and b.k > c.k and c.k >d.k
select num=4,array=a.k+','+b.k+','+c.k+','+d.k+','+e.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c ,(select k from tbtt) d ,(select k from tbtt) e where a.k > b.a and b.k > c.k and c.k >d.k and d.k >e.k
--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 --A列：04 11 19 20 24 28 取1-3 个数
--B列：01 08 17 25 33 取1-2 个数
--C列：03 13 18 27 取0-1 个数
--D列：06 07 23 29 取0-2 个数
--E列：02 12 取0-1 个数
--F列：15 32 取0-1 个数
--G列：09 16 取0-1 个数
--H列：30 取0-1 个数
--I列：05 10 取0-1 个数
--J列：14 21 22 26 31 取0-1 个数
;with c_tbtt as (
select s='a',v=a from tbtt where a is not null union all
select s='b',v=b from tbtt where b is not null union all
select s='c',v=c from tbtt where c is not null union all
select s='d',v=d from tbtt where d is not null union all
select s='e',v=e from tbtt where e is not null union all
select s='f',v=f from tbtt where f is not null union all
select s='g',v=g from tbtt where g is not null union all
select s='h',v=h from tbtt where h is not null union all
select s='i',v=i from tbtt where i is not null union all
select s='j',v=j from tbtt where j is not null
)
select s,v from
(
select top 6 s=isnull(a.s,b.s),v=isnull(a.v,b.v)
from
(select top (abs(checksum(newid()))%3+1) s,v from c_tbtt where s='a' order by newid() union all
select top (abs(checksum(newid()))%2+1) s,v from c_tbtt where s='b' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='c' order by newid() union all
select top (abs(checksum(newid()))%3) s,v from c_tbtt where s='d' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='e' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='f' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='g' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='h' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='i' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='j') a right join
c_tbtt b on a.s=b.s and a.v=b.v
order by case when a.s in ('a','b') then 0 else 1 end,case when a.v is not null then 0 else 1 end,newid()
) a order by v
--取0-1个数
select num=0,array='' union all
select num=1,array=a.k+',' from tbtt a where a.k is not null--取0-2个数
select num=0,array='' union all
select num=1,array=a.k+',' from tbtt a where a.k is not null union all
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k > b.k--取1-2个数
select num=1,array=a.k+',' from tbtt a where a.k is not null union all
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k > b.k--取2-3个数
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k is not null and a.k > b.k union all
select num=3,array=a.k+','+b.k+','+c.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c where a.k > b.k and b.k > c.k
--取1-3个数
select num=1,array=a.k+',' from tbtt a where a.k is not null union all
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k > b.k union all
select num=3,array=a.k+','+b.k+','+c.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c where a.k > b.a and b.k > c.k--取2-5个数
select num=2,array=a.k+','+b.k+',' from tbtt a,(select k from tbtt) b where a.k is not null and a.k > b.k union all
select num=3,array=a.k+','+b.k+','+c.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c where a.k > b.a and b.k > c.k
select num=4,array=a.k+','+b.k+','+c.k+','+d.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c ,(select k from tbtt) d where a.k > b.a and b.k > c.k and c.k >d.k
select num=4,array=a.k+','+b.k+','+c.k+','+d.k+','+e.k+',' from tbtt a,(select k from tbtt) b,(select k from tbtt) c ,(select k from tbtt) d ,(select k from tbtt) e where a.k > b.a and b.k > c.k and c.k >d.k and d.k >e.k--同时，把上面的cte也改一下
--取0-2个数
tk as(select 0 num union all select 1 union all select 2),
--取1-3个数
tk as(select 1 num union all select 2 union all select 3),
--取0-1个数
tk as(select 0 num union all select 1),
--取2-3个数
tk as(select 2 num union all select 3),
s v
---- ----
b 01
i 05
a 20
a 24
a 28
b 33(6 行受影响)
等待答疑......
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)
;with c_tbtt as (
select s='a',v=a from tbtt where a is not null union all
select s='b',v=b from tbtt where b is not null union all
select s='c',v=c from tbtt where c is not null union all
select s='d',v=d from tbtt where d is not null union all
select s='e',v=e from tbtt where e is not null union all
select s='f',v=f from tbtt where f is not null union all
select s='g',v=g from tbtt where g is not null union all
select s='h',v=h from tbtt where h is not null union all
select s='i',v=i from tbtt where i is not null union all
select s='j',v=j from tbtt where j is not null
)select s,v into #t from
(
select top 6 s=isnull(a.s,b.s),v=isnull(a.v,b.v)
from
(select top (abs(checksum(newid()))%3+1) s,v from c_tbtt where s='a' order by newid() union all
select top (abs(checksum(newid()))%2+1) s,v from c_tbtt where s='b' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='c' order by newid() union all
select top (abs(checksum(newid()))%3) s,v from c_tbtt where s='d' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='e' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='f' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='g' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='h' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='i' order by newid() union all
select top (abs(checksum(newid()))%2) s,v from c_tbtt where s='j' order by newid()) a right join
c_tbtt b on a.s=b.s and a.v=b.v
order by case when a.s in ('a','b') then 0 else 1 end,case when a.v is not null then 0 else 1 end,newid()
) a order by vdeclare @r_output varchar(100)
set @r_output=''
select @r_output=@r_output+right(100+v,2)+' '
from #t order by vprint @r_outputdrop table #t
--同时，把上面的cte也改一下
--取2-5个数，是这样修改吗
tk as(select 2 num union all select 3 union all select 4 union all select 5),
04 05 17 18 20 28
仅仅只有1条记录？总结一下18楼coleling的执行结果为336870条结果，用时11‘56'',时间可以忍受，对比19楼qianjin036a
的结果条目数372150条结果，34楼的zsforever的结果仅仅1条是错的！
我不知道执行结果的到底是多少？请coleling不要介意，请指点迷津——到底谁对呢？
谢谢coleling 的回复，你是强人，最后一个问题请帮忙解决，这是给你开的专帖，答案就写在专贴里，免得别人误会
和34楼的zsforever，可惜这个贴得分只有100分，多多少少都要给他们一些，欠18楼coleling一定补上！望大家谅解，这个贴后面一定结贴，结贴前想请教19楼qianjin036a和34楼的zsforever，只是想再多学习一下总结一下
18楼coleling的执行结果为336870条结果，用时11‘56'',时间可以忍受，对比19楼qianjin036a
的结果条目数372150条结果，34楼的zsforever的结果仅仅1条是错的！
我不知道执行结果的到底是多少？请coleling不要介意，请大家指点迷津——到底谁对呢？
if object_id('combnum','') is not null
drop function combnum
gocreate function combnum(@N int,@R INT)
returns int
as
begin
if @R > @N OR @R = 0 return 1 declare @v1 int,@v2 int,@i int
set @v1 = 1
set @v2 = @N
set @i = 2
while @i <= @R
begin
set @v1 = @v1 * @i
set @v2 = @v2*(@N-@i+1) set @i = @i + 1
end
return @v2/@v1
end
go;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 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)select sum(dbo.combnum(6,t1.num)*dbo.combnum(5,t2.num)*dbo.combnum(4,t3.num)*dbo.combnum(4,t4.num)*dbo.combnum(2,t5.num)*dbo.combnum(2,t6.num)*dbo.combnum(2,t7.num)*dbo.combnum(1,t8.num)*dbo.combnum(2,t9.num)*dbo.combnum(5,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 = 6
-----------
336870(1 行受影响)
484470是如何验算出来的？能给出函数公式吗？coleling的有说服力，愿意多学学，继续努力中......
----- ----- ----- ----- ----- ----- ----- ----- ----- -----
04 01 03 06 02 15 09 30 05 14
11 08 13 07 12 32 16 10 21
19 17 18 23 22
20 25 27 29 26
24 33 31
28
每个列取的个数是不一样的，楼上是这样理解的吗？
不好意思，算错，是这个数：334570d列取0时组合数：143350
d列取1-2时组合数：191220
看不明白......
go
create table #(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 #
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('tempdb.dbo.#bit') is not null drop table #bit
go
create table #bit(e int identity primary key, m bigint)
insert #bit select top 33 null from syscolumns
godeclare @2 bigint, @d datetime
select @2=2, @d=getdate()
update #bit set m=power(@2,e);with a as
(
--A列：04 11 19 20 24 28 取1-3 个数
select cn=1, power(@2,a) a from # where a is not null union all
select cn=2, power(@2,a.a)+power(@2,b.a) from # a, # b where a.a<b.a union all
select cn=3, power(@2,a.a)+power(@2,b.a)+power(@2,c.a) from # a, # b, # c where a.a<b.a and b.a<c.a
),
b as
(
--B列：01 08 17 25 33 取1-2 个数
select cn=1, power(@2,b) b from # where b is not null union all
select cn=2, power(@2,a.b)+power(@2,b.b) from # a, # b where a.b<b.b
),
d as
(
--D列：06 07 23 29 取0-2 个数
select cn=1, power(@2,d) d from # where d is not null union all
select cn=2, power(@2,a.d)+power(@2,b.d) from # a, # b where a.d<b.d
),
u as
(
--C, E-J 取0-1 个数
select x=1, c from # where c is not null union all
select x=2, e from # where e is not null union all
select x=3, f from # where f is not null union all
select x=4, g from # where g is not null union all
select x=5, h from # where h is not null union all
select x=6, i from # where i is not null union all
select x=7, j from # where j is not null
),
t as -- a/b至少有2个 最多4个够了
(
select cn=1, power(@2,c) t from u union all -- 任意1列取1个
select cn=2, power(@2,a.c)+power(@2,b.c) from u a, u b where a.x<b.x union all -- 任意2列各取1个
select cn=3, power(@2,a.c)+power(@2,b.c)+power(@2,c.c) from u a, u b, u c where a.x<b.x and b.x<c.x union all -- 任意3列各取1个
select cn=4, power(@2,a.c)+power(@2,b.c)+power(@2,c.c)+power(@2,d.c) from u a, u b, u c, u d where a.x<b.x and b.x<c.x and c.x<d.x -- 任意4列各取1个
),
ab as
(
--基集
select cn=a.cn+b.cn, ab=a+b from a, b
),
abd as
(
select cn=ab.cn+d.cn, abd=ab+d from ab, d where ab.cn+d.cn<=6
),
rs as
(
select bin=ab+t from ab, t where ab.cn+t.cn=6 union all-- D列取0个数
select abd+isnull(t,0) from abd left join t on abd.cn+t.cn=6 -- D列取1-2个数
)
select *, rs = convert(varchar(20), ltrim((select ' '+right(100+e,2) from #bit where m & t.bin>0 for xml path('')))) into #rs from rs t-- 生成排序记录时间
select datediff(second, @d, getdate()) second
/*
second
-----------
19
*/-- 这个不需要10秒，在预计的30秒之内：
select * from #rs order by rs
/*
bin rs
-------------------- --------------------
126 01 02 03 04 05 06
190 01 02 03 04 05 07
318 01 02 03 04 05 08.....15971909632 27 28 29 31 32 33
16508780544 27 28 30 31 32 33
16911433728 28 29 30 31 32 33(336870 行受影响)*/-- coleling 的记录数是对的，当时我算漏了a+b+d = 6(个数字) 的2300条--drop table #rs
create table tbtt(id int identity(1,1),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
go
--获取前三列中的所有组合
select * into #t1 from(
select a.a as a1,1 as b1,b.a as a2,1 as b2,c.a as a3,1 as b3 from tbtt a,tbtt b,tbtt c where a.id<b.id and b.id <c.id
union all
select a.a as a1,1 as b1,b.a as a2,1 as b2,null as a3,0 as b3 from tbtt a,tbtt b where a.id<b.id
union all
select a as a1,1 as b1,null as a2,0 as b2,null as a3,0 as b3 from tbtt
)t1,(
select a.b as a4,1 as b4,b.b as a5,1 as b5 from tbtt a,tbtt b where a.id<b.id and a.b is not null and b.b is not null
union all
select b as a4,1 as b4,null as a5,0 as b5 from tbtt where b is not null
)t2,(
select null as a6,0 as b6
union all
select c as a6,1 as b6 from tbtt where c is not null
)t3
--将前三列组合中不足6个数的与第四列组合
select * into #t2 from(
select * from #t1 where b1+b2+b3+b4+b5+b6<6
)t,(
select a7,b7,a8,b8 from(
select 0 as id,null as a7,0 as b7
union all
select id,d as a7,1 as a7 from tbtt where d is not null
)a,(
select 0 as id,null as a8,0 as b8
union all
select id,d as a8,1 as b8 from tbtt where d is not null
)b where a.id<b.id
union all
select null,0,null,0
)t4
--将前四列组合中不足6个数的与第五列组合
select * into #t3 from(
select * from #t2 where b1+b2+b3+b4+b5+b6+b7+b8<6
)t,(
select e as a9,1 as b9 from tbtt where e is not null
union all
select null ,0
)t5
--将前五列组合中不足6个数的与第六列组合
select * into #t4 from(
select * from #t3 where b1+b2+b3+b4+b5+b6+b7+b8+b9<6
)t,(
select f as a10,1 as b10 from tbtt where f is not null
union all
select null,0
)t6
--将前六列组合中不足6个数的与第七列组合
select * into #t5 from(
select * from #t4 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10<6
)t,(
select g as a11,1 as b11 from tbtt where g is not null
union all
select null,0
)t7
--将前七列组合中不足6个数的与第八列组合
select * into #t6 from(
select * from #t5 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11<6 and b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11>2
)t,(
select h as a12,1 as b12 from tbtt where h is not null
union all
select null,0
)t8
--将前八列组合中不足6个数的与第九列组合
select * into #t7 from(
select * from #t6 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12<6 and b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12>3
)t,(
select i as a13,1 as b13 from tbtt where i is not null
union all
select null,0
)t9
--将前九列组合中不足6个数的与第十列组合
select * into #t8 from(
select * from #t7 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13=5
)t,(
select j as a14,1 as b14 from tbtt where j is not null
union all
select null,0
)t10
--不分列合成了,只分表输出
select a1,a2,a3,a4,a5,a6 from #t1 where b1+b2+b3+b4+b5+b6=6
select a1,isnull(a2,'')as a2,isnull(a3,'')as a3,a4,isnull(a5,'')as a5,isnull(a6,'')as a6,isnull(a7,'')as a7,isnull(a8,'')as a8 from #t2 where b1+b2+b3+b4+b5+b6+b7+b8=6
select a1,isnull(a2,'')as a2,isnull(a3,'')as a3,a4,isnull(a5,'')as a5,isnull(a6,'')as a6,isnull(a7,'')as a7,isnull(a8,'')as a8,isnull(a9,'')as a9 from #t3 where b1+b2+b3+b4+b5+b6+b7+b8+b9=6
select a1,isnull(a2,'')as a2,isnull(a3,'')as a3,a4,isnull(a5,'')as a5,isnull(a6,'')as a6,isnull(a7,'')as a7,isnull(a8,'')as a8,isnull(a9,'')as a9,isnull(a10,'')as a10 from #t4 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10=6
select a1,isnull(a2,'')as a2,isnull(a3,'')as a3,a4,isnull(a5,'')as a5,isnull(a6,'')as a6,isnull(a7,'')as a7,isnull(a8,'')as a8,isnull(a9,'')as a9,isnull(a10,'')as a10,isnull(a11,'')as a11 from #t5 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11=6
select a1,isnull(a2,'')as a2,isnull(a3,'')as a3,a4,isnull(a5,'')as a5,isnull(a6,'')as a6,isnull(a7,'')as a7,isnull(a8,'')as a8,isnull(a9,'')as a9,isnull(a10,'')as a10,isnull(a11,'')as a11,isnull(a12,'')as a12 from #t6 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12=6
select a1,isnull(a2,'')as a2,isnull(a3,'')as a3,a4,isnull(a5,'')as a5,isnull(a6,'')as a6,isnull(a7,'')as a7,isnull(a8,'')as a8,isnull(a9,'')as a9,isnull(a10,'')as a10,isnull(a11,'')as a11,isnull(a12,'')as a12,isnull(a13,'')as a13 from #t7 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13=6
select a1,isnull(a2,'')as a2,isnull(a3,'')as a3,a4,isnull(a5,'')as a5,isnull(a6,'')as a6,isnull(a7,'')as a7,isnull(a8,'')as a8,isnull(a9,'')as a9,isnull(a10,'')as a10,isnull(a11,'')as a11,isnull(a12,'')as a12,isnull(a13,'')as a13,isnull(a14,'')as a14 from #t8 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13+b14=6
go
drop table tbtt,#t1,#t2,#t3,#t4,#t5,#t6,#t7,#t8
/*
a1 a2 a3 a4 a5 a6
---- ---- ---- ---- ---- ----
04 11 19 01 08 03
04 11 19 01 17 03
04 11 19 01 25 03
04 11 19 01 33 03
04 11 19 08 17 03
04 11 19 08 25 03
04 11 19 08 33 03
04 11 19 17 25 03
04 11 19 17 33 03
04 11 19 25 33 03
04 11 19 01 08 13
04 11 19 01 17 13
04 11 19 01 25 13
04 11 19 01 33 13
04 11 19 08 17 13
04 11 19 08 25 13
04 11 19 08 33 13
04 11 19 17 25 13
04 11 19 17 33 13
04 11 19 25 33 13
04 11 19 01 08 18
04 11 19 01 17 18
04 11 19 01 25 18
04 11 19 01 33 18
04 11 19 08 17 18
04 11 19 08 25 18
04 11 19 08 33 18
04 11 19 17 25 18
04 11 19 17 33 18
04 11 19 25 33 18
04 11 19 01 08 27
04 11 19 01 17 27
04 11 19 01 25 27
04 11 19 01 33 27
04 11 19 08 17 27
04 11 19 08 25 27
04 11 19 08 33 27
04 11 19 17 25 27...... 共336870条记录
*/
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
go
--获取前三列中的所有组合
select * into #t1 from(
select a.a as a1,1 as b1,b.a as a2,1 as b2,c.a as a3,1 as b3 from tbtt a,tbtt b,tbtt c where a.id<b.id and b.id <c.id
union all
select a.a as a1,1 as b1,b.a as a2,1 as b2,null as a3,0 as b3 from tbtt a,tbtt b where a.id<b.id
union all
select a as a1,1 as b1,null as a2,0 as b2,null as a3,0 as b3 from tbtt
)t1,(
select a.b as a4,1 as b4,b.b as a5,1 as b5 from tbtt a,tbtt b where a.id<b.id and a.b is not null and b.b is not null
union all
select b as a4,1 as b4,null as a5,0 as b5 from tbtt where b is not null
)t2,(
select null as a6,0 as b6
union all
select c as a6,1 as b6 from tbtt where c is not null
)t3
--将前三列组合中不足6个数的与第四列组合
select * into #t2 from(
select * from #t1 where b1+b2+b3+b4+b5+b6<6
)t,(
select a7,b7,a8,b8 from(
select 0 as id,null as a7,0 as b7
union all
select id,d as a7,1 as a7 from tbtt where d is not null
)a,(
select 0 as id,null as a8,0 as b8
union all
select id,d as a8,1 as b8 from tbtt where d is not null
)b where a.id<b.id
union all
select null,0,null,0
)t4
--将前四列组合中不足6个数的与第五列组合
select * into #t3 from(
select * from #t2 where b1+b2+b3+b4+b5+b6+b7+b8<6
)t,(
select e as a9,1 as b9 from tbtt where e is not null
union all
select null ,0
)t5
--将前五列组合中不足6个数的与第六列组合
select * into #t4 from(
select * from #t3 where b1+b2+b3+b4+b5+b6+b7+b8+b9<6
)t,(
select f as a10,1 as b10 from tbtt where f is not null
union all
select null,0
)t6
--将前六列组合中不足6个数的与第七列组合
select * into #t5 from(
select * from #t4 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10<6
)t,(
select g as a11,1 as b11 from tbtt where g is not null
union all
select null,0
)t7
--将前七列组合中不足6个数的与第八列组合
select * into #t6 from(
select * from #t5 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11<6 and b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11>2
)t,(
select h as a12,1 as b12 from tbtt where h is not null
union all
select null,0
)t8
--将前八列组合中不足6个数的与第九列组合
select * into #t7 from(
select * from #t6 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12<6 and b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12>3
)t,(
select i as a13,1 as b13 from tbtt where i is not null
union all
select null,0
)t9
--将前九列组合中不足6个数的与第十列组合
select * into #t8 from(
select * from #t7 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13=5
)t,(
select j as a14,1 as b14 from tbtt where j is not null
union all
select null,0
)t10
--合并为6列,并排序输出
delete from #t2 where b1+b2+b3+b4+b5+b6+b7+b8<>6
delete from #t3 where b1+b2+b3+b4+b5+b6+b7+b8+b9<>6
delete from #t4 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10<>6
delete from #t5 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11<>6
delete from #t6 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12<>6
delete from #t7 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13<>6
delete from #t8 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13+b14<>6
select * from(
select a1,a2,a3,a4,a5,a6 from #t1 where b1+b2+b3+b4+b5+b6=6
union all
select a1,isnull(a2,a8),isnull(a3,(case when b2=1 then a8 else a7 end)),a4,
isnull(a5,(case when b2+b3=2 then a8 else a7 end)),isnull(a6,isnull(a7,a8))
from #t2
union all
select a1,isnull(a2,a9),isnull(a3,(case when b2=1 then a9 else isnull(a8,a7) end)),a4,
isnull(a5,(case when b2+b3=2 then a9 else (case when b2+b3=1 then isnull(a8,a7) else a7 end) end)),
isnull(a6,isnull(a7,isnull(a8,a9)))
from #t3
union all
select a1,isnull(a2,a10),isnull(a3,(case when b2=1 then a10 else isnull(a9,isnull(a8,a7)) end)),a4,
isnull(a5,(case when b2+b3=2 then a10 else (case when b2+b3=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,a10))))
from #t4
union all
select a1,isnull(a2,a11),isnull(a3,(case when b2=1 then a11 else isnull(a10,isnull(a9,isnull(a8,a7))) end)),a4,
isnull(a5,(case when b2+b3=2 then a11 else (case when b2+b3=1 then isnull(a10,isnull(a9,isnull(a8,a7))) else (case when b10=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,isnull(a10,a11)))))
from #t5
union all
select a1,isnull(a2,a12),isnull(a3,(case when b2=1 then a12 else isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))) end)),a4,
isnull(a5,(case when b2+b3=2 then a12 else (case when b2+b3=1 then isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))) else (case when b11=1 then isnull(a10,isnull(a9,isnull(a8,a7))) else (case when b10=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,isnull(a10,isnull(a11,a12))))))
from #t6
union all
select a1,isnull(a2,a13),isnull(a3,(case when b2=1 then a13 else isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7))))) end)),a4,
isnull(a5,(case when b2+b3=2 then a13 else (case when b2+b3=1 then isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7))))) else (case when b12=1 then isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))) else (case when b11=1 then isnull(a10,isnull(a9,isnull(a8,a7))) else (case when b10=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end) end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,isnull(a10,isnull(a11,isnull(a12,a13)))))))
from #t7
union all
select a1,isnull(a2,a14),isnull(a3,(case when b2=1 then a14 else isnull(a13,isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))))) end)),a4,
isnull(a5,(case when b2+b3=2 then a14 else (case when b2+b3=1 then isnull(a13,isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))))) else (case when b13=1 then isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7))))) else (case when b12=1 then isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))) else (case when b11=1 then isnull(a10,isnull(a9,isnull(a8,a7))) else (case when b10=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end) end) end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,isnull(a10,isnull(a11,isnull(a12,isnull(a13,a14))))))))
from #t8
)t
order by convert(int,a1)+convert(int,a2)+convert(int,a3)+convert(int,a4)+convert(int,a5)+convert(int,a6)
go
drop table tbtt,#t1,#t2,#t3,#t4,#t5,#t6,#t7,#t8
/*
a1 a2 a3 a4 a5 a6
---- ---- ---- ---- ---- ----
04 05 02 01 06 03
04 05 02 01 07 03
04 05 02 01 08 03
04 02 07 01 06 03
04 02 06 01 08 03
04 05 09 01 02 03
04 05 02 01 07 06
04 02 07 01 08 03
04 09 02 01 06 03...... (336870 行受影响)
*/
排序：a1<a2<a3<a4<a5<a6，生成无序的组合，应该都在5秒以内。coleling 的方法生成无序组合应该是最快的，别看用游标，他的方法耗时25分钟，99.99%都消耗在排序上。
花几十分钟排序???
多几秒钟而已:
create table tbtt(id int identity(1,1),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
go
--获取前三列中的所有组合
select * into #t1 from(
select a.a as a1,1 as b1,b.a as a2,1 as b2,c.a as a3,1 as b3 from tbtt a,tbtt b,tbtt c where a.id<b.id and b.id <c.id
union all
select a.a as a1,1 as b1,b.a as a2,1 as b2,null as a3,0 as b3 from tbtt a,tbtt b where a.id<b.id
union all
select a as a1,1 as b1,null as a2,0 as b2,null as a3,0 as b3 from tbtt
)t1,(
select a.b as a4,1 as b4,b.b as a5,1 as b5 from tbtt a,tbtt b where a.id<b.id and a.b is not null and b.b is not null
union all
select b as a4,1 as b4,null as a5,0 as b5 from tbtt where b is not null
)t2,(
select null as a6,0 as b6
union all
select c as a6,1 as b6 from tbtt where c is not null
)t3
--将前三列组合中不足6个数的与第四列组合
select * into #t2 from(
select * from #t1 where b1+b2+b3+b4+b5+b6<6
)t,(
select a7,b7,a8,b8 from(
select 0 as id,null as a7,0 as b7
union all
select id,d as a7,1 as a7 from tbtt where d is not null
)a,(
select 0 as id,null as a8,0 as b8
union all
select id,d as a8,1 as b8 from tbtt where d is not null
)b where a.id<b.id
union all
select null,0,null,0
)t4
--将前四列组合中不足6个数的与第五列组合
select * into #t3 from(
select * from #t2 where b1+b2+b3+b4+b5+b6+b7+b8<6
)t,(
select e as a9,1 as b9 from tbtt where e is not null
union all
select null ,0
)t5
--将前五列组合中不足6个数的与第六列组合
select * into #t4 from(
select * from #t3 where b1+b2+b3+b4+b5+b6+b7+b8+b9<6
)t,(
select f as a10,1 as b10 from tbtt where f is not null
union all
select null,0
)t6
--将前六列组合中不足6个数的与第七列组合
select * into #t5 from(
select * from #t4 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10<6
)t,(
select g as a11,1 as b11 from tbtt where g is not null
union all
select null,0
)t7
--将前七列组合中不足6个数的与第八列组合
select * into #t6 from(
select * from #t5 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11<6 and b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11>2
)t,(
select h as a12,1 as b12 from tbtt where h is not null
union all
select null,0
)t8
--将前八列组合中不足6个数的与第九列组合
select * into #t7 from(
select * from #t6 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12<6 and b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12>3
)t,(
select i as a13,1 as b13 from tbtt where i is not null
union all
select null,0
)t9
--将前九列组合中不足6个数的与第十列组合
select * into #t8 from(
select * from #t7 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13=5
)t,(
select j as a14,1 as b14 from tbtt where j is not null
union all
select null,0
)t10
--删除各表中不满足条件的
delete from #t2 where b1+b2+b3+b4+b5+b6+b7+b8<>6
delete from #t3 where b1+b2+b3+b4+b5+b6+b7+b8+b9<>6
delete from #t4 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10<>6
delete from #t5 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11<>6
delete from #t6 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12<>6
delete from #t7 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13<>6
delete from #t8 where b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12+b13+b14<>6
--合并为6列
declare @t table(a1 varchar(2),a2 varchar(2),a3 varchar(2),a4 varchar(2),a5 varchar(2),a6 varchar(2))
insert into @t
select a1,a2,a3,a4,a5,a6 from #t1 where b1+b2+b3+b4+b5+b6=6
union all
select a1,isnull(a2,a8),isnull(a3,(case when b2=1 then a8 else a7 end)),a4,
isnull(a5,(case when b2+b3=2 then a8 else a7 end)),isnull(a6,isnull(a7,a8))
from #t2
union all
select a1,isnull(a2,a9),isnull(a3,(case when b2=1 then a9 else isnull(a8,a7) end)),a4,
isnull(a5,(case when b2+b3=2 then a9 else (case when b2+b3=1 then isnull(a8,a7) else a7 end) end)),
isnull(a6,isnull(a7,isnull(a8,a9)))
from #t3
union all
select a1,isnull(a2,a10),isnull(a3,(case when b2=1 then a10 else isnull(a9,isnull(a8,a7)) end)),a4,
isnull(a5,(case when b2+b3=2 then a10 else (case when b2+b3=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,a10))))
from #t4
union all
select a1,isnull(a2,a11),isnull(a3,(case when b2=1 then a11 else isnull(a10,isnull(a9,isnull(a8,a7))) end)),a4,
isnull(a5,(case when b2+b3=2 then a11 else (case when b2+b3=1 then isnull(a10,isnull(a9,isnull(a8,a7))) else (case when b10=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,isnull(a10,a11)))))
from #t5
union all
select a1,isnull(a2,a12),isnull(a3,(case when b2=1 then a12 else isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))) end)),a4,
isnull(a5,(case when b2+b3=2 then a12 else (case when b2+b3=1 then isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))) else (case when b11=1 then isnull(a10,isnull(a9,isnull(a8,a7))) else (case when b10=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,isnull(a10,isnull(a11,a12))))))
from #t6
union all
select a1,isnull(a2,a13),isnull(a3,(case when b2=1 then a13 else isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7))))) end)),a4,
isnull(a5,(case when b2+b3=2 then a13 else (case when b2+b3=1 then isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7))))) else (case when b12=1 then isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))) else (case when b11=1 then isnull(a10,isnull(a9,isnull(a8,a7))) else (case when b10=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end) end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,isnull(a10,isnull(a11,isnull(a12,a13)))))))
from #t7
union all
select a1,isnull(a2,a14),isnull(a3,(case when b2=1 then a14 else isnull(a13,isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))))) end)),a4,
isnull(a5,(case when b2+b3=2 then a14 else (case when b2+b3=1 then isnull(a13,isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))))) else (case when b13=1 then isnull(a12,isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7))))) else (case when b12=1 then isnull(a11,isnull(a10,isnull(a9,isnull(a8,a7)))) else (case when b11=1 then isnull(a10,isnull(a9,isnull(a8,a7))) else (case when b10=1 then isnull(a9,isnull(a8,a7)) else (case when b9=1 then isnull(a8,a7) else a7 end) end) end) end) end) end) end)),
isnull(a6,isnull(a7,isnull(a8,isnull(a9,isnull(a10,isnull(a11,isnull(a12,isnull(a13,a14))))))))
from #t8
declare @t1 table(a1 varchar(2),a2 varchar(2),a3 varchar(2),a4 varchar(2),a5 varchar(2),a6 varchar(2))
declare @t2 table(a1 varchar(2),a2 varchar(2),a3 varchar(2),a4 varchar(2),a5 varchar(2),a6 varchar(2))
declare @t3 table(a1 varchar(2),a2 varchar(2),a3 varchar(2),a4 varchar(2),a5 varchar(2),a6 varchar(2))
declare @t4 table(a1 varchar(2),a2 varchar(2),a3 varchar(2),a4 varchar(2),a5 varchar(2),a6 varchar(2))
--取得a1
insert into @t1
select * from @t where a1<a2 and a1<a3 and a1<a4 and a1<a5 and a1<a6
union all
select a2,a1,a3,a4,a5,a6 from @t where a2<a1 and a2<a3 and a2<a4 and a2<a5 and a2<a6
union all
select a3,a1,a2,a4,a5,a6 from @t where a3<a1 and a3<a2 and a3<a4 and a3<a5 and a3<a6
union all
select a4,a1,a2,a3,a5,a6 from @t where a4<a1 and a4<a2 and a4<a3 and a4<a5 and a4<a6
union all
select a5,a1,a2,a3,a4,a6 from @t where a5<a1 and a5<a2 and a5<a3 and a5<a4 and a5<a6
union all
select a6,a1,a2,a3,a4,a5 from @t where a6<a1 and a6<a2 and a6<a3 and a6<a4 and a6<a5
--取得a2
insert into @t2
select * from @t1 where a2<a3 and a2<a4 and a2<a5 and a2<a6
union all
select a1,a3,a2,a4,a5,a6 from @t1 where a3<a2 and a3<a4 and a3<a5 and a3<a6
union all
select a1,a4,a2,a3,a5,a6 from @t1 where a4<a2 and a4<a3 and a4<a5 and a4<a6
union all
select a1,a5,a2,a3,a4,a6 from @t1 where a5<a2 and a5<a3 and a5<a4 and a5<a6
union all
select a1,a6,a2,a3,a4,a5 from @t1 where a6<a2 and a6<a3 and a6<a4 and a6<a5
--取得a3
insert into @t3
select * from @t2 where a3<a4 and a3<a5 and a3<a6
union all
select a1,a2,a4,a3,a5,a6 from @t2 where a4<a3 and a4<a5 and a4<a6
union all
select a1,a2,a5,a3,a4,a6 from @t2 where a5<a3 and a5<a4 and a5<a6
union all
select a1,a2,a6,a3,a4,a5 from @t2 where a6<a3 and a6<a4 and a6<a5
--取得a4
insert into @t4
select * from @t3 where a4<a5 and a4<a6
union all
select a1,a2,a3,a5,a4,a6 from @t3 where a5<a4 and a5<a6
union all
select a1,a2,a3,a6,a4,a5 from @t3 where a6<a4 and a6<a5
--取得a5,a6,并排序输出
select * from(
select * from @t4 where a5<a6
union all
select a1,a2,a3,a4,a6,a5 from @t4 where a6<a5
)t order by a1,a2,a3,a4,a5,a6
go
drop table tbtt,#t1,#t2,#t3,#t4,#t5,#t6,#t7,#t8
/*
a1 a2 a3 a4 a5 a6
---- ---- ---- ---- ---- ----
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...... 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 行受影响)
*/
非常感谢，这个注释很详尽，谢谢你和楼上所有朋友的悉心指导，54 楼 vidor你比SQL还要强大，我的机器执行15秒完成
--原来使用的是SQL 2008 有些语法不兼容，你现在可以试试！
--现在已知表【bt】里有以下记录--A列：04 11 19 20 24 28 取1-3 个数
--B列：01 08 17 25 33 取1-2 个数
--C列：03 13 18 27 取0-1 个数
--D列：06 07 23 29 取0-2 个数
--E列：02 12 取0-1 个数
--F列：15 32 取0-1 个数
--G列：09 16 取0-1 个数
--H列：30 取0-1 个数
--I列：05 10 取0-1 个数
--J列：14 21 22 26 31 取0-1 个数--用SQL如何完成6个数的所有组合，并建立【6组合表】记录这些组合数-- 例如‘04 08 14 25 28 32’就是从
--A列：04 28 取2个数
--B列：08 25 取2个数
--F列：32 取1个数
--J列：14 取1个数完成的组合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 --首先找出在所有列中取数个数的组合
Go
if OBJECT_ID('ta', 'U') is not null
drop table ta
Go
create table ta(i int)
insert ta
select 1 union all select 2 union all select 3Go
if OBJECT_ID('tb', 'U') is not null
drop table tb
Go
create table tb(i int)
insert tb
select 1 union all select 2 Go
if OBJECT_ID('tc', 'U') is not null
drop table tc
Go
create table tc(i int)
insert tc
select 0 union all select 1 Go
if OBJECT_ID('td', 'U') is not null
drop table td
Go
create table td(i int)
insert td
select 0 union all select 1 union all select 2 Go
if OBJECT_ID('te', 'U') is not null
drop table te
Go
create table te(i int)
insert te
select 0 union all select 1Go
if OBJECT_ID('tf', 'U') is not null
drop table tf
Go
create table tf(i int)
insert tf
select 0 union all select 1Go
if OBJECT_ID('tg', 'U') is not null
drop table tg
Go
create table tg(i int)
insert tg
select 0 union all select 1Go
if OBJECT_ID('th', 'U') is not null
drop table th
Go
create table th(i int)
insert th
select 0 union all select 1Go
if OBJECT_ID('ti', 'U') is not null
drop table ti
Go
create table ti(i int)
insert ti
select 0 union all select 1Go
if OBJECT_ID('tj', 'U') is not null
drop table tj
Go
create table tj(i int)
insert tj
select 0 union all select 1Go
if OBJECT_ID('vw_ta_j_Count', 'V') is not null
drop view vw_ta_j_Count
Go
create view vw_ta_j_Count
as
select ta.i a,tb.i b,tc.i c,td.i d, te.i e, tf.i f, tg.i g, th.i h, ti.i i, tj.i j
from ta, tb, tc, td, te, tf, tg, th, ti, tj
where ta.i+tb.i+tc.i+td.i + te.i + tf.i + tg.i + th.i + ti.i + tj.i =6--创建组合值和组合数的对应关系；
Go
if OBJECT_ID('fn_a', 'TF') is not null
drop function fn_a
Go
--select * from dbo.fn_a(3)
create function fn_a(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 1
begin
insert into @t
select CAST(a as varchar)+ ' '
from tbtt
end
else if @i = 2
begin
insert into @t
select CAST(t1.a as varchar) + ' ' + CAST(t2.a as varchar)+ ' '
from (select a from tbtt) t1, (select a from tbtt) t2
where t1.a>t2.a
end
else
begin
insert into @t
select CAST(t1.a as varchar) + ' ' + CAST(t2.a as varchar) + ' ' + CAST(t3.a as varchar)+ ' '
from (select a from tbtt) t1,
(select a from tbtt) t2,
(select a from tbtt) t3
where t1.a>t2.a and t2.a>t3.a
end
return
endGo
if OBJECT_ID('fn_b', 'TF') is not null
drop function fn_b
Go
--select * from dbo.fn_b(1)
create function fn_b(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 1
begin
insert into @t
select CAST(b as varchar)+ ' '
from tbtt
where b is not null
end
else if @i = 2
begin
insert into @t
select CAST(t1.b as varchar) + ' ' + CAST(t2.b as varchar)+ ' '
from (select b from tbtt where b is not null) t1,
(select b from tbtt where b is not null) t2
where t1.b>t2.b
end
return
endGo
if OBJECT_ID('fn_c', 'TF') is not null
drop function fn_c
Go
--select * from dbo.fn_c(0)
create function fn_c(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 1
begin
insert into @t
select CAST(c as varchar)+ ' '
from tbtt
where c is not null
end
else if @i = 0
begin
insert into @t
values ('')
end
return
end
Go
if OBJECT_ID('fn_d', 'TF') is not null
drop function fn_d
Go
--select * from dbo.fn_d(2)
create function fn_d(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 0
begin
insert into @t
values ('')
end
else
if @i = 1
begin
insert into @t
select CAST(d as varchar)+ ' '
from tbtt
where d is not null
end
else if @i = 2
begin
insert into @t
select CAST(t1.d as varchar) + ' ' + CAST(t2.d as varchar)+ ' '
from (select d from tbtt where d is not null) t1,
(select d from tbtt where d is not null) t2
where t1.d>t2.d
end
return
endGo
if OBJECT_ID('fn_e', 'TF') is not null
drop function fn_e
Go
--select * from dbo.fn_e(1)
create function fn_e(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 0
begin
insert into @t
values ('')
end
else
if @i = 1
begin
insert into @t
select CAST(e as varchar)+ ' '
from tbtt
where e is not null
end
return
endGo
if OBJECT_ID('fn_f', 'TF') is not null
drop function fn_f
Go
--select * from dbo.fn_f(1)
create function fn_f(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 0
begin
insert into @t
values ('')
end
else
if @i = 1
begin
insert into @t
select CAST(f as varchar)+ ' '
from tbtt
where f is not null
end
return
endGo
if OBJECT_ID('fn_g', 'TF') is not null
drop function fn_g
Go
--select * from dbo.fn_g(1)
create function fn_g(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 0
begin
insert into @t
values ('')
end
else
if @i = 1
begin
insert into @t
select CAST(g as varchar)+ ' '
from tbtt
where g is not null
end
return
endGo
if OBJECT_ID('fn_h', 'TF') is not null
drop function fn_h
Go
--select * from dbo.fn_h(1)
create function fn_h(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 0
begin
insert into @t
values ('')
end
else
if @i = 1
begin
insert into @t
select CAST(h as varchar)+ ' '
from tbtt
where h is not null
end
return
endGo
if OBJECT_ID('fn_i', 'TF') is not null
drop function fn_i
Go
--select * from dbo.fn_i(1)
create function fn_i(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 0
begin
insert into @t
values ('')
end
else
if @i = 1
begin
insert into @t
select CAST(i as varchar)+ ' '
from tbtt
where i is not null
end
return
end
Go
if OBJECT_ID('fn_j', 'TF') is not null
drop function fn_j
Go
--select * from dbo.fn_j(1)
create function fn_j(
@i integer
) returns @t table(retVal varchar(100))
as
begin
if @i = 0
begin
insert into @t
values ('')
end
else
if @i = 1
begin
insert into @t
select CAST(j as varchar)+ ' '
from tbtt
where j is not null
end
return
endGo
--采用游标的方式将最后的组合结果输出
declare @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 a, b, c, d, e, f, g, h, i, j from vw_ta_j_Count
open currow
fetch next from currow into @anum, @bnum, @cnum, @dnum, @enum, @fnum, @gnum, @hnum, @inum, @jnumdeclare
@result table(val varchar(100))
while @@fetch_status = 0
begin
insert @result
select a.retVal +b.retVal+c.retVal+d.retVal+e.retVal+f.retVal+g.retVal+h.retVal+i.retVal+j.retVal
from dbo.fn_a(@anum) a,
dbo.fn_b(@bnum) b,
dbo.fn_c(@cnum) c,
dbo.fn_d(@dnum) d,
dbo.fn_e(@enum) e,
dbo.fn_f(@fnum) f,
dbo.fn_g(@gnum) g,
dbo.fn_h(@hnum) h,
dbo.fn_i(@inum) i,
dbo.fn_j(@jnum) j
fetch next from currow into @anum, @bnum, @cnum, @dnum, @enum, @fnum, @gnum, @hnum, @inum, @jnum
end
close currow
deallocate currowselect * from @result