表account
ID OWNERID
0005460C5CF84FBBA585D1E1616B6DA8 NULL
00D7E09F7AEF4AF2836D7D6A54C20A3C NULL
00E4C42E04A44A698649EFA24BCBBFC5 NULL
00F246BD365B4E3A96E8D6A584161DAF NULL
01EB94008367481C8A9EFA8A9CA57501 NULL
02687465A5884F4D8A1D8C0462833483 NULL
029D27282B5A4806BD1F37C161DB5C64 NULL
02AB3D949F944440A2983CF26A1DCE1A NULL
032A16B5CF4743369824A1F17DCB16F3 NULL
04032E3E4E9049A18B30A6BB055AC26C NULL
06F067F284F342148153861138102421 NULL
07D5F8E3F9134B54894FA2CAE838501B NULL
08B066E56F154D2CA3799C58BC02B840 NULL
表users
ID SNAME
11890110221252090619802011811433 张三
11890113035930535792292130498086 李四
11890113425152087477948020576049 王二
11890113817507428539451754284973 中国
11890114388281134910229193282303 -
11890114467960128022705706768949 -
11890114812340137041272516941791 -现在要用表B的ID更新表A中的OWNERID.如果手工去更新可以用下面的语句.
update account set ownerid=(select id from users where sname='张三' )
where id in (select top 25 id from account where ownerid is null)
如果我想把users表中n个人每人平分出account表中where出的数据,可不可以用一条语句实现.
ID OWNERID
0005460C5CF84FBBA585D1E1616B6DA8 NULL
00D7E09F7AEF4AF2836D7D6A54C20A3C NULL
00E4C42E04A44A698649EFA24BCBBFC5 NULL
00F246BD365B4E3A96E8D6A584161DAF NULL
01EB94008367481C8A9EFA8A9CA57501 NULL
02687465A5884F4D8A1D8C0462833483 NULL
029D27282B5A4806BD1F37C161DB5C64 NULL
02AB3D949F944440A2983CF26A1DCE1A NULL
032A16B5CF4743369824A1F17DCB16F3 NULL
04032E3E4E9049A18B30A6BB055AC26C NULL
06F067F284F342148153861138102421 NULL
07D5F8E3F9134B54894FA2CAE838501B NULL
08B066E56F154D2CA3799C58BC02B840 NULL
表users
ID SNAME
11890110221252090619802011811433 张三
11890113035930535792292130498086 李四
11890113425152087477948020576049 王二
11890113817507428539451754284973 中国
11890114388281134910229193282303 -
11890114467960128022705706768949 -
11890114812340137041272516941791 -现在要用表B的ID更新表A中的OWNERID.如果手工去更新可以用下面的语句.
update account set ownerid=(select id from users where sname='张三' )
where id in (select top 25 id from account where ownerid is null)
如果我想把users表中n个人每人平分出account表中where出的数据,可不可以用一条语句实现.
if object_id('[account]') is not null drop table [account]
go
create table [account]([ID] varchar(32),[OWNERID] varchar(32))
insert [account]
select '0005460C5CF84FBBA585D1E1616B6DA8',null union all
select '00D7E09F7AEF4AF2836D7D6A54C20A3C',null union all
select '00E4C42E04A44A698649EFA24BCBBFC5',null union all
select '00F246BD365B4E3A96E8D6A584161DAF',null union all
select '01EB94008367481C8A9EFA8A9CA57501',null union all
select '02687465A5884F4D8A1D8C0462833483',null union all
select '029D27282B5A4806BD1F37C161DB5C64',null union all
select '02AB3D949F944440A2983CF26A1DCE1A',null union all
select '032A16B5CF4743369824A1F17DCB16F3',null union all
select '04032E3E4E9049A18B30A6BB055AC26C',null union all
select '06F067F284F342148153861138102421',null union all
select '07D5F8E3F9134B54894FA2CAE838501B',null union all
select '08B066E56F154D2CA3799C58BC02B840',null
if object_id('[users]') is not null drop table [users]
go
create table [users]([ID] varchar(32),[SNAME] varchar(4))
insert [users]
select 11890110221252090619802011811433,'张三' union all
select 11890113035930535792292130498086,'李四' union all
select 11890113425152087477948020576049,'王二' union all
select 11890113817507428539451754284973,'中国' union all
select 11890114388281134910229193282303,'-' union all
select 11890114467960128022705706768949,'-' union all
select 11890114812340137041272516941791,'-'
---更新---
update a
set a.OWNERID=c.id
from
account a,
(select px=(select count(1)+1 from account where ID<t.ID),* from account t) b,
(select px=(select count(1)+1 from users where ID<t.ID),* from users t) c
where
a.id=b.id and b.px=c.px---查询---
select * from [account]---结果---
ID OWNERID
-------------------------------- --------------------------------
0005460C5CF84FBBA585D1E1616B6DA8 11890110221252090619802011811433
00D7E09F7AEF4AF2836D7D6A54C20A3C 11890113035930535792292130498086
00E4C42E04A44A698649EFA24BCBBFC5 11890113425152087477948020576049
00F246BD365B4E3A96E8D6A584161DAF 11890113817507428539451754284973
01EB94008367481C8A9EFA8A9CA57501 11890114388281134910229193282303
02687465A5884F4D8A1D8C0462833483 11890114467960128022705706768949
029D27282B5A4806BD1F37C161DB5C64 11890114812340137041272516941791
02AB3D949F944440A2983CF26A1DCE1A NULL
032A16B5CF4743369824A1F17DCB16F3 NULL
04032E3E4E9049A18B30A6BB055AC26C NULL
06F067F284F342148153861138102421 NULL
07D5F8E3F9134B54894FA2CAE838501B NULL
08B066E56F154D2CA3799C58BC02B840 NULL(所影响的行数为 13 行)
这样?
create table account (ID varchar(50),OWNERID varchar(50) )
go
insert account select '0005460C5CF84FBBA585D1E1616B6DA8', NULL
insert account select '00D7E09F7AEF4AF2836D7D6A54C20A3C', NULL
insert account select '00E4C42E04A44A698649EFA24BCBBFC5', NULL
insert account select '00F246BD365B4E3A96E8D6A584161DAF', NULL
insert account select '01EB94008367481C8A9EFA8A9CA57501', NULL
insert account select '02687465A5884F4D8A1D8C0462833483', NULL
insert account select '029D27282B5A4806BD1F37C161DB5C64', NULL
insert account select '02AB3D949F944440A2983CF26A1DCE1A', NULL
insert account select '032A16B5CF4743369824A1F17DCB16F3', NULL
insert account select '04032E3E4E9049A18B30A6BB055AC26C', NULL
insert account select '06F067F284F342148153861138102421', NULL
insert account select '07D5F8E3F9134B54894FA2CAE838501B', NULL create table users ( ID varchar(50), SNAME varchar(10))
go
insert users select '11890110221252090619802011811433' , '张三'
insert users select '11890113035930535792292130498086' , '李四'
insert users select '11890113425152087477948020576049' , '王二'
insert users select '11890113817507428539451754284973' , '中国'
go
alter table account add id1 int identity(1,1)
go
alter table account add px int
go
alter table users add px int identity(1,1)
go
update account set px=(id1-1)/(select count(1) from users)
update account set OWNERID=users.id from users where account.px=users.px-1
select * from account
drop table account,users
/*
ID OWNERID id1 px
-------------------------------------------------- -------------------------------------------------- ----------- -----------
0005460C5CF84FBBA585D1E1616B6DA8 11890110221252090619802011811433 1 0
00D7E09F7AEF4AF2836D7D6A54C20A3C 11890110221252090619802011811433 2 0
00E4C42E04A44A698649EFA24BCBBFC5 11890110221252090619802011811433 3 0
00F246BD365B4E3A96E8D6A584161DAF 11890110221252090619802011811433 4 0
01EB94008367481C8A9EFA8A9CA57501 11890113035930535792292130498086 5 1
02687465A5884F4D8A1D8C0462833483 11890113035930535792292130498086 6 1
029D27282B5A4806BD1F37C161DB5C64 11890113035930535792292130498086 7 1
02AB3D949F944440A2983CF26A1DCE1A 11890113035930535792292130498086 8 1
032A16B5CF4743369824A1F17DCB16F3 11890113425152087477948020576049 9 2
04032E3E4E9049A18B30A6BB055AC26C 11890113425152087477948020576049 10 2
06F067F284F342148153861138102421 11890113425152087477948020576049 11 2
07D5F8E3F9134B54894FA2CAE838501B 11890113425152087477948020576049 12 2(所影响的行数为 12 行)
*/
--1.更新现有ID
UPDATE account SET OWNERID = LO.USID
FROM ACCOUNT T,# LO WHERE T.[ID] = LO.[ID] AND T.OWNERID IS NULL
--2.插入新的记录
INSERT ACCOUNT ([ID],OWNERID)
SELECT T.[ID],T.USID
FROM # T
--两个表分别以12条记录,4条记录为例,楼主可以按你的要求改动一下。
create table account (ID varchar(50),OWNERID varchar(50) )
go
insert account select '0005460C5CF84FBBA585D1E1616B6DA8', NULL
insert account select '00D7E09F7AEF4AF2836D7D6A54C20A3C', NULL
insert account select '00E4C42E04A44A698649EFA24BCBBFC5', NULL
insert account select '00F246BD365B4E3A96E8D6A584161DAF', NULL
insert account select '01EB94008367481C8A9EFA8A9CA57501', NULL
insert account select '02687465A5884F4D8A1D8C0462833483', NULL
insert account select '029D27282B5A4806BD1F37C161DB5C64', NULL
insert account select '02AB3D949F944440A2983CF26A1DCE1A', NULL
insert account select '032A16B5CF4743369824A1F17DCB16F3', NULL
insert account select '04032E3E4E9049A18B30A6BB055AC26C', NULL
insert account select '06F067F284F342148153861138102421', NULL
insert account select '07D5F8E3F9134B54894FA2CAE838501B', NULL create table users ( ID varchar(50), SNAME varchar(10))
go
insert users select '11890110221252090619802011811433' , '张三'
insert users select '11890113035930535792292130498086' , '李四'
insert users select '11890113425152087477948020576049' , '王二'
insert users select '11890113817507428539451754284973' , '中国'
go
alter table account add id1 int identity(1,1)
go
alter table account add px int
go
alter table users add px int identity(1,1)
go
update account set px=(id1-1)/((select count(1) from users)-1)
update account set OWNERID=users.id from users where account.px=users.px-1
select * from account
drop table account,users
/*
ID OWNERID id1 px
-------------------------------------------------- -------------------------------------------------- ----------- -----------
0005460C5CF84FBBA585D1E1616B6DA8 11890110221252090619802011811433 1 0
00D7E09F7AEF4AF2836D7D6A54C20A3C 11890110221252090619802011811433 2 0
00E4C42E04A44A698649EFA24BCBBFC5 11890110221252090619802011811433 3 0
00F246BD365B4E3A96E8D6A584161DAF 11890113035930535792292130498086 4 1
01EB94008367481C8A9EFA8A9CA57501 11890113035930535792292130498086 5 1
02687465A5884F4D8A1D8C0462833483 11890113035930535792292130498086 6 1
029D27282B5A4806BD1F37C161DB5C64 11890113425152087477948020576049 7 2
02AB3D949F944440A2983CF26A1DCE1A 11890113425152087477948020576049 8 2
032A16B5CF4743369824A1F17DCB16F3 11890113425152087477948020576049 9 2
04032E3E4E9049A18B30A6BB055AC26C 11890113817507428539451754284973 10 3
06F067F284F342148153861138102421 11890113817507428539451754284973 11 3
07D5F8E3F9134B54894FA2CAE838501B 11890113817507428539451754284973 12 3(所影响的行数为 12 行)
*/
我想要这样.
update account 表的结果是
如果account where 出来的数量是20条
users where 出来的数量 是10个
那users里的id随机更新二条account表中的ownerid
便account表如下
ID OWNERID
-------------------------------- --------------------------------
0005460C5CF84FBBA585D1E1616B6DA8 11890110221252090619802011811433
00D7E09F7AEF4AF2836D7D6A54C20A3C 11890114812340137041272516941791
00E4C42E04A44A698649EFA24BCBBFC5 11890114467960128022705706768949
00F246BD365B4E3A96E8D6A584161DAF 11890113035930535792292130498086
01EB94008367481C8A9EFA8A9CA57501 11890110221252090619802011811433
02687465A5884F4D8A1D8C0462833483 11890114467960128022705706768949
029D27282B5A4806BD1F37C161DB5C64 11890114812340137041272516941791
02AB3D949F944440A2983CF26A1DCE1A 11890113035930535792292130498086
032A16B5CF4743369824A1F17DCB16F3 11890113425152087477948020576049
04032E3E4E9049A18B30A6BB055AC26C 11890113425152087477948020576049
--两个表分别以12条记录,4条记录为例,楼主可以按你的要求改动一下。
create table account (ID varchar(50),OWNERID varchar(50) )
go
insert account select '0005460C5CF84FBBA585D1E1616B6DA8', NULL
insert account select '00D7E09F7AEF4AF2836D7D6A54C20A3C', NULL
insert account select '00E4C42E04A44A698649EFA24BCBBFC5', NULL
insert account select '00F246BD365B4E3A96E8D6A584161DAF', NULL
insert account select '01EB94008367481C8A9EFA8A9CA57501', NULL
insert account select '02687465A5884F4D8A1D8C0462833483', NULL
insert account select '029D27282B5A4806BD1F37C161DB5C64', NULL
insert account select '02AB3D949F944440A2983CF26A1DCE1A', NULL
insert account select '032A16B5CF4743369824A1F17DCB16F3', NULL
insert account select '04032E3E4E9049A18B30A6BB055AC26C', NULL
insert account select '06F067F284F342148153861138102421', NULL
insert account select '07D5F8E3F9134B54894FA2CAE838501B', NULL create table users ( ID varchar(50), SNAME varchar(10))
go
insert users select '11890110221252090619802011811433' , '张三'
insert users select '11890113035930535792292130498086' , '李四'
insert users select '11890113425152087477948020576049' , '王二'
insert users select '11890113817507428539451754284973' , '中国'
go
update d set OWNERID=f.id from account d,
(select *,px=(id1-1)/((select count(1) from users)-1) from (select *,id1=(select count(1) from account where id<=a.id ) from account a) b) e,
(select *,px=(select count(1) from users where id<=c.ID) from users c) f
where d.id=e.id and e.px=f.px-1select id,OWNERID from account
drop table account,users
/*id OWNERID
-------------------------------------------------- --------------------------------------------------
0005460C5CF84FBBA585D1E1616B6DA8 11890110221252090619802011811433
00D7E09F7AEF4AF2836D7D6A54C20A3C 11890110221252090619802011811433
00E4C42E04A44A698649EFA24BCBBFC5 11890110221252090619802011811433
00F246BD365B4E3A96E8D6A584161DAF 11890113035930535792292130498086
01EB94008367481C8A9EFA8A9CA57501 11890113035930535792292130498086
02687465A5884F4D8A1D8C0462833483 11890113035930535792292130498086
029D27282B5A4806BD1F37C161DB5C64 11890113425152087477948020576049
02AB3D949F944440A2983CF26A1DCE1A 11890113425152087477948020576049
032A16B5CF4743369824A1F17DCB16F3 11890113425152087477948020576049
04032E3E4E9049A18B30A6BB055AC26C 11890113817507428539451754284973
06F067F284F342148153861138102421 11890113817507428539451754284973
07D5F8E3F9134B54894FA2CAE838501B 11890113817507428539451754284973(所影响的行数为 12 行)
*/
users where 出来的数量 是10个呢?
if object_id('account') is not null
drop table account
go
if object_id('users') is not null
drop table users
go
create table account (ID varchar(50),OWNERID varchar(50) )
go
insert account select '0005460C5CF84FBBA585D1E1616B6DA8', NULL
insert account select '00D7E09F7AEF4AF2836D7D6A54C20A3C', NULL
insert account select '00E4C42E04A44A698649EFA24BCBBFC5', NULL
insert account select '00F246BD365B4E3A96E8D6A584161DAF', NULL
insert account select '01EB94008367481C8A9EFA8A9CA57501', NULL
insert account select '02687465A5884F4D8A1D8C0462833483', NULL
insert account select '029D27282B5A4806BD1F37C161DB5C64', NULL
insert account select '02AB3D949F944440A2983CF26A1DCE1A', NULL
insert account select '032A16B5CF4743369824A1F17DCB16F3', NULL
insert account select '04032E3E4E9049A18B30A6BB055AC26C', NULL
insert account select '06F067F284F342148153861138102421', NULL
insert account select '07D5F8E3F9134B54894FA2CAE838501B', NULL create table users ( ID varchar(50), SNAME varchar(10))
go
insert users select '11890110221252090619802011811433' , '张三'
insert users select '11890113035930535792292130498086' , '李四'
insert users select '11890113425152087477948020576049' , '王二'
insert users select '11890113817507428539451754284973' , '中国'
gouse tempdb
go
if object_id('temp') is not null
drop table temp
go
create table temp (rowid int identity(1,1),sname varchar(20))
insert into temp(sname) select sname from users
declare @s varchar(8000)
declare @m int,@n int
set @n = (select max(rowid) from temp)
set @m=0
while @m<@n
begin
if object_id('temp1') is not null
drop table temp1
create table temp1 (sname varchar(20))
insert into temp1(sname) select sname from temp where rowid = @m+1
select @s =isnull(@s,'')+char(10)+' update b '+char(10)
select @s =@s + ' set ownerid = (select id from users where sname =(select sname from temp1 ))'+char(10)
select @s =@s + ' from account b '+ char(10)
select @s =@s + ' where id in (select top 25 id '+ char(10)
select @s =@s + ' from account '+ char(10)
select @s =@s + ' where ownerid is null and id in(select top (('+convert(varchar, @m)+'+1)*25) id from account order by id asc)'+char(10)
select @s =@s + ' )'+char(10) set @m= @m +1
end
--print (@s)
exec (@s)
select * from account
---
/*
ID OWNERID
-------------------------------------------------- --------------------------------------------------
0005460C5CF84FBBA585D1E1616B6DA8 11890113817507428539451754284973
00D7E09F7AEF4AF2836D7D6A54C20A3C 11890113817507428539451754284973
00E4C42E04A44A698649EFA24BCBBFC5 11890113817507428539451754284973
00F246BD365B4E3A96E8D6A584161DAF 11890113817507428539451754284973
01EB94008367481C8A9EFA8A9CA57501 11890113817507428539451754284973
02687465A5884F4D8A1D8C0462833483 11890113817507428539451754284973
029D27282B5A4806BD1F37C161DB5C64 11890113817507428539451754284973
02AB3D949F944440A2983CF26A1DCE1A 11890113817507428539451754284973
032A16B5CF4743369824A1F17DCB16F3 11890113817507428539451754284973
04032E3E4E9049A18B30A6BB055AC26C 11890113817507428539451754284973
06F067F284F342148153861138102421 11890113817507428539451754284973
07D5F8E3F9134B54894FA2CAE838501B 11890113817507428539451754284973(12 row(s) affected)
*/
use tempdb
go
if object_id('temp') is not null
drop table temp
go
create table temp (rowid int identity(1,1),sname varchar(20))
insert into temp(sname) select sname from users
declare @s varchar(8000)
declare @m int,@n int
set @n = (select max(rowid) from temp)
set @m=0
while @m<@n
begin
if object_id('temp1') is not null
drop table temp1
create table temp1 (sname varchar(20))
insert into temp1(sname) select sname from temp where rowid = @m+1
declare @s1 varchar(40)
select @s1 = (select sname from temp1)
select @s =isnull(@s,'')+char(10)+' update b '+char(10)
select @s =@s + ' set ownerid = (select id from users where sname ='''+@s1+''')'+char(10)
select @s =@s + ' from account b '+ char(10)
select @s =@s + ' where id in (select top 3 id '+ char(10)
select @s =@s + ' from account '+ char(10)
select @s =@s + ' where ownerid is null and id in(select top (('+convert(varchar, @m)+'+1)*3) id from account order by id asc)'+char(10)
select @s =@s + ' )'+char(10) set @m= @m +1
end
--print (@s)
exec (@s)
select * from account
---
/*
ID OWNERID
-------------------------------------------------- --------------------------------------------------
ID OWNERID
-------------------------------------------------- --------------------------------------------------
0005460C5CF84FBBA585D1E1616B6DA8 11890110221252090619802011811433
00D7E09F7AEF4AF2836D7D6A54C20A3C 11890110221252090619802011811433
00E4C42E04A44A698649EFA24BCBBFC5 11890110221252090619802011811433
00F246BD365B4E3A96E8D6A584161DAF 11890113035930535792292130498086
01EB94008367481C8A9EFA8A9CA57501 11890113035930535792292130498086
02687465A5884F4D8A1D8C0462833483 11890113035930535792292130498086
029D27282B5A4806BD1F37C161DB5C64 11890113425152087477948020576049
02AB3D949F944440A2983CF26A1DCE1A 11890113425152087477948020576049
032A16B5CF4743369824A1F17DCB16F3 11890113425152087477948020576049
04032E3E4E9049A18B30A6BB055AC26C 11890113817507428539451754284973
06F067F284F342148153861138102421 11890113817507428539451754284973
07D5F8E3F9134B54894FA2CAE838501B 11890113817507428539451754284973(12 row(s) affected)
*/