表1:
pp1 pp2 pp3 pp4 pp5 AC
1 2 3 4 5
2 3 4 5 6
6 7 8 9 10问题:主要是统计表1每相邻的两条纪录之间有多少个相同的数字。(我的表有1200多万条纪录)
比如:表1中第2条纪录与第1条纪录相比,有2 3 4 5四个数字相同,则在第二条纪录的工AC值
写入4,依次类推,第三条纪录与第二条纪录有6 这个数字相同,则在第三条纪录的AC值写1
就是这样一个问题。
pp1 pp2 pp3 pp4 pp5 AC
1 2 3 4 5
2 3 4 5 6
6 7 8 9 10问题:主要是统计表1每相邻的两条纪录之间有多少个相同的数字。(我的表有1200多万条纪录)
比如:表1中第2条纪录与第1条纪录相比,有2 3 4 5四个数字相同,则在第二条纪录的工AC值
写入4,依次类推,第三条纪录与第二条纪录有6 这个数字相同,则在第三条纪录的AC值写1
就是这样一个问题。
Select 表1.*,identity(int,1,1) as Fid Into #tmp From 表1.*
create table #tmp1 (pp int,Fid int Not NULL)
INSERT INTO #TMP1
select t.pp1,t.Fid From #tmp
Union all
select t.pp2,t.Fid From #tmp
Union all
select t.pp3,t.Fid From #tmp
Union all
select t.pp4,t.Fid From #tmp
Union all
select t.pp5,t.Fid From #tmp
--测试数据
if object_id('ta') is not null drop table ta
go
create table ta(pp1 int, pp2 int, pp3 int, pp4 int, pp5 int, AC int)
insert ta(pp1, pp2, pp3, pp4, pp5)
select 1, 2, 3, 4, 5 union all
select 2, 3, 4, 5, 6 union all
select 6, 7, 8, 9, 10
go
--定义一个UDF,
--功能是:计算前一条记录pp11, pp12, pp13, pp14, pp15
---------与后一条记录pp21, pp22, pp23, pp24, pp25之间相同的次数
if object_id('dbo.calcFun') is not null drop function dbo.calcFun
go
create function dbo.calcFun(
@pp11 int, @pp12 int, @pp13 int , @pp14 int, @pp15 int,
@pp21 int, @pp22 int, @pp23 int , @pp24 int, @pp25 int)
returns int
as
begin
declare @count int
set @count=0
if @pp11 in(@pp21, @pp22, @pp23, @pp24, @pp25) set @count=@count+1
if @pp12 in(@pp21, @pp22, @pp23, @pp24, @pp25) set @count=@count+1
if @pp13 in(@pp21, @pp22, @pp23, @pp24, @pp25) set @count=@count+1
if @pp14 in(@pp21, @pp22, @pp23, @pp24, @pp25) set @count=@count+1
if @pp15 in(@pp21, @pp22, @pp23, @pp24, @pp25) set @count=@count+1
return @count
end
go
--处理程序的主要部分
--建立一个临时表, 表中插入序号列
select *, id=identity(int) into #tt from ta
--更新原表的AC列, 得到想要的结果
update ta
set ac=t.ac
from(select t1.pp1, t1.pp2 , t1.pp3, t1.pp4, t1.pp5,
ac=dbo.calcFun(t1.pp1, t1.pp2, t1.pp3, t1.pp4, t1.pp5,
t2.pp1, t2.pp2, t2.pp3, t2.pp4, t2.pp5)
from #tt as t1 left join #tt as t2 on t2.id=t1.id-1
)t
where ta.pp1=t.pp1
and ta.pp2=t.pp2
and ta.pp3=t.pp3
and ta.pp4=t.pp4
and ta.pp5=t.pp5
--查询结果
select* from ta
--扫尾
drop table #tt
drop table ta
create table #tmp1 (pp int,Fid int Not NULL,ac int null)
INSERT INTO #TMP1(pp,fid)
select t.pp1,t.Fid From #tmp t
Union all
select t.pp2,t.Fid From #tmp t
Union all
select t.pp3,t.Fid From #tmp t
Union all
select t.pp4,t.Fid From #tmp t
Union all
select t.pp5,t.Fid From #tmp t
Select Count(1),fID From #tmp1 a Where a.Fid in (Select distinct Fid From #tmp1 Where #tmp1.fid<=a.Fid) and a.pp in (Select pp From #tmp1 Where #tmp1.Fid<a.Fid) Group By Fid
select * from #tmp1 order by Fid
drop table #tmp,#tmp1
create table ta(pp1 int, pp2 int, pp3 int, pp4 int, pp5 int, AC int)
insert ta(pp1, pp2, pp3, pp4, pp5)
select 1, 2, 3, 4, 5 union all
select 2, 3, 4, 5, 6 union all
select 6, 7, 8, 9, 10Go
Select ta.*,identity(int,1,1) as Fid Into #tmp From ta
create table #tmp1 (pp int,Fid int Not NULL,ac int null)
INSERT INTO #TMP1(pp,fid)
select t.pp1,t.Fid From #tmp t
Union all
select t.pp2,t.Fid From #tmp t
Union all
select t.pp3,t.Fid From #tmp t
Union all
select t.pp4,t.Fid From #tmp t
Union all
select t.pp5,t.Fid From #tmp tUpdate #tmp1 Set Ac=a.ac From (
Select Count(1) As ac ,fID From #tmp1 a Where a.Fid in (Select distinct Fid From #tmp1 Where #tmp1.fid<=a.Fid) and a.pp in (Select pp From #tmp1 Where #tmp1.Fid<a.Fid) Group By Fid)a
Where a.Fid =#tmp1.Fid
select * from #tmp1 order by Fid
drop table #tmp,#tmp1
create table ta(pp1 int, pp2 int, pp3 int, pp4 int, pp5 int, AC int)
insert ta(pp1, pp2, pp3, pp4, pp5)
select 1, 2, 3, 4, 5 union all
select 2, 3, 4, 5, 6 union all
select 6, 7, 8, 9, 10 union all
select 7, 11, 8, 9, 10 union all
select 8, 9, 10, 12, 13Go
Alter table ta add Fid int (identity,1,1) --目的生成唯一的Fid
Go
create table #tmp1 (pp int,Fid int Not NULL,ac int null)
INSERT INTO #TMP1(pp,fid)
select t.pp1,t.Fid From ta t
Union all
select t.pp2,t.Fid From ta t
Union all
select t.pp3,t.Fid From ta t
Union all
select t.pp4,t.Fid From ta t
Union all
select t.pp5,t.Fid From ta tUpdate ta Set Ac=a.ac From (
Select Count(1) As ac ,fID From #tmp1 a Where a.Fid in (Select distinct Fid From #tmp1 Where #tmp1.fid<=a.Fid) and a.pp in (Select pp From #tmp1 Where #tmp1.Fid<a.Fid) Group By Fid)a
Where a.Fid =ta.Fid
select * from ta order by Fid
drop table tmp1
alter table ta drop column fid --删除Fid