表A
FIELD1 FIELD2 FIELD3 FIELD4
GH LH 3.5 65
GH LH 3.5 76
GH LH 3.3 90
GH1 LH1 2.8 87
得到结果显示为 :
根据field1，field2，field3 分组后不满10行的插入空记录(FIELD1,FIELD2保留，field3，field4插入0)。
添加标志和序号列
上表得到如下结果
FLAG SN FIELD1 FIELD2 FIELD3 FIELD4
1 1 GH LH 3.5 65
1 2 GH LH 3.5 76
1 3 GH LH 0 0
1 4 GH LH 0 0
......
1 10 GH LH 0 0
2 1 GH LH 3.3 90
2 2 GH LH 0 0
2 3 GH LH 0 0
.....
2 10 GH LH 0 0
3 1 GH1 LH1 2.8 87
3 2 GH1 LH1 0 0
3 3 GH1 LH1 0 0
......
3 10 GH1 LH1 0 0
得到这30条记录。
FIELD1 FIELD2 FIELD3 FIELD4
GH LH 3.5 65
GH LH 3.5 76
GH LH 3.3 90
GH1 LH1 2.8 87
得到结果显示为 :
根据field1，field2，field3 分组后不满10行的插入空记录(FIELD1,FIELD2保留，field3，field4插入0)。
添加标志和序号列
上表得到如下结果
FLAG SN FIELD1 FIELD2 FIELD3 FIELD4
1 1 GH LH 3.5 65
1 2 GH LH 3.5 76
1 3 GH LH 0 0
1 4 GH LH 0 0
......
1 10 GH LH 0 0
2 1 GH LH 3.3 90
2 2 GH LH 0 0
2 3 GH LH 0 0
.....
2 10 GH LH 0 0
3 1 GH1 LH1 2.8 87
3 2 GH1 LH1 0 0
3 3 GH1 LH1 0 0
......
3 10 GH1 LH1 0 0
得到这30条记录。
create table tb(f1 varchar(10),f2 varchar(10),f3 decimal(12,2),f4 decimal(12,2))
insert into tb
select 'GH','LH',3.5,65 union all
select 'GH','LH',3.5,76 union all
select 'GH','LH',3.3,90 union all
select 'GH1','LH1',2.8,87
go;with ach as
(
select rid=row_number() over (partition by f1,f2,f3 order by getdate()),
sid=row_number() over (order by getdate()),*
from tb
)select (select count(*) from ach where rid = 1 and sid <= a.sid) as flag,
b.number sn,
c.f1 f1,c.f2 f2,
(case when a.rid = b.number then a.f3 else 0 end) f3,
(case when a.rid = b.number then a.f4 else 0 end) f4
from master..spt_values b cross join (select distinct f1,f2,f3 from ach) c
left join ach a on c.f1 = a.f1 and c.f2 = a.f2 and c.f3 = a.f3 and a.rid = b.number
where b.number between 1 and 10 and b.[type] = 'p'drop table tb/*********************flag sn f1 f2 f3 f4
----------- ----------- ---------- ---------- --------------------------------------- ---------------------------------------
1 1 GH LH 3.30 90.00
0 2 GH LH 0.00 0.00
0 3 GH LH 0.00 0.00
0 4 GH LH 0.00 0.00
0 5 GH LH 0.00 0.00
0 6 GH LH 0.00 0.00
0 7 GH LH 0.00 0.00
0 8 GH LH 0.00 0.00
0 9 GH LH 0.00 0.00
0 10 GH LH 0.00 0.00
2 1 GH LH 3.50 65.00
2 2 GH LH 3.50 76.00
0 3 GH LH 0.00 0.00
0 4 GH LH 0.00 0.00
0 5 GH LH 0.00 0.00
0 6 GH LH 0.00 0.00
0 7 GH LH 0.00 0.00
0 8 GH LH 0.00 0.00
0 9 GH LH 0.00 0.00
0 10 GH LH 0.00 0.00
3 1 GH1 LH1 2.80 87.00
0 2 GH1 LH1 0.00 0.00
0 3 GH1 LH1 0.00 0.00
0 4 GH1 LH1 0.00 0.00
0 5 GH1 LH1 0.00 0.00
0 6 GH1 LH1 0.00 0.00
0 7 GH1 LH1 0.00 0.00
0 8 GH1 LH1 0.00 0.00
0 9 GH1 LH1 0.00 0.00
0 10 GH1 LH1 0.00 0.00(30 行受影响)
insert a
select 'GH','LH', 3.5 ,65 union all
select 'GH','LH', 3.5 ,76 union all
select 'GH','LH', 3.3 ,90 union all
select 'GH1','LH1', 2.8 ,87select ta.flag,ta.number,ta.FIELD1,ta.FIELD2,
FeIELD3=case when tb.number is null then 0 else tb.FeIELD3 end,
FeIELD4=case when tb.number is null then 0 else tb.field4 end
from
(select FLAG=DENSE_RANK() over (order by FIELD1,FIELD2,FeIELD3), FIELD1,FIELD2,FeIELD3,number from a,master..spt_values
where type='p' and number between 1 and 10
group by FIELD1,FIELD2,FeIELD3,number) ta
left join
(select number=row_number() over (partition by FIELD1,FIELD2,FeIELD3 order by FIELD4),* from a) tb
on ta.FIELD1=tb.FIELD1 and ta.FIELD2=tb.FIELD2 and ta.FeIELD3=tb.FeIELD3 and ta.number=tb.number/*
flag number FIELD1 FIELD2 FeIELD3 FeIELD4
-------------------- ----------- ------ ------ --------------------------------------- -----------
1 1 GH LH 3.3 90
1 2 GH LH 0.0 0
1 3 GH LH 0.0 0
1 4 GH LH 0.0 0
1 5 GH LH 0.0 0
1 6 GH LH 0.0 0
1 7 GH LH 0.0 0
1 8 GH LH 0.0 0
1 9 GH LH 0.0 0
1 10 GH LH 0.0 0
2 1 GH LH 3.5 65
2 2 GH LH 3.5 76
2 3 GH LH 0.0 0
2 4 GH LH 0.0 0
2 5 GH LH 0.0 0
2 6 GH LH 0.0 0
2 7 GH LH 0.0 0
2 8 GH LH 0.0 0
2 9 GH LH 0.0 0
2 10 GH LH 0.0 0
3 1 GH1 LH1 2.8 87
3 2 GH1 LH1 0.0 0
3 3 GH1 LH1 0.0 0
3 4 GH1 LH1 0.0 0
3 5 GH1 LH1 0.0 0
3 6 GH1 LH1 0.0 0
3 7 GH1 LH1 0.0 0
3 8 GH1 LH1 0.0 0
3 9 GH1 LH1 0.0 0
3 10 GH1 LH1 0.0 0(30 行受影响)
*/
insert into tb
select 'GH','LH',3.5,65 union all
select 'GH','LH',3.5,76 union all
select 'GH','LH',3.3,90 union all
select 'GH1','LH1',2.8,87
gowith temp (sn) as
(select 1 as sn
union all
select sn + 1 from temp where sn < 10),
test as
(select distinct DENSE_RANK() over(order by f1,f2,f3) 'flag',
f1,f2
from tb),
test1 as
(select * from test,temp),
test2 as
(select distinct DENSE_RANK() over(order by f1,f2,f3) 'flag',
ROW_NUMBER() over(partition by f1,f2,f3 order by f1,f2,f3) 'sn',
f1,f2,f3,f4
from tb)
select test1.flag,test1.sn,test1.f1,test1.f2,test2.f3,test2.f4 from test1
left join test2
on test1.f1 = test2.f1
and test1.f2 = test2.f2
and test1.flag = test2.flag
and test1.sn = test2.sn
order by test1.flag,test1.sn
create table #A
(FIELD1 nvarchar(10), FIELD2 nvarchar(10), FIELD3 float,FIELD4 int)
insert #A
select 'GH','LH',3.5, 65 union all
select 'GH','LH',3.5, 76 union all
select 'GH','LH',3.3, 90 union all
select 'GH1','LH1',2.8, 87;with TempA as (select number as SN from master..spt_values where type='p' and number between 1 and 10)
,TempB as(select Row_number()over(order by getdate()) as FLAG, FIELD1,FIELD2,FIELD3 from #A group by FIELD1,FIELD2,FIELD3)
,TempC as(select Row_number()over(partition by FIELD1,FIELD2,FIELD3 order by getdate()) as num,* from #A)
,TempD as(select * from TempA as a join TempB as b on 1=1)
select D.FLAG,D.SN,D.FIELD1,D.FIELD2,isnull(C.FIELD3,0) as FIELD3,isnull(c.FIELD4,0) as FIELD4 from TempD as d Left join TempC as c on
d.FIELD1=c.FIELD1 and d.FIELD2=c.FIELD2 and d.FIELD3=c.FIELD3 and c.num=d.SN寫得有點複雜.. 都不好意思貼出來了..
注:数据库为sql 2000
create table tb(f1 varchar(10),f2 varchar(10),f3 decimal(12,2),f4 decimal(12,2))
insert into tb
select 'GH','LH',3.5,65 union all
select 'GH','LH',3.5,76 union all
select 'GH','LH',3.3,90 union all
select 'GH1','LH1',2.8,87
go
select sid=identity(int,1,1),*
into #tb
from tbselect *,(select count(*) from #tb where f1 = t.f1 and f2 = t.f2 and f3 = t.f3 and sid <= t.sid) as rid
into #ach
from #tb tselect (select count(*) from #ach where rid = 1 and sid <= a.sid) as flag,
b.number sn,
c.f1 f1,c.f2 f2,
(case when a.rid = b.number then a.f3 else 0 end) f3,
(case when a.rid = b.number then a.f4 else 0 end) f4
from master..spt_values b cross join (select distinct f1,f2,f3 from #ach) c
left join #ach a on c.f1 = a.f1 and c.f2 = a.f2 and c.f3 = a.f3 and a.rid = b.number
where b.number between 1 and 10 and b.[type] = 'p'drop table tb,#tb,#ach/******************flag sn f1 f2 f3 f4
----------- ----------- ---------- ---------- --------------------------------------- ---------------------------------------
2 1 GH LH 3.30 90.00
0 2 GH LH 0.00 0.00
0 3 GH LH 0.00 0.00
0 4 GH LH 0.00 0.00
0 5 GH LH 0.00 0.00
0 6 GH LH 0.00 0.00
0 7 GH LH 0.00 0.00
0 8 GH LH 0.00 0.00
0 9 GH LH 0.00 0.00
0 10 GH LH 0.00 0.00
1 1 GH LH 3.50 65.00
1 2 GH LH 3.50 76.00
0 3 GH LH 0.00 0.00
0 4 GH LH 0.00 0.00
0 5 GH LH 0.00 0.00
0 6 GH LH 0.00 0.00
0 7 GH LH 0.00 0.00
0 8 GH LH 0.00 0.00
0 9 GH LH 0.00 0.00
0 10 GH LH 0.00 0.00
3 1 GH1 LH1 2.80 87.00
0 2 GH1 LH1 0.00 0.00
0 3 GH1 LH1 0.00 0.00
0 4 GH1 LH1 0.00 0.00
0 5 GH1 LH1 0.00 0.00
0 6 GH1 LH1 0.00 0.00
0 7 GH1 LH1 0.00 0.00
0 8 GH1 LH1 0.00 0.00
0 9 GH1 LH1 0.00 0.00
0 10 GH1 LH1 0.00 0.00(30 行受影响)
insert a
select 'GH','LH', 3.5 ,65 union all
select 'GH','LH', 3.5 ,76 union all
select 'GH','LH', 3.3 ,90 union all
select 'GH1','LH1', 2.8 ,87select FIELD1,FIELD2,FeIELD3,identity(int,1,1)id
into #a from a group by FIELD1,FIELD2,FeIELD3
select ta.id,ta.number,ta.FIELD1,ta.FIELD2,
FeIELD3=case when tb.number is null then 0 else tb.FeIELD3 end,
FeIELD4=case when tb.number is null then 0 else tb.field4 end
from
(select FIELD1,FIELD2,FeIELD3,number,max(id)id
from #a,master..spt_values
where type='p' and number between 1 and 10
group by FIELD1,FIELD2,FeIELD3,number) ta
left join
(select number=(select count(*) from a
where FIELD1=t1.FIELD1 and FIELD2=t1.FIELD2
and FeIELD3=t1.FeIELD3 and FIELD4<=t1.FIELD4),* from a t1) tb
on ta.FIELD1=tb.FIELD1 and ta.FIELD2=tb.FIELD2
and ta.FeIELD3=tb.FeIELD3 and ta.number=tb.number/*
id number FIELD1 FIELD2 FeIELD3 FeIELD4
----------- ----------- ------ ------ ---------- -----------
1 1 GH LH 3.3 90
1 2 GH LH .0 0
1 3 GH LH .0 0
1 4 GH LH .0 0
1 5 GH LH .0 0
1 6 GH LH .0 0
1 7 GH LH .0 0
1 8 GH LH .0 0
1 9 GH LH .0 0
1 10 GH LH .0 0
2 1 GH LH 3.5 65
2 2 GH LH 3.5 76
2 3 GH LH .0 0
2 4 GH LH .0 0
2 5 GH LH .0 0
2 6 GH LH .0 0
2 7 GH LH .0 0
2 8 GH LH .0 0
2 9 GH LH .0 0
2 10 GH LH .0 0
3 1 GH1 LH1 2.8 87
3 2 GH1 LH1 .0 0
3 3 GH1 LH1 .0 0
3 4 GH1 LH1 .0 0
3 5 GH1 LH1 .0 0
3 6 GH1 LH1 .0 0
3 7 GH1 LH1 .0 0
3 8 GH1 LH1 .0 0
3 9 GH1 LH1 .0 0
3 10 GH1 LH1 .0 0（所影响的行数为 30 行）*/
create table #A
(FIELD1 nvarchar(10), FIELD2 nvarchar(10), FIELD3 float,FIELD4 int)
insert #A
select 'GH','LH',3.5, 65 union all
select 'GH','LH',3.5, 76 union all
select 'GH','LH',3.3, 90 union all
select 'GH1','LH1',2.8, 87declare @TempA table(num int identity(1,1),SN int,FIELD1 nvarchar(10), FIELD2 nvarchar(10), FIELD3 float,FIELD4 int)
insert @TempA(SN,FIELD1 , FIELD2 , FIELD3 ,FIELD4)
select 0,FIELD1 , FIELD2 , FIELD3 ,FIELD4 from #A
union all
select 0,FIELD1 , FIELD2 , FIELD3 ,0 from master..spt_values as v join
(select count(1)+1 as [count],FIELD1 , FIELD2 , FIELD3 from #A
group by FIELD1 , FIELD2 , FIELD3 having count(1)<10) as a
on number between [count] and 10
where type='p' order by FIELD1 ,FIELD2 , FIELD3 ,FIELD4 descupdate @TempA set SN=case when num % 10<>0 then num % 10 else 10 end
select (num-SN)/10+1 as falg,SN,FIELD1 ,FIELD2 , FIELD3 ,FIELD4 from @TempA
(30 row(s) affected)(30 row(s) affected)
falg SN FIELD1 FIELD2 FIELD3 FIELD4
----------- ----------- ---------- ---------- ---------------------- -----------
1 1 GH LH 3.3 90
1 2 GH LH 3.3 0
1 3 GH LH 3.3 0
1 4 GH LH 3.3 0
1 5 GH LH 3.3 0
1 6 GH LH 3.3 0
1 7 GH LH 3.3 0
1 8 GH LH 3.3 0
1 9 GH LH 3.3 0
1 10 GH LH 3.3 0
2 1 GH LH 3.5 76
2 2 GH LH 3.5 65
2 3 GH LH 3.5 0
2 4 GH LH 3.5 0
2 5 GH LH 3.5 0
2 6 GH LH 3.5 0
2 7 GH LH 3.5 0
2 8 GH LH 3.5 0
2 9 GH LH 3.5 0
2 10 GH LH 3.5 0
3 1 GH1 LH1 2.8 87
3 2 GH1 LH1 2.8 0
3 3 GH1 LH1 2.8 0
3 4 GH1 LH1 2.8 0
3 5 GH1 LH1 2.8 0
3 6 GH1 LH1 2.8 0
3 7 GH1 LH1 2.8 0
3 8 GH1 LH1 2.8 0
3 9 GH1 LH1 2.8 0
3 10 GH1 LH1 2.8 0(30 row(s) affected)