--> 建立数据表#tb1 (实际数据有7000多)
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
go
create table #tb1(ID [int] IDENTITY(1,1) NOT NULL,notext varchar(20),no1 int,no2 int,no3 int,no4 int,no5 int,no6 int,blu int)
insert #tb1
select '10 11 15 26 31 32','10','11','15','26','31','32','06' union all
select '10 11 12 22 31 32','10','11','12','22','31','32','07' union all
select '03 11 26 31 32 33','03','11','26','31','32','33','08' union all
select '10 11 15 23 25 31','10','11','15','23','25','31','03' union all
select '11 16 23 26 31 32','11','16','23','26','31','32','07' union all
select '08 10 13 15 26 31','08','10','13','15','26','31','15' --- select * from #tb1
--> 建立数据表#tb2 (实际数据有100多万)
if object_id('tempdb.dbo.#tb2') is not null drop table #tb2
go
create table #tb2(ID [int] IDENTITY(1,1) NOT NULL,notext varchar(20),no1 int,no2 int,no3 int,no4 int,no5 int,no6 int,blu int,site varchar(12))
insert #tb2
select '10 11 15 26 31 32','10','11','15','26','31','32','06','taobao' union all
select '10 11 15 26 28 32','10','11','15','26','28','32','11','taobao' union all
select '10 11 15 21 26 31','10','11','15','21','26','31','09','taobao' union all
select '06 10 15 26 31 32','06','10','15','26','31','32','01','taobao' union all
select '02 10 11 15 31 32','02','10','11','15','31','32','07','taobao' union all
select '10 11 17 26 31 32','10','11','17','26','31','32','12','taobao' union all
select '10 11 24 26 31 32','10','11','24','26','31','32','12','taobao' union all
select '10 11 25 26 31 32','10','11','25','26','31','32','12','taobao' union all
select '10 11 15 21 26 31','10','11','15','21','26','31','09','taobao' union all
select '10 11 15 25 26 32','10','11','15','25','26','32','12','lecai' union all
select '02 10 11 26 31 32','02','10','11','26','31','32','09','cpbao' union all
select '10 11 19 26 31 32','10','11','19','26','31','32','09','cpbao' union all
select '10 11 26 31 32 33','10','11','26','31','32','33','09','cpbao' union all
select '10 11 15 23 26 31','10','11','15','23','26','31','03','cpbao' union all
select '03 04 05 26 31 32','03','04','05','26','31','32','07','cpbao' union all
select '01 02 09 19 26 31','01','02','09','19','26','31','15','cp163' --- select * from #tb2
--找出#tb1表里 与 #tb2表里有5个或6个号码相同的每注号码
--问题：用以下语句仅仅找出#tb1表里第一组号码 与 #tb2表里有5个或6个号码相同的每一注号码，
--请问如何修改代码，找出#tb1表里每一组号码 与 #tb2表里有5个或6个号码相同的所有的号码？
select top 1 id,notext,No1,No2,No3,No4,No5,No6 into #tb3 from #tb1 --取 #tb1表里第一组号码，如果全部取就是得到错误的结果
;WITH CTE AS ( select QTY from #tb1 unpivot (QTY for TYPE in(No1,No2,No3,No4,No5,No6)) as p ) --这里无法依次取到#tb1表里每一组号码
Select ID as RRID,notext,
((Case when NO1 in (Select * from CTE ) then 1 else 0 end)+
(Case when NO2 in (Select * from CTE ) then 1 else 0 end)+
(Case when NO3 in (Select * from CTE ) then 1 else 0 end)+
(Case when NO4 in (Select * from CTE ) then 1 else 0 end)+
(Case when No5 in (Select * from CTE ) then 1 else 0 end)+
(Case when NO6 in (Select * from CTE ) then 1 else 0 end)) as RRpNumber Into #RRepTable
From #tb2
-- 找6个相同号码
Select notext,blu From #tb2 Where
ID in (Select ID From #RRepTable left join #tb2 on #RRepTable.RRID= #tb2.ID Where 1=1
and (RRpNumber between 6 and 6))
union all
----- 找5个相同号码
Select notext,blu From #tb2 Where
ID in (Select ID From #RRepTable left join #tb2 on #RRepTable.RRID= #tb2.ID Where 1=1
and (RRpNumber between 5 and 5))
drop table #tb1 drop table #tb2 drop table #tb3 drop table #RRepTable
--请问如何修改代码，找出#tb1表里每一组号码 与 #tb2表里有5个或6个相同号码的所有的结果？
------解决思路----------------------
将6组数字转换成bit值，很简单的比较。
原创，求推荐。
DECLARE @base bigint
SET @base = 2
SELECT POWER(@base,number) value
INTO #1bit
FROM master..spt_values
WHERE type = 'p'
AND number < 36 -- 最大可选数
SELECT p1.value
------解决思路----------------------
p2.value value
INTO #2bitDiff
FROM #1bit p1
JOIN #1bit p2
ON p1.value <> p2.value
SELECT ID,
POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb1x
FROM #tb1
SELECT ID,
POWER(@base,no1-1) +
POWER(@base,no2-1) +
POWER(@base,no3-1) +
POWER(@base,no4-1) +
POWER(@base,no5-1) +
POWER(@base,no6-1) AS value
INTO #tb2x
FROM #tb2
-- 找6个相同号码
SELECT #tb1x.ID ID1,
#tb2x.ID ID2
INTO #6same
FROM #tb2x
JOIN #tb1x
ON #tb2x.value = #tb1x.value
SELECT #tb2.*
FROM #6same
JOIN #tb2
ON #tb2.ID = #6same.ID2
-- 找5个相同号码，异或有2个bit为1
SELECT #tb1x.ID ID1,
#tb2x.ID ID2
INTO #5same
FROM #tb2x
JOIN #tb1x
ON (#tb2x.value ^ #tb1x.value) IN (SELECT value FROM #2bitDiff)
SELECT #tb1.notext,
#tb2.*
FROM #5same
JOIN #tb1
ON #tb1.ID = #5same.ID1
JOIN #tb2
ON #tb2.ID = #5same.ID2
ID notext no1 no2 no3 no4 no5 no6 blu site
----------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------
1 10 11 15 26 31 32 10 11 15 26 31 32 6 taobao
notext ID notext no1 no2 no3 no4 no5 no6 blu site
-------------------- ----------- -------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------
10 11 15 26 31 32 2 10 11 15 26 28 32 10 11 15 26 28 32 11 taobao
10 11 15 26 31 32 3 10 11 15 21 26 31 10 11 15 21 26 31 9 taobao
10 11 15 26 31 32 4 06 10 15 26 31 32 6 10 15 26 31 32 1 taobao
10 11 15 26 31 32 5 02 10 11 15 31 32 2 10 11 15 31 32 7 taobao
10 11 15 26 31 32 6 10 11 17 26 31 32 10 11 17 26 31 32 12 taobao
10 11 15 26 31 32 7 10 11 24 26 31 32 10 11 24 26 31 32 12 taobao
10 11 15 26 31 32 8 10 11 25 26 31 32 10 11 25 26 31 32 12 taobao
10 11 15 26 31 32 9 10 11 15 21 26 31 10 11 15 21 26 31 9 taobao
10 11 15 26 31 32 10 10 11 15 25 26 32 10 11 15 25 26 32 12 lecai
10 11 15 26 31 32 11 02 10 11 26 31 32 2 10 11 26 31 32 9 cpbao
10 11 15 26 31 32 12 10 11 19 26 31 32 10 11 19 26 31 32 9 cpbao
10 11 15 26 31 32 13 10 11 26 31 32 33 10 11 26 31 32 33 9 cpbao
03 11 26 31 32 33 13 10 11 26 31 32 33 10 11 26 31 32 33 9 cpbao
10 11 15 23 25 31 14 10 11 15 23 26 31 10 11 15 23 26 31 3 cpbao
10 11 15 26 31 32 14 10 11 15 23 26 31 10 11 15 23 26 31 3 cpbao