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 '03', '02', '05', '01', '04', '11', null, '13', '23', '22' union all
select '09', '10', '06', '07', '08', '20', null, '27', '29', '30' union all
select '17', '12', '15', '31', '14', null, null, null, null, null union all
select '21', '18', '16', null, '25', null, null, null, null, null union all
select '26', '24', '19', null, '28', null, null, null, null, null union all
select '32', '33', null, null, null, null, null, null, null, null
GO
---select * from tbtt
if object_id('OrderString','FN') is not null
drop function OrderString
go--建立函数,功能是把类似于'2,1,3'的字符串转成'1 2 3'
CREATE FUNCTION OrderString(@s VARCHAR(8000)) --drop FUNCTION OrderString
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), ---可以取1-2 个数, 包含了‘09’
t2 as(select 1 num union all select 2), ---可以取1-2 个数
t3 as(select 1 num union all select 2), ---可以取1-2 个数,包含了‘15’,‘16’
t4 as(select 0 num union all select 1 union all select 2), ---可以取0-2 个数
t5 as(select 0 num union all select 1 union all select 2), ---可以取0-2 个数, 包含了‘28’
t6 as(select 0 num union all select 1), ---可以取0-1 个数
t7 as(select 0 num ), ---可以取0 个数
t8 as(select 0 num union all select 1 ), ---可以取0-1 个数, 包含了‘27’
t9 as(select 0 num union all select 1 ), ---可以取0-1 个数
t10 as(select 0 num union all select 1 ) ---可以取0-1 个数, 包含了‘22’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 where a.a is not null 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=1,array=a.c+',' from tbtt a where a.c is not null union all
select num=2,array=a.c+','+b.c+',' from tbtt a,(select c from tbtt) b where a.c > b.c
) 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 union all
select num=2,array=a.e+','+b.e+',' from tbtt a,(select e from tbtt) b where a.e > b.e
) 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=''
) 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) array into result999 from @result order by dbo.OrderString(array)
select * from result999 where array='09 15 16 22 27 28' ---应该有这组数,可怎么找不到?脚本的问题在哪里?
array
-------------
(0 行受影响)
奇怪了,应该有这组数,可怎么找不到?脚本的问题在哪里?
;with
t1 as(select 1 num union all select 2 union all select 3), ---可以取1-3 个数, 包含了‘09’
select replace('2,1,3',',',' ')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 1 3(1 行受影响)*/
09 15 16 22 23 24
09 15 16 22 23 33
09 15 16 22 24 25
09 15 16 22 24 26
09 15 16 22 24 27
09 15 16 22 24 28
09 15 16 22 24 29
09 15 16 22 24 31
09 15 16 22 24 32
09 15 16 22 24 33
09 15 16 22 25 33
09 15 16 22 26 33
09 15 16 22 27 33
09 15 16 22 28 33
09 15 16 22 29 33
09 15 16 22 31 33
09 15 16 22 32 33
09 15 16 23 24 25
09 15 16 23 24 26
09 15 16 23 24 27
09 15 16 23 24 28
没有错啊,关键是执行后怎么result999 里没有这组数'09 15 16 22 27 28',肯定脚本的问题,但我琢磨不出来
CREATE FUNCTION OrderString(@s VARCHAR(8000)) --drop FUNCTION OrderString
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @ret VARCHAR(1000)
SET @ret = ''
.....
.....这没有问题,LZ试了一下
select * from result134 where array='09 15 16 22 27 33'
执行结果
array
---------------------
09 15 16 22 27 33(1 行受影响)可见result999 里没有这组数的结果'09 15 16 22 27 28',肯定脚本的问题,但我琢磨不出来
09 15 16 22 23 24 ---仅仅取了以‘09’为首的数据显示作为说明
09 15 16 22 23 33
09 15 16 22 24 25
09 15 16 22 24 26
09 15 16 22 24 27
09 15 16 22 24 28
09 15 16 22 24 29
09 15 16 22 24 31
09 15 16 22 24 32
09 15 16 22 24 33
09 15 16 22 25 33
09 15 16 22 26 33
09 15 16 22 27 33
09 15 16 22 28 33
09 15 16 22 29 33
09 15 16 22 31 33
09 15 16 22 32 33
09 15 16 23 24 25
09 15 16 23 24 26
09 15 16 23 24 27
09 15 16 23 24 28
......
其中
......
array='09 15 16 22 27 28' ---是取了a列的'09'、c列'15 16'、e列'28'、h列'27'、j列'22',可以不管b列的数据,在296250条记录里应该有'09 15 16 22 27 28' ,可实际运行脚本后却没有,肯定脚本的问题,但我琢磨不出来
......等待指点,谢谢
09可能转成9了,少了一位,
试一下,加个ltrim
substring(ltrim(a.array)+ltrim(b.array)+ltrim(c.array)+ltrim(d.array)+ltrim(e.array)+ltrim(f.array)+ltrim(g.array)+ltrim(h.array)+ltrim(i.array)+ltrim(j.array),1,17)
--“取了a列的'09'、c列'15 16'、e列'28'、h列'27'、j列'22',可以不管b列的数据”--什么叫做可以不管b列的数据?那要下面这句干什么
t2 as(select 1 num union all select 2), ---可以取1-2 个数--如果b列可以不管的话,那就应该改成:
t2 as(select 0 union all select 1 num union all select 2), ---可以取0-2 个数
--还有这里也改一下:
select num=0,array='' union all
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列的数据没有,也不会影响有'09 15 16 22 27 28' 这组数的打印结果,简单的描述可以表达如下
--现在已知表【tbbt】里有以下记录--A列:03 09 17 21 26 32 取1-3 个数
--B列:02 10 12 18 24 33 取1-2 个数
--C列:05 06 15 16 19 取1-2 个数
--D列:01 07 31 取0-2 个数
--E列:04 08 14 25 28 取0-2 个数
--F列:11 20 取0-1 个数
--G列: 取0 个数
--H列:13 27 取0-1 个数
--I列:23 29 取0-1 个数
--J列:22 30 取0-1 个数--用SQL完成6个数的所有组合,并建立【array】记录这些组合数-- 例如‘09 15 16 22 27 28’就是从
--A列:09 取了1个数
--C列:15 16 取了2个数
--E列:28 取了1个数
--H列:27 取了1个数
--J列:22 取了1个数完成的组合----问题是执行脚本后,没有‘09 15 16 22 27 28’, 肯定是我的问题,但我琢磨不出来,请指点迷津,谢谢
--现在已知表【tbbt】里有以下记录--A列:03 09 17 21 26 32 取1-3 个数
--B列:02 10 12 18 24 33 取1-2 个数 ---改为‘取0-2 个数’
--C列:05 06 15 16 19 取1-2 个数
--D列:01 07 31 取0-2 个数
--E列:04 08 14 25 28 取0-2 个数
--F列:11 20 取0-1 个数
--G列: 取0 个数
--H列:13 27 取0-1 个数
--I列:23 29 取0-1 个数
--J列:22 30 取0-1 个数--用SQL完成6个数的所有组合,并建立【array】记录这些组合数-- 那么‘09 15 16 22 27 28’就是合法的6位数
谢谢10楼的coleling
高人!!!!!!
t2 as(select 0 union all select 1 num union all select 2), ---可以取0-2 个数
--还有这里也改一下:
select num=0,array='' union all
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
结贴,给分吧,呵呵!