22,207
社区成员
发帖
与我相关
我的任务
分享如何用SQL代码 遍历4个号码的组合是否全部包含
---- 【表t1】是全部4个号码的组合 C(15,4)=1365 组，
;WITH t1 AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as No1,
RIGHT('00'+LTRIM(b.rn),2) as No2,
RIGHT('00'+LTRIM(c.rn),2) as No3,
RIGHT('00'+LTRIM(d.rn),2) as No4,
RIGHT('00'+LTRIM(a.rn),2)
+' '+RIGHT('00'+LTRIM(b.rn),2)
+' '+RIGHT('00'+LTRIM(c.rn),2)
+' '+RIGHT('00'+LTRIM(d.rn),2) as NoTextA
FROM t1 a
JOIN t1 b ON a.rn<b.rn
JOIN t1 c ON b.rn<c.rn
JOIN t1 d ON c.rn<d.rn
--- 【表t2】是全部6个号码的组合 C(15,6) =5005组。
;WITH t2 AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as No1,
RIGHT('00'+LTRIM(b.rn),2) as No2,
RIGHT('00'+LTRIM(c.rn),2) as No3,
RIGHT('00'+LTRIM(d.rn),2) as No4,
RIGHT('00'+LTRIM(e.rn),2) as No5,
RIGHT('00'+LTRIM(f.rn),2) as No6,
RIGHT('00'+LTRIM(a.rn),2)
+' '+RIGHT('00'+LTRIM(b.rn),2)
+' '+RIGHT('00'+LTRIM(c.rn),2)
+' '+RIGHT('00'+LTRIM(d.rn),2)
+' '+RIGHT('00'+LTRIM(e.rn),2)
+' '+RIGHT('00'+LTRIM(f.rn),2) as NoTextB
FROM t2 a
JOIN t2 b ON a.rn<b.rn
JOIN t2 c ON b.rn<c.rn
JOIN t2 d ON c.rn<d.rn
JOIN t2 e ON d.rn<e.rn
JOIN t2 f ON e.rn<f.rn
--- 问题：每次从【表t2】6个号码的组合中选取5005 个号码， 如何用SQL代码 遍历【表t1】 4个号码的组合是否全部包含。
...全文
请发表友善的回复…
发表回复
RICHEER COCA 2021-04-25
- 打赏
- 举报
可能是我的表述不是hen清晰，补充如下：
不是找N个数中包含所有M个数组合的情况(N>M),帖子里 T1和T2是2个已知的数据，为了便于学习研究，所以用代码生成表T1和T2.
这次的问题不是找6个数中包含所有4个数组合的情况，因为t1表是已知的数据记录，举例是4个数，也可能是5个数，可以理解为基因携带者，那么在t2里逐一找出这些基因携带者。
例如：
1. t1表里的第一行“01 02 03 04” 与 t2表里的5005行记录 遍历一遍
55条记录是符合“全部4个号码的组合”要求的，简称第一次遍历的结果
2. 接下来t1表里的第2行01 02 03 05 与 t2表里的5005行 也遍历一遍，
这次符合“全部4个号码的组合”要求的有55条记录，与第一次遍历的结果重复的仅仅记录一次，
3. ......
4. ......
以此类推
qq28454 2021-04-25
- 打赏
- 举报
学习，学习
锟斤拷锟斤拷 2021-04-25
- 打赏
- 举报
最后半句说的不对，T1中的结果有不在T2中的前四个数出现的，但是T1的结果肯定完全包含于T2
允许夏天疯狂 2021-04-25
- 打赏
- 举报
文盲老顾 2021-04-25
- 打赏
- 举报
用@max做了下验证，应该#t2就是你要的结果了，@max=48时，我这里用时37秒
declare @max int
set @max=15
;with t as (
select number as rn
from master..spt_values
where type='p' and number between 1 and @max
)
select ROW_NUMBER() over(order by @@rowcount) as rid,a.rn as n1,b.rn as n2,c.rn as n3,d.rn as n4
into #t1
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
select *
into #t2
from #t1
cross apply (
select number as n5
from master..spt_values
where type='p' and number>n4 and number<=@max
) n5
cross apply (
select number as n6
from master..spt_values
where type='p' and number>n5 and number<=@max
) n6
select * from #t2
drop table #t2
drop table #t1锟斤拷锟斤拷 2021-04-25
- 打赏
- 举报
拿你的C(15,4)和C(15,6)举例来说，15选6肯定包含所有15选4的情况，只不过又在后面加了11选2
或者这么说，你T2里的前四个数，没有不在T1中出现的；T1中的结果，没有不在T2中的前四个数出现的。
锟斤拷锟斤拷 2021-04-25
- 打赏
- 举报
而且也不需要distinct，因为虽说是组合，但是你已经排序了，从左到右就是从小到大，你把开头的通配符去掉，只留LIKE B.NoTextA+'%'试试，结果应该一样吧
锟斤拷锟斤拷 2021-04-25
- 打赏
- 举报
....不知道是不是我理解的问题，但是我感觉你要的结果就是T2
文盲老顾 2021-04-25
- 打赏
- 举报
如果你只是找6个数中包含所有4个数组合的情况，不要弄出来两个表后遍历比较，直接用一个表生成第二个表
;with t as (
select number as rn
from master..spt_values
where type='p' and number between 1 and 48
)
select ROW_NUMBER() over(order by @@rowcount) as rid,a.rn as n1,b.rn as n2,c.rn as n3,d.rn as n4
into #t1
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
select *
into #t2
from #t1
cross apply (
select number as n5
from master..spt_values
where type='p' and number>n4 and number<=48
) n5
cross apply (
select number as n6
from master..spt_values
where type='p' and number>n5 and number<=48
) n6
drop table #t2
drop table #t1
RICHEER COCA 2021-04-25
- 打赏
- 举报
原始表t1、t2分别由不同的48个数字组成，实测用时12分31秒（输出12271512 行结果）
文盲老顾 2021-04-25
- 打赏
- 举报
;with t as (
select number as rn
from master..spt_values
where type='p' and number between 1 and 4
)
select ROW_NUMBER() over(order by @@rowcount) as rid,a.rn as n1,b.rn as n2,c.rn as n3,d.rn as n4
into #t1
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
;with t as (
select number as rn
from master..spt_values
where type='p' and number between 1 and 15
)
select ROW_NUMBER() over(order by @@rowcount) as rid,a.rn as n1,b.rn as n2,c.rn as n3,d.rn as n4,e.rn as n5,f.rn as n6
into #t2
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
JOIN t e ON d.rn<e.rn
JOIN t f ON e.rn<f.rn
select a.rid,b.n1,b.n2,b.n3,b.n4,b.n5,b.n6,a.n1,a.n2,a.n3,a.n4
from (
--select MIN(a.rid) as rid,b.n1,b.n2,b.n3,b.n4
select a.rid,b.n1,b.n2,b.n3,b.n4
from #t2 a
cross apply(
select *
from #t1
where n1 in (a.n1,a.n2,a.n3,a.n4,a.n5,a.n6)
and n2 in (a.n1,a.n2,a.n3,a.n4,a.n5,a.n6)
and n3 in (a.n1,a.n2,a.n3,a.n4,a.n5,a.n6)
and n4 in (a.n1,a.n2,a.n3,a.n4,a.n5,a.n6)
) b
--group by b.n1,b.n2,b.n3,b.n4
) a
left join #t2 b on a.rid=b.rid
order by 1,a.n1,a.n2,a.n3,a.n4
drop table #t1
drop table #t2RICHEER COCA 2021-04-25
- 打赏
- 举报
神级
大佬
收益匪浅，学习了。谢谢！结帖文盲老顾 2021-04-25
- 打赏
- 举报
原始表t1、t2分别由不同的48个数字组成，实测用时2分21秒（输出1000个结果）
文盲老顾 2021-04-25
- 打赏
- 举报
-- #t1 为原始t1表，这里取1-48的数字组合，4个数
;with t as (
select number as rn
from master..spt_values
where type='p' and number between 1 and 48
)
select ROW_NUMBER() over(order by @@rowcount) as rid
,a.rn as n1,b.rn as n2,c.rn as n3,d.rn as n4
into #t1
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
-- #t2 为原始t2表，这里取4-51的数字组合，6个数
;with t as (
select number as rn
from master..spt_values
where type='p' and number between 4 and 51
)
select ROW_NUMBER() over(order by @@rowcount) as rid
,a.rn as n1,b.rn as n2,c.rn as n3,d.rn as n4,e.rn as n5,f.rn as n6
into #t2
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
JOIN t e ON d.rn<e.rn
JOIN t f ON e.rn<f.rn
-- #t3 为从t1原始表中提取所有存在的值，即1-48，根据实际情况可能是任意值
select distinct val
into #t3
from #t1
unpivot(val for col in (n1,n2,n3,n4)) p
-- #t4 为原始表t2行转列的结果集，用以分组统计出现次数
select *
into #t4
from #t2
unpivot(val for col in (n1,n2,n3,n4,n5,n6)) p
-- 将#t4中列的值只去#t3中出现的内容，且同一行，出现的值至少达到4次
-- 同1个id，达到4次以上的值统计，即表示该id对应的行，在#t1中有子集
-- 将结果保存至#t5
select rid,COUNT(0) as cnt
into #t5
from #t4
where val in (
select val from #t3
)
group by rid having(COUNT(0)>=4)
-- 根据#t5得到实际原始表t2中有t1子集的项
select top 1000 b.* from #t5 a
left join #t2 b on a.rid=b.rid
order by b.rid
drop table #t1
drop table #t2
drop table #t3
drop table #t4
drop table #t5
weixin_43325036 2021-04-25
- 打赏
- 举报
这个历遍4组数据，逻辑是怎样呀
RICHEER COCA 2021-04-25
- 打赏
- 举报
12#是个很不错的方案，但问题是，大神把T1和T2当成 全集 和 子集的关系了，实际不是的。
表 T1就是T1，切切不要认为是C(M,N)
表 T2就是T2，切切不要认为是C(M,N)
为了方便学习，采用代码生成 两个数据表。1#里生成数据表的语句最好是不动，就当是生成2个表
理解为：
因为t1表是已知的数据记录，举例是4个数，也可能是5个数，可以理解为基因携带者，那么在t2里逐一找出这些基因携带者。
RICHEER COCA 2021-04-25
- 打赏
- 举报
感谢感谢，离成功的答案已经不远了
很抱歉，
我的表述有误，帖子里 T1和T2是2个已知的数据，为了便于学习研究，所以用代码生成表T1和T2.
表 T1就是T1，切切不要认为是C(M,N)
表 T2就是T2，切切不要认为是C(M,N)
不是找N个数中包含所有M个数组合的情况(N>M)
这次的问题不是找6个数中包含所有4个数组合的情况，因为t1表是已知的数据记录，举例是4个数，也可能是5个数，可以理解为基因携带者，那么在t2里逐一找出这些基因携带者。
例如：
1. t1表里的第一行“01 02 03 04” 与 t2表里的5005行记录 遍历一遍
55条记录是符合“全部4个号码的组合”要求的，简称第一次遍历的结果
2. 接下来t1表里的第2行01 02 03 05 与 t2表里的5005行 也遍历一遍，
这次符合“全部4个号码的组合”要求的有55条记录，与第一次遍历的结果重复的仅仅记录一次，
3. ......
4. ......
以此类推
[/quote]
请帮助修改一下代码， 验证 #12 的 t1表 “01 02 03 04” 与 t2表里的5005行记录 遍历一遍有 55条记录
[/quote]
我的表述有误，帖子里 T1和T2是2个已知的数据，为了便于学习研究，所以用代码生成表T1和T2.
表 T1就是T1，切切不要认为是C(M,N)
表 T2就是T2，切切不要认为是C(M,N)
不是找N个数中包含所有M个数组合的情况(N>M)
这次的问题不是找6个数中包含所有4个数组合的情况，因为t1表是已知的数据记录，举例是4个数，也可能是5个数，可以理解为基因携带者，那么在t2里逐一找出这些基因携带者。
例如：
1. t1表里的第一行“01 02 03 04” 与 t2表里的5005行记录 遍历一遍
55条记录是符合“全部4个号码的组合”要求的，简称第一次遍历的结果
2. 接下来t1表里的第2行01 02 03 05 与 t2表里的5005行 也遍历一遍，
这次符合“全部4个号码的组合”要求的有55条记录，与第一次遍历的结果重复的仅仅记录一次，
3. ......
4. ......
以此类推
[/quote]
请帮助修改一下代码， 验证 #12 的 t1表 “01 02 03 04” 与 t2表里的5005行记录 遍历一遍有 55条记录
[/quote]RINK_1 2021-04-25
- 打赏
- 举报
USE [TEST]
GO
---- 【表t1】是设定1--48，提供组合 C(48,4)用
;WITH t1 AS
(
SELECT TOP 48 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
),
---- 【表t2】是设定1--48，提供组合 C(48,6)用
t2 AS
(
SELECT TOP 48 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
),
---- 【表t3】是全部4个号码的组合 C(48,4)=194580 组，
t3 AS
(SELECT RIGHT('00'+LTRIM(a.rn),2) as No1,
RIGHT('00'+LTRIM(b.rn),2) as No2,
RIGHT('00'+LTRIM(c.rn),2) as No3,
RIGHT('00'+LTRIM(d.rn),2) as No4,
RIGHT('00'+LTRIM(a.rn),2)
+' '+RIGHT('00'+LTRIM(b.rn),2)
+' '+RIGHT('00'+LTRIM(c.rn),2)
+' '+RIGHT('00'+LTRIM(d.rn),2) as NoTextA
FROM t1 a
JOIN t1 b ON a.rn<b.rn
JOIN t1 c ON b.rn<c.rn
JOIN t1 d ON c.rn<d.rn
),
--- 【表t4】是全部6个号码的组合 C(48,6) =12271512组。
t4 AS
(SELECT
RIGHT('00'+LTRIM(a.rn),2) as No1,
RIGHT('00'+LTRIM(b.rn),2) as No2,
RIGHT('00'+LTRIM(c.rn),2) as No3,
RIGHT('00'+LTRIM(d.rn),2) as No4,
RIGHT('00'+LTRIM(e.rn),2) as No5,
RIGHT('00'+LTRIM(f.rn),2) as No6,
RIGHT('00'+LTRIM(a.rn),2)
+' '+RIGHT('00'+LTRIM(b.rn),2)
+' '+RIGHT('00'+LTRIM(c.rn),2)
+' '+RIGHT('00'+LTRIM(d.rn),2)
+' '+RIGHT('00'+LTRIM(e.rn),2)
+' '+RIGHT('00'+LTRIM(f.rn),2) as NoTextB
FROM t2 a
JOIN t2 b ON a.rn<b.rn
JOIN t2 c ON b.rn<c.rn
JOIN t2 d ON c.rn<d.rn
JOIN t2 e ON d.rn<e.rn
JOIN t2 f ON e.rn<f.rn),
---- 每一行 NoTextB遍历4个号码的组合NoTextA，如果全部包含在NoTextB里就把这些NoTextB作为运算结果（结果需为不重复记录）
t5 AS
(
select NoTextB
from t4 A
join t3 B on A.NoTextB like '%'+B.NoTextA+'%'
)
SELECT distinct NoTextB FROM t5 order by NoTextB
文盲老顾 2021-04-25
- 打赏
- 举报
可能是我的表述不是hen清晰，补充如下：
不是找N个数中包含所有M个数组合的情况(N>M),帖子里 T1和T2是2个已知的数据，为了便于学习研究，所以用代码生成表T1和T2.
这次的问题不是找6个数中包含所有4个数组合的情况，因为t1表是已知的数据记录，举例是4个数，也可能是5个数，可以理解为基因携带者，那么在t2里逐一找出这些基因携带者。
例如：
1. t1表里的第一行“01 02 03 04” 与 t2表里的5005行记录 遍历一遍
55条记录是符合“全部4个号码的组合”要求的，简称第一次遍历的结果
2. 接下来t1表里的第2行01 02 03 05 与 t2表里的5005行 也遍历一遍，
这次符合“全部4个号码的组合”要求的有55条记录，与第一次遍历的结果重复的仅仅记录一次，
3. ......
4. ......
以此类推
[/quote]
so ，#12不就是这样的结果吗
t1 为n个数的集合，t2为m个数的集合，m>n
且t2必定包含t1的所有可能的扩展
那么，就直接以t1为基础，加上所有可能的扩展，不就是你要的结果了吗
你可以自己测试#12的指令，修改几次max的值看看
RICHEER COCA 2021-04-24
- 打赏
- 举报
USE [TEST]
GO
---- 【表t1】是设定1--48，提供组合 C(48,4)用
;WITH t1 AS
(
SELECT TOP 48 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
),
---- 【表t2】是设定1--48，提供组合 C(48,6)用
t2 AS
(
SELECT TOP 48 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
),
---- 【表t3】是全部4个号码的组合 C(48,4)=194580 组，
t3 AS
(SELECT RIGHT('00'+LTRIM(a.rn),2) as No1,
RIGHT('00'+LTRIM(b.rn),2) as No2,
RIGHT('00'+LTRIM(c.rn),2) as No3,
RIGHT('00'+LTRIM(d.rn),2) as No4,
RIGHT('00'+LTRIM(a.rn),2)
+' '+RIGHT('00'+LTRIM(b.rn),2)
+' '+RIGHT('00'+LTRIM(c.rn),2)
+' '+RIGHT('00'+LTRIM(d.rn),2) as NoTextA
FROM t1 a
JOIN t1 b ON a.rn<b.rn
JOIN t1 c ON b.rn<c.rn
JOIN t1 d ON c.rn<d.rn
),
--- 【表t4】是全部6个号码的组合 C(48,6) =12271512组。
t4 AS
(SELECT
RIGHT('00'+LTRIM(a.rn),2) as No1,
RIGHT('00'+LTRIM(b.rn),2) as No2,
RIGHT('00'+LTRIM(c.rn),2) as No3,
RIGHT('00'+LTRIM(d.rn),2) as No4,
RIGHT('00'+LTRIM(e.rn),2) as No5,
RIGHT('00'+LTRIM(f.rn),2) as No6,
RIGHT('00'+LTRIM(a.rn),2)
+' '+RIGHT('00'+LTRIM(b.rn),2)
+' '+RIGHT('00'+LTRIM(c.rn),2)
+' '+RIGHT('00'+LTRIM(d.rn),2)
+' '+RIGHT('00'+LTRIM(e.rn),2)
+' '+RIGHT('00'+LTRIM(f.rn),2) as NoTextB
FROM t2 a
JOIN t2 b ON a.rn<b.rn
JOIN t2 c ON b.rn<c.rn
JOIN t2 d ON c.rn<d.rn
JOIN t2 e ON d.rn<e.rn
JOIN t2 f ON e.rn<f.rn),
---- 每一行 NoTextB遍历4个号码的组合NoTextA，如果全部包含在NoTextB里就把这些NoTextB作为运算结果（结果需为不重复记录）
t5 AS
(
select NoTextB
from t4 A
join t3 B on A.NoTextB like '%'+B.NoTextA+'%'
)
SELECT distinct NoTextB FROM t5 order by NoTextB
加载更多回复（5）
