如何从K1到K16这16个组里 任意取10个组，保证这10个组的分数相加的和为33，请专家答疑解惑

Question

---> 已知数据  K1到K16这16个组的分数值分别对应在表#tb里

if object_id('[tempdb]..#tb') is not null drop table #tb;
go
create table #tb ([ID] [int] IDENTITY(1,1) NOT NULL primary key,KLASS varchar(10),WValue INT )
go
insert into #tb  -- select * from #tb
select 'k1','3'
union all select 'k2','21'
union all select 'k3','5'
union all select 'k4','2'
union all select 'k5','1'
union all select 'k6','9'
union all select 'k7','10'
union all select 'k8','5'
union all select 'k9','10'
union all select 'k10','2'
union all select 'k11','2'
union all select 'k12','1'
union all select 'k13','5'
union all select 'k14','2'
union all select 'k15','7'
union all select 'k16','16'


从K1到K16这16个组里 任意取10个组，这10个组的分数相加的和为33，

查询的结果是

----------------------------------------
 k1 k3 k4 k5 k7 k8 k10 k11 k12 k14
 k1 k3 k4 k5 k7 k10 k11 k12 k13 k14
 k1 k3 k4 k5 k8 k9 k10 k11 k12 k14
 k1 k3 k4 k5 k8 k10 k11 k12 k13 k15
 k1 k3 k4 k5 k8 k10 k12 k13 k14 k15
 k1 k3 k4 k5 k8 k11 k12 k13 k14 k15
 k1 k4 k5 k7 k8 k10 k11 k12 k13 k14
 k1 k3 k4 k5 k9 k10 k11 k12 k13 k14
 k1 k4 k5 k8 k9 k10 k11 k12 k13 k14
 k1 k3 k5 k8 k10 k11 k12 k13 k14 k15

 请问用SQL脚本 如何完成。

Answer 1

with t as (
select * from #tb where klass not in ('k2','k16')
), x as (
select concat('-','k2' ,'-',t1.klass ,'-',t2.klass ,'-',t3.klass ,'-',t4.klass ,'-','k16','-') cc
 from t t1,t t2,t t3,t t4 
 where t1.id<t2.id 
 and t2.id<t3.id 
 and t3.id<t4.id and 
 t1.wvalue+t2.wvalue+t3.wvalue+t4.wvalue=31),
 c as (
select cc,klass,'c'+cast(row_number() over(partition by cc order by id) as VARCHAR) rn from x ,#tb 
where charindex(concat('-',klass ,'-'),cc)=0)
select c1,c2,c3,c4,c5,c6,c7,c8,c9,c10 from c pivot (max(klass) for rn in (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10)) as z

思路:
16个数字里挑10个数字任意组合,有1099511627776种可能性,
当前16个数字求和为101,需要剔除6个数字之和为101-33=68,
转化为从16个数字里找6个数字之和为68,此时任意排列的可能性有16777216种,
观察数据分布规律,易得最大的两个数字为必选,否则无法满足6个数字为68,
因此问题转化为 14 个数字里挑 4个数字等于68-21-26=31,此时任意排列的可能性有38416种,
如果要按key从小到大排列,此时只有1001种可能性,数据库完全可以支撑计算,所以就有了上面这个sql