22,206
社区成员
发帖
与我相关
我的任务
分享
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)
/*需要的结果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)
*/
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
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
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
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
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
前面代码这里省略,建立条件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)