任意两个数字之间不相同的正差值的总个数再减去“正选数量为6-1”的值为AC值，求AC值

RICHEER COCA 2017-10-05 11:13:51



--> 建立数据表#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

--> 问题：求AC值





计算方法及规则：

任意两个数字之间不相同的正差值（相同差值只计作一个）的总个数再减去“正选数量为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

...全文

618 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

RICHEER COCA 2017-10-09

打赏
举报

引用 8 楼 ayalicer 的回复:

昨晚测试写的~又得重写一遍~ /* .. */ 部分先手动选中创建100W行测试数据,我这边跑了20秒出头

感谢 8 楼 ayalicer 的回复，经过测试
/* .. */ 部分先手动选中创建100W行测试数据,我这边跑了7秒出头
执行统计CT值的代码，120W行测试数据,我这边跑了44秒出头，时间可以接受，非常非常感谢大神的指点

nettman 2017-10-09

打赏
举报

学习

砸死牛顿的苹果 2017-10-08

打赏
举报

昨晚测试写的~又得重写一遍~ /* .. */ 部分先手动选中创建100W行测试数据,我这边跑了20秒出头

/*

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