现有两个表,一个总表存放[单号][店名][时间][数量],记录大概几千条,另一明细表存放[序号][编号][单号]等,记录大概有十万条,现在明细表没单号,需要从总表把单号更新到明细表,按总表的序号、明细表的序号排序,根据总表数量更新明细表,如
总表:
[序号][单号][店名][时间] [数量]
1 001 A 20080101 1
2 002 B 20080102 2
3 003 C 20080103 3
3 004 D 20080104 2原来的明细表没有单号:
[序号][编号][单号]...
1 1001 ...
2 1001 ...
3 1001 ...
4 1001 ...
5 1001 ...
6 1001 ...
7 1001 ...
8 1001 ...我需要的结果是
[序号][编号][单号]...
1 1001 001 ...
2 1001 002 ...
3 1001 002 ...
4 1001 003 ...
5 1001 003 ...
6 1001 003 ...
7 1001 004 ...
8 1001 004 ...我试着用循环,TOP加参数时出错(用圆括号括起来也不可以),只好循环嵌套,速度太慢了,请教各位有快捷点的办法吗
只有这么点分,谢谢了
总表:
[序号][单号][店名][时间] [数量]
1 001 A 20080101 1
2 002 B 20080102 2
3 003 C 20080103 3
3 004 D 20080104 2原来的明细表没有单号:
[序号][编号][单号]...
1 1001 ...
2 1001 ...
3 1001 ...
4 1001 ...
5 1001 ...
6 1001 ...
7 1001 ...
8 1001 ...我需要的结果是
[序号][编号][单号]...
1 1001 001 ...
2 1001 002 ...
3 1001 002 ...
4 1001 003 ...
5 1001 003 ...
6 1001 003 ...
7 1001 004 ...
8 1001 004 ...我试着用循环,TOP加参数时出错(用圆括号括起来也不可以),只好循环嵌套,速度太慢了,请教各位有快捷点的办法吗
只有这么点分,谢谢了
a
set
单号=b.单号
from
明细表 a,总表 b
where
a.序号=b.序号
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1]([序号] int,[单号] varchar(3),[店名] varchar(1),[时间] datetime,[数量] int)
go
insert [tb1]
select 1,'001','A','20080101',1 union all
select 2,'002','B','20080102',2 union all
select 3,'003','C','20080103',3 union all
select 3,'004','D','20080104',2
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
create table [tb2]([序号] int,[编号] int,[单号] varchar(20))
go
insert [tb2]
select 1,1001,null union all
select 2,1001,null union all
select 3,1001,null union all
select 4,1001,null union all
select 5,1001,null union all
select 6,1001,null union all
select 7,1001,null union all
select 8,1001,nullselect id=identity(int,1,1),t.*
into #temp
from [tb1] t,master..spt_values r
where r.type = 'P' and r.number between 1 and t.数量update t
set 单号 = r.单号
from tb2 t,#temp r
where t.序号 = r.iddrop table #tempselect * from tb2
---------------------------
1 1001 001
2 1001 002
3 1001 002
4 1001 003
5 1001 003
6 1001 003
7 1001 004
8 1001 004
into b
from tb t join master..spt_values s
on s.number between 1 and t.PN
where s.type='P'这个master..spt_values 的number最大只有255,可以我的单有超过这个数量怎么办
SELECT TOP 800000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b ,syscolumns c
TOP 800000 id = IDENTITY(int, 1, 1),c.*
INTO
#
FROM
syscolumns a, syscolumns b ,tb1 c
where
c.id between 1 and c.数量update
a
set
单号 = a.单号
from
tb2 a,# b
where
a.序号 = b.id
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1]([序号] int,[单号] varchar(3),[店名] varchar(1),[时间] datetime,[数量] int)
go
insert [tb1]
select 1,'001','A','20080101',1 union all
select 2,'002','B','20080102',2 union all
select 3,'003','C','20080103',3 union all
select 3,'004','D','20080104',2
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
create table [tb2]([序号] int,[编号] int,[单号] varchar(20))
go
insert [tb2]
select 1,1001,null union all
select 2,1001,null union all
select 3,1001,null union all
select 4,1001,null union all
select 5,1001,null union all
select 6,1001,null union all
select 7,1001,null union all
select 8,1001,nullselect top 10000 number=identity(int,1,1) into #work from sysobjects,syscolumnsselect id=identity(int,1,1),t.*
into #temp
from [tb1] t,#work r
where r.number between 1 and t.数量update t
set 单号 = r.单号
from tb2 t,#temp r
where t.序号 = r.iddrop table #temp,#workselect * from tb2