---- 【表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個號碼的組合是否全部包含。
uj5u.com熱心網友回復：
不是很理解你的需求，不知道是不是下面這樣的，不過性能不好
---- 【表t1】是全部4個號碼的組合 C(15,4)=1365 組，
WITH t1 AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
),
t2 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),
--- 【表t2】是全部6個號碼的組合 C(15,6) =5005組。
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(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 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
JOIN t1 e ON d.rn<e.rn
JOIN t1 f ON e.rn<f.rn)
select *
from t3 A
join t2 B on A.NoTextB like '%'+B.NoTextA+'%'
uj5u.com熱心網友回復：
非常感謝
，結果有實際結果 不應該超過 5005行
學習中
uj5u.com熱心網友回復：
非常感謝
t1表里的第一行01 02 03 04 與 t2表里的5005行 遍歷一遍
1. 這些記錄是符合“全部4個號碼的組合”要求的，簡稱第一次遍歷的結果
2. 接下來t1表里的第2行01 02 03 05 與 t2表里的5005行 也遍歷一遍，這次符合“全部4個號碼的組合”要求的與第一次遍歷的結果重復的僅僅記錄一次，
3. ......
4. ......
以此類推
uj5u.com熱心網友回復：
;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
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
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 #t2
uj5u.com熱心網友回復：
運算結果僅有一條，實際應該是55條（#3樓 是手工驗算的，55條包含有這01 02 03 04）
t1表里的第一行01 02 03 04 與 t2表里的5005行 遍歷一遍，包含有這01 02 03 04的組合數實際有55行
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/279749.html
標籤：疑難問題