22,300
社区成员




--> 建立数据表#test1 (数据表#test1实际共有120W 行记录,现在测试仅仅取10行记录)
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
CREATE TABLE [#test1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[No1] [int] NULL,
[No2] [int] NULL,
[No3] [int] NULL,
[No4] [int] NULL,
[No5] [int] NULL,
[No6] [int] NULL)
insert #test1
select '10','11','12','13','26','28' union all
select '4','9','19','20','21','26' union all
select '1','7','10','23','28','32' union all
select '4','6','7','10','13','25' union all
select '4','6','15','17','30','31' union all
select '1','3','10','21','26','27' union all
select '1','9','19','21','23','26' union all
select '5','8','9','14','17','23' union all
select '5','9','18','20','22','30' union all
select '1','2','8','13','17','24'
-- select * from #test1
计算方法及规则:
任意两个数字之间不相同的正差值(相同差值只计作一个)的总个数再减去“正选数量为6-1”的值。
如:01 03 10 21 26 27,正选数量为6,在这6个号码中计算得到任意两个数字之间的不相同的正差值的总个数为15,那么这组号码的AC值就是15-(6-1)=10。
再例如:10 11 12 13 26 28 共有不相同的差值9个,所以AC值=4,即9-(6-1)=4。
ID No1 No2 No3 No4 No5 No6 AC
1 10 11 12 13 26 28 4
2 4 9 19 20 21 26 7
3 1 7 10 23 28 32 8
4 4 6 7 10 13 25 7
5 4 6 15 17 30 31 7
6 1 3 10 21 26 27 10
7 1 9 19 21 23 26 9
8 5 8 9 14 17 23 6
9 5 9 18 20 22 30 7
10 1 2 8 13 17 24 7
/*
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
CREATE TABLE [#test1](
[ID] [int] IDENTITY(1,1) NOT NULL primary key,
[No1] [int] NULL,
[No2] [int] NULL,
[No3] [int] NULL,
[No4] [int] NULL,
[No5] [int] NULL,
[No6] [int] NULL)
insert #test1
select '10','11','12','13','26','28' union all
select '4','9','19','20','21','26' union all
select '1','7','10','23','28','32' union all
select '4','6','7','10','13','25' union all
select '4','6','15','17','30','31' union all
select '1','3','10','21','26','27' union all
select '1','9','19','21','23','26' union all
select '5','8','9','14','17','23' union all
select '5','9','18','20','22','30' union all
select '1','2','8','13','17','24'
go
insert #test1 select no1,no2,no3,no4,no5,no6 from #test1
go 16
insert #test1 select no1,no2,no3,no4,no5,no6 from #test1 where id<=1000000-655360
select count(*) as 总行数 from #test1 -- 总共100W条数据
go
*/
;with t as
(select id,oid=1,k=no1 from #test1
union all
select id,oid=2,k=no2 from #test1
union all
select id,oid=3,k=no3 from #test1
union all
select id,oid=4,k=no4 from #test1
union all
select id,oid=5,k=no5 from #test1
union all
select id,oid=6,k=no6 from #test1
)
,t1 as
(select
a.id
,ct=count(distinct(b.k-a.k))-5
from t a
join t b on a.id =b.id and b.oid >a.oid
group by a.id
)
select t.*,t1.ct
from #test1 t
left join t1 on t.id=t1.id
order by t.id