34,593
社区成员
发帖
与我相关
我的任务
分享
create table 客户表
(CUS_ID int,CUS_MOBILE varchar(20))
insert into 客户表
select 2001,'138123456' union all
select 2002,'138456789' union all
select 2003,'132111111' union all
select 2004,'134124574' union all
select 2005,'138123456' union all
select 2006,'135478956' union all
select 2007,'137478956'
create table 客户经理表
(CM_NAME varchar(10))
insert into 客户经理表
select '张三丰' union all
select '李四' union all
select '王五'
select d.CUS_ID,d.CUS_MOBILE,e.CM_NAME
from
(select CUS_ID,CUS_MOBILE,row_number() over(order by getdate()) 'rn' from 客户表) d
left join
(select CM_NAME,row_number() over(order by newid()) 'rn'
from
(select a.CM_NAME
from 客户经理表 a
cross join
(select number from master.dbo.spt_values
where type='P' and number>=1
and number<=((select count(1) from 客户表)/(select count(1) from 客户经理表))) b
union all
select top ((select count(1) from 客户表)
-(select count(1) from 客户经理表)*((select count(1) from 客户表)/(select count(1) from 客户经理表))) CM_NAME
from 客户经理表 c
order by newid()) t) e on d.rn=e.rn
/*
CUS_ID CUS_MOBILE CM_NAME
----------- -------------------- ----------
2001 138123456 李四
2002 138456789 王五
2003 132111111 李四
2004 134124574 张三丰
2005 138123456 李四
2006 135478956 王五
2007 137478956 张三丰
(7 row(s) affected)
*/
create table 客户表T(CUS_ID int,CUS_MOBILE varchar(15))
insert into 客户表T
select 2001,'138123456' union all
select 2002,'138456789' union all
select 2003,'132111111' union all
select 2004,'134124574' union all
select 2005,'138123456' union all
select 2006,'135478956' union all
select 2007,'137478956'
create table 客户经理表(CM_NAME varchar(10))
insert into 客户经理表
select '张三丰' union all
select '李四' union all
select '王五'
go
;with t
as
(
select *,
ROW_NUMBER() over(order by getdate()) rownum
from 客户表T
),
tt
as
(
select a.*,
b.c,
ROW_NUMBER() over(order by newid()) rownum
from 客户经理表 a
inner join (select COUNT(*) as c from 客户经理表) b
on 1 = 1
)
select t.CUS_ID,t.CUS_MOBILE,tt.CM_NAME
from t
inner join tt
on t.rownum % tt.c = tt.rownum - 1
/*
CUS_ID CUS_MOBILE CM_NAME
2003 132111111 李四
2006 135478956 李四
2001 138123456 王五
2004 134124574 王五
2007 137478956 王五
2002 138456789 张三丰
2005 138123456 张三丰
*/
create table 客户表T(CUS_ID int,CUS_MOBILE varchar(15))
insert into 客户表T
select 2001,'138123456' union all
select 2002,'138456789' union all
select 2003,'132111111' union all
select 2004,'134124574' union all
select 2005,'138123456' union all
select 2006,'135478956' union all
select 2007,'137478956'
create table 客户经理表(CM_NAME varchar(10))
insert into 客户经理表
select '张三丰' union all
select '李四' union all
select '王五'
go
;with t
as
(
select *,
ROW_NUMBER() over(order by newid()) rownum
from 客户表T
),
tt
as
(
select a.*,
b.c,
ROW_NUMBER() over(order by newid()) rownum
from 客户经理表 a
inner join (select COUNT(*) as c from 客户经理表) b
on 1 = 1
)
select t.CUS_ID,t.CUS_MOBILE,tt.CM_NAME
from t
inner join tt
on t.rownum % tt.c = tt.rownum - 1
/*
CUS_ID CUS_MOBILE CM_NAME
2001 138123456 王五
2004 134124574 王五
2005 138123456 张三丰
2004 134124574 张三丰
2006 135478956 张三丰
2004 134124574 李四
2003 132111111 李四
*/