--> 测试数据: [a] if object_id('[a]') is not null drop table [a] create table [a] (id int) insert into [a] select 2 union all select 4 --> 测试数据: [b] if object_id('[b]') is not null drop table [b] create table [b] (name varchar(2),sex varchar(2),age int) insert into [b] select 'aa','男',12 union all select 'bb','女',12 union all select 'cc','男',12 union all select 'dd','女',12select * from (select px=(select count(1) from a where id<=t.id),* from [a] t)a, (select px=(select count(1) from b where name<=t.name),* from [b] t)b where a.px=(b.px+1)/2--结果:
update a set a.id=b.id from tb a left join (select px=(select count(1) from [id] where id<t.id),* from [id] t) b on a.id/100=b.px
--> 测试数据: [a] if object_id('[a]') is not null drop table [a] create table [a] (id int) insert into [a] select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10--> 测试数据: [b] if object_id('[b]') is not null drop table [b] create table [b] (name varchar(10)) go declare @i int set @i=1 while(@i<=1000) begin insert into b select 'aa'+ltrim(@i) set @i=@i+1 end go --查询结果 select * from (select px=(select count(1) from a where id<=t.id),* from [a] t)a, (select px=(select count(1) from b where name<=t.name),* from [b] t)b where a.px=case b.px%100 when 0 then ((b.px-1)/100)+1 else (b.px/100)+1 end order by a.px
--> 测试数据: [a]
if object_id('[a]') is not null drop table [a]
create table [a] (id int)
insert into [a]
select 2 union all
select 4
--> 测试数据: [b]
if object_id('[b]') is not null drop table [b]
create table [b] (name varchar(2),sex varchar(2),age int)
insert into [b]
select 'aa','男',12 union all
select 'bb','女',12 union all
select 'cc','男',12 union all
select 'dd','女',12select * from
(select px=(select count(1) from a where id<=t.id),* from [a] t)a,
(select px=(select count(1) from b where name<=t.name),* from [b] t)b
where a.px=(b.px+1)/2--结果:
id sname ownerid
1 xxxx null
2 xxxx null
3 xx null
4 xxx null
-----
1000 xxxx null
id表
id sname
aaa 张三
bbb 李四
----
dfdfd 王二
(一共十个)更新1000数据的表,把id表的id update到1000条数据表中.每个id表中的id分100条数据
set a.id=b.id
from tb a
left join (select px=(select count(1) from [id] where id<t.id),* from [id] t) b
on a.id/100=b.px
--> 测试数据: [a]
if object_id('[a]') is not null drop table [a]
create table [a] (id int)
insert into [a]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10--> 测试数据: [b]
if object_id('[b]') is not null drop table [b]
create table [b] (name varchar(10))
go
declare @i int
set @i=1
while(@i<=1000)
begin
insert into b select 'aa'+ltrim(@i)
set @i=@i+1
end
go
--查询结果
select * from
(select px=(select count(1) from a where id<=t.id),* from [a] t)a,
(select px=(select count(1) from b where name<=t.name),* from [b] t)b
where a.px=case b.px%100 when 0 then ((b.px-1)/100)+1 else (b.px/100)+1 end
order by a.px