34,575
社区成员
发帖
与我相关
我的任务
分享
create table tb(块 varchar(10),行 int,列 int,id varchar(10))
insert into tb
select 'a',1,1,'asd1'
union
select 'a',1,2,'asd2'
union
select 'a',1,3,'asd3'
union
select 'a',1,4,'asd4'
union
select 'a',1,5,'asd5'
union
select 'a',1,6,'asd6'
union
select 'a',2,1,'asd7'
union
select 'a',2,2,'asd8'
union
select 'a',2,3,'asd9'
union
select 'a',2,4,'asd10'
union
select 'a',3,1,'asd11'
union
select 'a',3,2,'asd12'
union
select 'b',1,1,'asd13'
union
select 'b',1,2,'asd24'
union
select 'b',1,3,'asd35'
union
select 'b',2,1,'asd46'
create table tb2 (块 varchar(8),id varchar(10))
insert into tb2
select 'a','asd88'
union
select 'b','asd54'
insert tb
select 块,(select max(行) from tb where 块 = b.块) ,
(select top 1 列 + 1from tb where 块 = b.块 order by 行 desc,列 desc ),id from tb2 b
select * from tb
drop table tb,tb2
/*
块 行 列 id
---------- ----------- ----------- ----------
a 1 1 asd1
a 1 2 asd2
a 1 3 asd3
a 1 4 asd4
a 1 5 asd5
a 1 6 asd6
a 2 1 asd7
a 2 2 asd8
a 2 3 asd9
a 2 4 asd10
a 3 1 asd11
a 3 2 asd12
b 1 1 asd13
b 1 2 asd24
b 1 3 asd35
b 2 1 asd46
a 3 3 asd88
b 2 2 asd54
(所影响的行数为 18 行)
*/
create table tb(块 varchar(10),行 int,列 int,id varchar(10))
insert into tb
select 'a',1,1,'asd1'
union
select 'a',1,2,'asd2'
union
select 'a',1,3,'asd3'
union
select 'a',1,4,'asd4'
union
select 'a',1,5,'asd5'
union
select 'a',1,6,'asd6'
union
select 'a',2,1,'asd7'
union
select 'a',2,2,'asd8'
union
select 'a',2,3,'asd9'
union
select 'a',2,4,'asd10'
union
select 'a',3,1,'asd11'
union
select 'a',3,2,'asd12'
union
select 'b',1,1,'asd13'
union
select 'b',1,2,'asd24'
union
select 'b',1,3,'asd35'
union
select 'b',2,1,'asd46'
create table tb2 (块 varchar(8),id varchar(10))
insert into tb2
select 'a','asd88'
union
select 'b','asd54'
insert into tb
select
a.块,
(select max(行) from tb d where d.块=a.块) as 行,
(select max(列)+1 from tb b
where b.块=a.块
and not exists (select 1 from tb c where c.块=b.块 and c.行>b.行)) as 列,
a.id
from tb2 a
insert into tb
select
a.块,
(select max(行) from tb d where d.块=a.块) as 行,
(select max(列)+1 from tb b
where b.块=a.块
and not exists (select 1 from tb c where c.块=b.块 and c.行>b.行)) as 列,
a.id
from tb2 a
insert into 表一
select a.块 , isnull(max(a.col2),1) , isnull(max(a.行),1) , isnull(max(a.列),1) + 1 , b.id from 表一 a,表2 b where a.块 = b.块 group by a.块
insert into 表一
select a.块 , isnull(max(a.col2),1) , isnull(max(a.行),1) , isnull(max(a.列),1) + 1 , b.id from 表一 a,表2 b where a.块 = b.块
insert 表一(块,行,列,id)
select
块,
(select max(行) from 表一 where 块=a.块) as 行,
(select max(列)+1 from 表一 b where 块=a.块 and not exists (select 1 from 表一 where 块=b.块 and 行>b.行)) as 列,
id
from 表2 a