[Sonum] [varchar](10) NULL,
[ItemNum] [varchar](30) NULL,
[ItemQty] [int] NULL,
[SoArea] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[testtb] ADD CONSTRAINT [DF_testtb_Sonum] DEFAULT (‘‘) FOR [Sonum]
GO
ALTER TABLE [dbo].[testtb] ADD CONSTRAINT [DF_testtb_ItemNum] DEFAULT (‘‘) FOR [ItemNum]
GO
ALTER TABLE [dbo].[testtb] ADD CONSTRAINT [DF_testtb_ItemQty] DEFAULT ((0)) FOR [ItemQty]
GO
ALTER TABLE [dbo].[testtb] ADD CONSTRAINT [DF_testtb_SoArea] DEFAULT (‘‘) FOR [SoArea]
GO
CREATE TABLE [dbo].[testtb2](
[Sonum] [varchar](10) NULL,
[ItemNum] [varchar](30) NULL,
[ItemQty] [int] NULL,
[SoArea] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[testtb2] ADD CONSTRAINT [DF_testtb2_Sonum] DEFAULT (‘‘) FOR [Sonum]
GO
ALTER TABLE [dbo].[testtb2] ADD CONSTRAINT [DF_testtb2_ItemNum] DEFAULT (‘‘) FOR [ItemNum]
GO
ALTER TABLE [dbo].[testtb2] ADD CONSTRAINT [DF_testtb2_ItemQty] DEFAULT ((0)) FOR [ItemQty]
GO
ALTER TABLE [dbo].[testtb2] ADD CONSTRAINT [DF_testtb2_SoArea] DEFAULT (‘‘) FOR [SoArea]
GO
CREATE TABLE [dbo].[testtb3](
[Sonum] [varchar](10) NULL,
[ItemNum] [varchar](30) NULL,
[ItemQty] [int] NULL,
[SoArea] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[testtb3] ADD CONSTRAINT [DF_testtb3_Sonum] DEFAULT (‘‘) FOR [Sonum]
GO
ALTER TABLE [dbo].[testtb3] ADD CONSTRAINT [DF_testtb3_ItemNum] DEFAULT (‘‘) FOR [ItemNum]
GO
ALTER TABLE [dbo].[testtb3] ADD CONSTRAINT [DF_testtb3_ItemQty] DEFAULT ((0)) FOR [ItemQty]
GO
ALTER TABLE [dbo].[testtb3] ADD CONSTRAINT [DF_testtb3_SoArea] DEFAULT (‘‘) FOR [SoArea]
GO
CREATE TABLE [dbo].[AreaDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[areaid] [varchar](5) NOT NULL,
[Emtry] [char](10) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AreaDetail] ADD CONSTRAINT [DF_AreaDetail_areaid] DEFAULT (‘‘) FOR [areaid]
GO
ALTER TABLE [dbo].[AreaDetail] ADD CONSTRAINT [DF_AreaDetail_Emtry] DEFAULT (‘Y‘) FOR [Emtry]
GO
insert into testtb values(‘SO1‘,‘Item1001‘,‘2000‘,‘‘)
insert into testtb values(‘SO1‘,‘Item1002‘,‘600‘,‘‘)
insert into testtb values(‘SO2‘,‘Item1003‘,‘3500‘,‘‘)
insert into testtb values(‘SO2‘,‘Item1004‘,‘1400‘,‘‘)
insert into testtb values(‘SO2‘,‘Item1005‘,‘1000‘,‘‘)
insert into testtb values(‘SO3‘,‘Item1006‘,‘7500‘,‘‘)
insert into testtb3 values(‘SO1‘,‘Item1001‘,‘2000‘,‘A1‘)
insert into testtb3 values(‘SO1‘,‘Item1002‘,‘400‘,‘A1‘)
insert into testtb3 values(‘SO1‘,‘Item1002‘,‘200‘,‘A2‘)
insert into testtb3 values(‘SO2‘,‘Item1003‘,‘2400‘,‘A3‘)
insert into testtb3 values(‘SO2‘,‘Item1003‘,‘1100‘,‘A4‘)
insert into testtb3 values(‘SO2‘,‘Item1004‘,‘1300‘,‘A4‘)
insert into testtb3 values(‘SO2‘,‘Item1004‘,‘100‘,‘A5‘)
insert into testtb3 values(‘SO2‘,‘Item1005‘,‘1000‘,‘A5‘)
insert into testtb3 values(‘SO3‘,‘Item1006‘,‘2400‘,‘A6‘)
insert into testtb3 values(‘SO3‘,‘Item1006‘,‘2400‘,‘A7‘)
insert into testtb3 values(‘SO3‘,‘Item1006‘,‘2400‘,‘A8‘)
insert into testtb3 values(‘SO3‘,‘Item1006‘,‘300‘,‘A9‘)
insert into AreaDetail(areaid,Emtry) values(‘A1‘,‘Y‘)
insert into AreaDetail(areaid,Emtry) values(‘A2‘,‘Y‘)
insert into AreaDetail(areaid,Emtry) values(‘A3‘,‘Y‘)
insert into AreaDetail(areaid,Emtry) values(‘A4‘,‘Y‘)
insert into AreaDetail(areaid,Emtry) values(‘A5‘,‘Y‘)
insert into AreaDetail(areaid,Emtry) values(‘A6‘,‘Y‘)
insert into AreaDetail(areaid,Emtry) values(‘A7‘,‘Y‘)
insert into AreaDetail(areaid,Emtry) values(‘A8‘,‘Y‘)
insert into AreaDetail(areaid,Emtry) values(‘A9‘,‘Y‘)
select * from testtb
select * from testtb3
select * from AreaDetailView Code
--select * from testtb
--select * from testtb3
--select * from AreaDetail
declare @i int
declare @AreaId varchar(5)
declare @Sonum nvarchar(1000)
declare Cursor_DBname cursor for
select distinct sonum from testtb
open Cursor_DBname
fetch next from Cursor_DBname into @Sonum
while(@@FETCH_STATUS = 0)
begin
------第二层循环start----------
set @i=1
while @i < (select count(*) from testtb where Sonum =@Sonum)
begin
select TOP 1 @AreaId=areaid from AreaDetail WHERE Emtry=‘Y‘ ORDER BY ID
--将数据插入到testtb2，目的是变成testtb3
update AreaDetail set Emtry=‘N‘ where areaid=@AreaId--填充区域后，要将它变成非空状态
break
set @i=@i+1
end
----第二层循环-End-----------
fetch next from Cursor_DBname into @Sonum
end
close Cursor_DBname
DEALLOCATE Cursor_DBnameView Code
sql
标签：