超难的一个问题:例子里执行代码可以得到NoText,那么把这些NoText当做条件的话,反过来如何求得原来被看做是条件的值?

RICHEER COCA 2017-07-01 04:50:04

先举个例子:

现在已知表【TTB1】里有以下记录


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 个数
[/code]
取这些列的个数的总和为6个数的所有组合,并且结果格式为
/*
notext
----------------------
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)

*/

执行以下代码可以得到结果

if object_id('TTB1','U') is not null
drop table TTB1
go

--建表,插入演示数据 select a as [取1-2 个数], b as [取0-1 个数], c as [取0-2 个数], d as [取0-1 个数], e as [取0-1 个数], f as [取0-1 个数], g as [取0-1 个数], h as [取0-1 个数], i as [取0-1 个数], j as [取0-1 个数] from TTB1
create table TTB1(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 TTB1
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('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 = 6

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 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 TTB1 a union all
select num=2,array=a.a+','+b.a+',' from TTB1 a,(select a from TTB1) b where a.a > b.a union all
select num=3,array=a.a+','+b.a+','+c.a+',' from TTB1 a,(select a from TTB1) b,(select a from TTB1) c where a.a > b.a and b.a > c.a
) a, (
select num=1,array=a.b+',' from TTB1 a where a.b is not null union all
select num=2,array=a.b+','+b.b+',' from TTB1 a,(select b from TTB1) b where a.b > b.b
) b, (
select num=0,array='' union all
select num=1,array=a.c+',' from TTB1 a where a.c is not null
) c, (
select num=0,array='' union all
select num=1,array=a.d+',' from TTB1 a where a.d is not null union all
select num=2,array=a.d+','+b.d+',' from TTB1 a,(select d from TTB1) b where a.d > b.d
) d, (
select num=0,array='' union all
select num=1,array=a.e+',' from TTB1 a where a.e is not null
) e, (
select num=0,array='' union all
select num=1,array=a.f+',' from TTB1 a where a.f is not null
) f, (
select num=0,array='' union all
select num=1,array=a.g+',' from TTB1 a where a.g is not null
) g, (
select num=0,array='' union all
select num=1,array=a.h+',' from TTB1 a where a.h is not null
) h, (
select num=0,array='' union all
select num=1,array=a.i+',' from TTB1 a where a.i is not null
) i, (
select num=0,array='' union all
select num=1,array=a.j+',' from TTB1 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 currow

select dbo.OrderString(array) from @result order by dbo.OrderString(array)



需要解决的问题描述:

如果提供表TBB2里有一列NoText以下这些记录
/*
notext
----------------------
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)

*/

请问:
现在需要用代码求得【resultTBB1】里的记录,简单地说,就是把例子的条件和结果倒置,现在把例子里的结果数据作为条件,通过SQL代码把例子里的@result里的数据用代码求得例子里的【TTB1】,
请问如何实现。

/*需要的结果resultTBB1


取1-2 个数 取0-1 个数 取0-2 个数 取0-1 个数 取0-1 个数 取0-1 个数 取0-1 个数 取0-1 个数 取0-1 个数 取0-1 个数
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
04 01 03 06 02 15 09 30 05 14
11 08 13 07 12 32 16 NULL 10 21
19 17 18 23 NULL NULL NULL NULL NULL 22
20 25 27 29 NULL NULL NULL NULL NULL 26
24 33 NULL NULL NULL NULL NULL NULL NULL 31
28 NULL NULL NULL NULL NULL NULL NULL NULL NULL

(6 row(s) affected)

*/


...全文
335 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
RICHEER COCA 2017-07-04
  • 打赏
  • 举报
回复
引用 4 楼 sinat_28984567 的回复:
简化一下问题……把测试数据和想要的结果描述出来,测试数据可以少点,比如实际15字段,测试可以5个字段,然后把5个字段对应的结果写出来就行了

if object_id('TBB2','U') is not null
    drop table TBB2  -- select * from TBB2

	--建表,插入演示数据
CREATE TABLE [dbo].[TBB2](notext varchar(20))
insert TBB2 
select '01 02 03 04 06 09' union all
select '01 02 03 04 06 11' union all
select '01 02 03 04 06 15' union all
select '01 02 03 04 09 11' union all
select '01 02 03 04 09 15' union all
select '01 02 03 04 11 15' union all
select '01 02 03 06 09 11' union all
select '01 02 03 06 11 15' union all
select '01 02 03 09 11 15' union all
select '01 02 04 06 09 11' union all
select '01 02 04 06 09 13' union all
select '01 02 04 06 09 15' union all
select '01 02 04 06 11 13' union all
select '01 02 04 06 11 15' union all
select '01 02 04 06 13 15' union all
select '01 02 04 09 11 13' union all
select '01 02 04 09 11 15' union all
select '01 02 04 09 13 15' union all
select '01 02 04 11 13 15' union all
select '01 02 06 09 11 13' union all
select '01 02 06 09 11 15' union all
select '01 02 06 11 13 15' union all
select '01 02 09 11 13 15' union all
select '01 03 04 06 09 11' union all
select '01 03 04 06 09 15' union all
select '01 03 04 06 11 15' union all
select '01 03 04 09 11 15' union all
select '01 03 06 09 11 15' union all
select '01 04 06 09 11 13' union all
select '01 04 06 09 11 15' union all
select '01 04 06 09 13 15' union all
select '01 04 06 11 13 15' union all
select '01 04 09 11 13 15' union all
select '01 06 09 11 13 15'     
GO
需要的就果

a	b	c	d	e	f	g
04	01	03	06	02	15	09	
11	NULL	13	NULL	NULL	NULL	NULL
其中 a 表示 [取1-2 个数] 其中 b 表示 [取0-1 个数] 其中 c 表示 [取0-2 个数] 其中 d 表示 [取0-1 个数] 其中 e 表示 [取0-1 个数] 其中 f 表示 [取0-1 个数] 其中 g 表示 [取0-1 个数] 其中 h 表示 [取0-1 个数] 其中 i 表示 [取0-1 个数] 其中 j 表示 [取0-1 个数] 例如 '01 04 06 11 13 15'’就是从 A列:04 11 取2个数 B列:01 取1个数 C列:13 取1个数 D列:06 取1个数 F列:15 取1个数 取这些列的个数的总和为6个的组合记录。 那么如何用代码完成对测试数据TBB2的统计结果为

a	b	c	d	e	f	g
04	01	03	06	02	15	09	
11	NULL	13	NULL	NULL	NULL	NULL
RICHEER COCA 2017-07-04
  • 打赏
  • 举报
回复
引用 7 楼 wyj23114 的回复:
这东西要么不可逆,要么逆向会得出N种结果,仅供参考


select vl,case rownum when 1 then 'c' when 3 then 'a' when 4 then 'b' else char(99+_rownum) end  from 表名 order by rownum,vl
请问

select vl,case rownum when 1 then 'c' when 3 then 'a' when 4 then 'b' else char(
103   ---这个数字代表什么意思?改变后 出现的A B C....H I J为何是有变化的,请教
+_rownum) end  from ct3 order by rownum,vl
RICHEER COCA 2017-07-04
  • 打赏
  • 举报
回复
引用 7 楼 wyj23114 的回复:
这东西要么不可逆,要么逆向会得出N种结果,仅供参考

with c1 as(
select *,a=substring(txt,1,2),b=substring(txt,4,2),c=substring(txt,7,2),d=substring(txt,10,2),e=substring(txt,13,2),f=substring(txt,16,2) from tb t
)
,c2 as(
select * from(
select vl=a from c1 union all
select b from c1 union all
select c from c1 union all
select d from c1 union all
select e from c1 union all
select f from c1 
)t
),cte as(select top 99.999999 percent   vl,count(1) ct from c2 group by vl order by count(1) desc)
select vl,case rownum when 1 then 'c' when 3 then 'a' when 4 then 'b' else char(99+_rownum) end  from (select *,_rownum=ROW_NUMBER()over(partition by ct order by vl),rownum=DENSE_RANK()over(order by ct) from cte )t order by rownum,vl
执行结果

4	11	1	3	13	2	6	9	15
a	a	b	c	c	d	e	f	g

请问,如何得到最终的结果格式

a    b    c    d    e    f    g
04    01    03    02    06     09    15   
11    NULL    13    NULL    NULL    NULL    NULL
RICHEER COCA 2017-07-04
  • 打赏
  • 举报
回复
引用 6 楼 sinat_28984567 的回复:
其中 a 表示 [取1-2 个数] 是取notext列中的1-2个数?notext很多条,怎么取?
这也是我考虑到了,用语言表述困难,所以就举了一个事例,只要根据实例的结果去找出条件值就是答案。 请问大神 sinat_28984567,楼主在#2楼的所述,能实现吗?
伤痕累累 2017-07-04
  • 打赏
  • 举报
回复
这东西要么不可逆,要么逆向会得出N种结果,仅供参考

with c1 as(
select *,a=substring(txt,1,2),b=substring(txt,4,2),c=substring(txt,7,2),d=substring(txt,10,2),e=substring(txt,13,2),f=substring(txt,16,2) from tb t
)
,c2 as(
select * from(
select vl=a from c1 union all
select b from c1 union all
select c from c1 union all
select d from c1 union all
select e from c1 union all
select f from c1 
)t
),cte as(select top 99.999999 percent   vl,count(1) ct from c2 group by vl order by count(1) desc)
select vl,case rownum when 1 then 'c' when 3 then 'a' when 4 then 'b' else char(99+_rownum) end  from (select *,_rownum=ROW_NUMBER()over(partition by ct order by vl),rownum=DENSE_RANK()over(order by ct) from cte )t order by rownum,vl
二月十六 2017-07-04
  • 打赏
  • 举报
回复
其中 a 表示 [取1-2 个数] 是取notext列中的1-2个数?notext很多条,怎么取?
RICHEER COCA 2017-07-03
  • 打赏
  • 举报
回复
引用 2 楼 u011709039 的回复:
先举个例子: 现在已知表【TTB1】里有以下记录

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 个数
取这些列的个数的总和为6个数的所有组合,并且结果格式为 /* notext ---------------------- 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) */ 执行以下代码可以得到 取这些列的个数的总和为6个数的所有组合

if object_id('TTB1','U') is not null
    drop table TTB1
go
 
--建表,插入演示数据 select a as [取1-2 个数], b as [取0-1 个数], c as [取0-2 个数], d as [取0-1 个数], e as [取0-1 个数], f as [取0-1 个数], g as [取0-1 个数], h as [取0-1 个数], i as [取0-1 个数], j as [取0-1 个数] from TTB1
create table TTB1(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 TTB1
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('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 = 6

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 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 TTB1 a union all
        select num=2,array=a.a+','+b.a+',' from TTB1 a,(select a from TTB1) b where a.a > b.a union all
        select num=3,array=a.a+','+b.a+','+c.a+',' from TTB1 a,(select a from TTB1) b,(select a from TTB1) c where a.a > b.a and b.a > c.a
    ) a, (
        select num=1,array=a.b+',' from TTB1 a where a.b is not null union all
        select num=2,array=a.b+','+b.b+',' from TTB1 a,(select b from TTB1) b where a.b > b.b
    ) b, (
        select num=0,array='' union all
        select num=1,array=a.c+',' from TTB1 a where a.c is not null
    ) c, (
        select num=0,array='' union all
        select num=1,array=a.d+',' from TTB1 a where a.d is not null union all
        select num=2,array=a.d+','+b.d+',' from TTB1 a,(select d from TTB1) b where a.d > b.d
    ) d, (
        select num=0,array='' union all
        select num=1,array=a.e+',' from TTB1 a where a.e is not null
    ) e, (
        select num=0,array='' union all
        select num=1,array=a.f+',' from TTB1 a where a.f is not null
    ) f, (
        select num=0,array='' union all
        select num=1,array=a.g+',' from TTB1 a where a.g is not null
    ) g, (
        select num=0,array='' union all
        select num=1,array=a.h+',' from TTB1 a where a.h is not null
    ) h, (
        select num=0,array='' union all
        select num=1,array=a.i+',' from TTB1 a where a.i is not null
    ) i, (
        select num=0,array='' union all
        select num=1,array=a.j+',' from TTB1 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 currow

---  因为 @result里数据有(336870 row(s) affected),以下代码可以得到需要的条件TBB2
if object_id('TTB1','U') is not null
    drop table TBB2
CREATE TABLE [dbo].[TBB2](notext varchar(20))
insert TBB2
 

select dbo.OrderString(array) from @result order by dbo.OrderString(array)
需要解决的问题描述: 如果提供以下这些记录

notext
----------------------
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) 请问: 现在需要用代码求得【resultTBB2】里的记录,简单地说,就是把例子的条件和结果倒置,把例子里的结果数据作为条件,通过例子里的@result用代码求得例子里的【TTB1】, 请问如何实现。 结果表resultTBB2,以下是需要得到的
取1-2 个数	取0-1 个数	取0-2 个数	取0-1 个数	取0-1 个数	取0-1 个数	取0-1 个数	取0-1 个数	取0-1 个数	取0-1 个数
04	01	03	06	02	15	09	30	05	14
11	08	13	07	12	32	16	NULL	10	21
19	17	18	23	NULL	NULL	NULL	NULL	NULL	22
20	25	27	29	NULL	NULL	NULL	NULL	NULL	26
24	33	NULL	NULL	NULL	NULL	NULL	NULL	NULL	31
28	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
以上是完整的表述 楼主试着写了几天的代码,但毫无进展,对于楼主实属难题啊,难道也把大佬们给难住了
二月十六 2017-07-03
  • 打赏
  • 举报
回复
引用 3 楼 u011709039 的回复:
以上是完整的表述 楼主试着写了几天的代码,但毫无进展,对于楼主实属难题啊,难道也把大佬们给难住了
楼主我觉得可以简化一下问题……把测试数据和想要的结果描述出来,测试数据可以少点,比如实际15字段,测试可以5个字段,然后把5个字段对应的结果写出来就行了……楼主这个太长,没信心能看完……
RICHEER COCA 2017-07-01
  • 打赏
  • 举报
回复
先举个例子: 现在已知表【TTB1】里有以下记录

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 个数
取这些列的个数的总和为6个数的所有组合,并且结果格式为 /* notext ---------------------- 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) */ 执行以下代码可以得到 取这些列的个数的总和为6个数的所有组合

if object_id('TTB1','U') is not null
    drop table TTB1
go
 
--建表,插入演示数据 select a as [取1-2 个数], b as [取0-1 个数], c as [取0-2 个数], d as [取0-1 个数], e as [取0-1 个数], f as [取0-1 个数], g as [取0-1 个数], h as [取0-1 个数], i as [取0-1 个数], j as [取0-1 个数] from TTB1
create table TTB1(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 TTB1
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('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 = 6

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 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 TTB1 a union all
        select num=2,array=a.a+','+b.a+',' from TTB1 a,(select a from TTB1) b where a.a > b.a union all
        select num=3,array=a.a+','+b.a+','+c.a+',' from TTB1 a,(select a from TTB1) b,(select a from TTB1) c where a.a > b.a and b.a > c.a
    ) a, (
        select num=1,array=a.b+',' from TTB1 a where a.b is not null union all
        select num=2,array=a.b+','+b.b+',' from TTB1 a,(select b from TTB1) b where a.b > b.b
    ) b, (
        select num=0,array='' union all
        select num=1,array=a.c+',' from TTB1 a where a.c is not null
    ) c, (
        select num=0,array='' union all
        select num=1,array=a.d+',' from TTB1 a where a.d is not null union all
        select num=2,array=a.d+','+b.d+',' from TTB1 a,(select d from TTB1) b where a.d > b.d
    ) d, (
        select num=0,array='' union all
        select num=1,array=a.e+',' from TTB1 a where a.e is not null
    ) e, (
        select num=0,array='' union all
        select num=1,array=a.f+',' from TTB1 a where a.f is not null
    ) f, (
        select num=0,array='' union all
        select num=1,array=a.g+',' from TTB1 a where a.g is not null
    ) g, (
        select num=0,array='' union all
        select num=1,array=a.h+',' from TTB1 a where a.h is not null
    ) h, (
        select num=0,array='' union all
        select num=1,array=a.i+',' from TTB1 a where a.i is not null
    ) i, (
        select num=0,array='' union all
        select num=1,array=a.j+',' from TTB1 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 currow

---  因为 @result里数据有(336870 row(s) affected),以下代码可以得到需要的条件TBB2
if object_id('TTB1','U') is not null
    drop table TBB2
CREATE TABLE [dbo].[TBB2](notext varchar(20))
insert TBB2
 

select dbo.OrderString(array) from @result order by dbo.OrderString(array)
需要解决的问题描述: 如果提供以下这些记录

notext
----------------------
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) 请问: 现在需要用代码求得【resultTBB2】里的记录,简单地说,就是把例子的条件和结果倒置,把例子里的结果数据作为条件,通过例子里的@result用代码求得例子里的【TTB1】, 请问如何实现。 结果表resultTBB2,以下是需要得到的
取1-2 个数	取0-1 个数	取0-2 个数	取0-1 个数	取0-1 个数	取0-1 个数	取0-1 个数	取0-1 个数	取0-1 个数	取0-1 个数
04	01	03	06	02	15	09	30	05	14
11	08	13	07	12	32	16	NULL	10	21
19	17	18	23	NULL	NULL	NULL	NULL	NULL	22
20	25	27	29	NULL	NULL	NULL	NULL	NULL	26
24	33	NULL	NULL	NULL	NULL	NULL	NULL	NULL	31
28	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
RICHEER COCA 2017-07-01
  • 打赏
  • 举报
回复
补充代码
    
前面代码这里省略,建立条件TBB的话2需要补上
......
......
fetch next from currow into @anum, @bnum, @cnum, @dnum, @enum, @fnum, @gnum, @hnum, @inum, @jnum
end
close currow
deallocate currow

---  因为 @result里数据有(336870 row(s) affected),以下代码可以得到需要的条件TBB2
if object_id('TTB1','U') is not null
    drop table TBB2
CREATE TABLE [dbo].[TBB2](notext varchar(20))
insert TBB2
 

select dbo.OrderString(array) from @result order by dbo.OrderString(array)

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧