create talbe ls (idm int, flag int)
CREATE TALBE t (idM int,MainCode varchar(10),MainName varchar(50),SubName varchar(50))
Insert ls (idm, flag) Select idm, flag From Tbl_Main
Declare @Min int, @max int, @flag int, @min1 int, @idm int
select @min = min(idm), @max = Max(idm) From ls
While @min <= @max
begin
Select @flag = flag From ls where idm = @min
set @min1 = 0
while @min1 <= @flag
begin
Select @idm = idm From ls where idm = @min1
Insert t (idM,MainCode,MainName) Select idM,MainCode,MainName From Tbl_Main
Where idm = @min1
Set @min1 = @min1 + 1
end
set @min = @min + 1
end
Update t set SubName = a.SubName From Tbl_Sub a, t b Where a.MainCode=b.MainCode
Select * From t
这是第7
CREATE TALBE t (idM int,MainCode varchar(10),MainName varchar(50),SubName varchar(50))
Insert ls (idm, flag) Select idm, flag From Tbl_Main
Declare @Min int, @max int, @flag int, @min1 int, @idm int
select @min = min(idm), @max = Max(idm) From ls
While @min <= @max
begin
Select @flag = flag From ls where idm = @min
set @min1 = 0
while @min1 <= @flag
begin
Select @idm = idm From ls where idm = @min1
Insert t (idM,MainCode,MainName) Select idM,MainCode,MainName From Tbl_Main
Where idm = @min1
Set @min1 = @min1 + 1
end
set @min = @min + 1
end
Update t set SubName = a.SubName From Tbl_Sub a, t b Where a.MainCode=b.MainCode
Select * From t
这是第7
第8
where b.MainCode = a.MainCode
第9
Select a.idM ,a.MainCode,a.MainName,b.SubName From Tbl_Main a Left Join Tbl_Sub On a.MainCode=b.MainCode
CREATE TALBE t (idM int,MainCode varchar(10),MainName varchar(50),SubName varchar(50))
Insert ls (idm, flag) Select idm, flag From Tbl_Main
Declare @Min int, @max int, @flag int, @min1 int
select @min = min(idm), @max = Max(idm) From ls
While @min <= @max
begin
Select @flag = flag From ls where idm = @min
set @min1 = 0
while @min1 <= @flag
begin
Insert t (idM,MainCode,MainName) Select idM,MainCode,MainName From
Tbl_Main Where idm = @min1
Set @min1 = @min1 + 1
end
set @min = @min + 1
end
Update t set SubName = a.SubName From Tbl_Sub a, t b Where a.MainCode=b.MainCode
Select * From t
这是第7
Select a.* From Tbl_Main a Where MainCode In(Select Distinct MainCode From Tbl_Sub)
Select idS, a.MainName as MainClass, SunName From Tbl_Sub b MainCode a
where b.MainCode = a.MainCode
CREATE TALBE #t (idM int,MainCode varchar(10),MainName varchar(50),SubName varchar(50))
Insert #ls (idm, flag) Select idm, flag From Tbl_Main --在临时表中放入id
Declare @Min int, @max int, @flag int, @min1 int
select @min = min(idm), @max = Max(idm) From #ls
While @min <= @max --在临时表中最大最小id之间做循环
begin
Select @flag = flag From #ls where idm = @min --取出flag的值
set @min1 = 0
while @min1 <= @flag --从0开始循环到flag的值,插入到#t表中
begin
Insert #t (idM,MainCode,MainName) Select idM,MainCode,MainName From
Tbl_Main Where idm = @min1
Set @min1 = @min1 + 1
end
set @min = @min + 1
end
Update #t set SubName = a.SubName From Tbl_Sub a, t b Where a.MainCode=b.MainCode
Select * From #t
drop talbe #t, #ls
OK?
CREATE TALBE #t (idM int,MainCode varchar(10),MainName varchar(50),SubName varchar(50))
Insert #ls (idm, flag) Select idm, flag From Tbl_Main --在临时表中放入id
Declare @Min int, @max int, @flag int, @min1 int
select @min = min(idm), @max = Max(idm) From #ls
While @min <= @max --在临时表中最大最小id之间做循环
begin
Select @flag = flag From #ls where idm = @min --取出flag的值
set @min1 = 0
while @min1 <= @flag --从0开始循环到flag的值,插入到#t表中
begin
Insert #t (idM,MainCode,MainName) Select idM,MainCode,MainName From
Tbl_Main Where idm = @min1
Set @min1 = @min1 + 1
end
set @min = @min + 1
end
Update #t set SubName = a.SubName From Tbl_Sub a, #t b Where a.MainCode=b.MainCode
Select * From #t
drop talbe #t, #ls
OK?
CREATE TALBE #t (idM int,MainCode varchar(10),MainName varchar(50),SubName varchar(50))
Insert #ls (idm, flag) Select idm, flag From Tbl_Main --在临时表中放入id
Declare @Min int, @max int, @flag int, @min1 int
select @min = min(idm), @max = Max(idm) From #ls
While @min <= @max --在临时表中最大最小id之间做循环
begin
Select @flag = flag From #ls where idm = @min --取出flag的值
set @min1 = 1
while @min1 <= @flag --从1开始循环到flag的值,插入到#t表中
begin
Insert #t (idM,MainCode,MainName) Select idM,MainCode,MainName From
Tbl_Main Where idm = @min1
Set @min1 = @min1 + 1
end
set @min = @min + 1
end
Insert #t (idM,MainCode,MainName) Select idM,MainCode,MainName From
Tbl_Main Where flag = 0
Update #t set SubName = a.SubName From Tbl_Sub a, #t b Where a.MainCode=b.MainCode
Select * From #t
drop talbe #t, #ls
flag = 0 and 1的时候插入一次
flag > 1的时候跟据数的值的大小来插入的。
应该可以有没用插时表的办法,
但这是我的思路,你可以想好的办法啊。
if exists(select name from sysobjects where xtype = 'u' and status >= 0 and name = Tbl_MainA) truncate table tablename
else create table tablename(...)
go
insert into tablename
select * from ...