--其中1,2,3,4,5,6分别对应a,b,c,d,e,f create table tb(id int, content varchar(10))
insert into tb values(1 , 'adsfasdf')
insert into tb values(2 , 'adsfasdf')
insert into tb values(3 , 'adsfasdf')
insert into tb values(4 , 'adsfasdf')
insert into tb values(5 , 'adsfasdf')
insert into tb values(6 , 'adsfasdf')
insert into tb values(7 , 'adsfasdf')
insert into tb values(8 , 'adsfasdf')
insert into tb values(9 , 'adsfasdf')
insert into tb values(10 , 'adsfasdf')
insert into tb values(11 , 'adsfasdf')
insert into tb values(12 , 'adsfasdf')
insert into tb values(13 , 'adsfasdf')
insert into tb values(14 , 'adsfasdf')
insert into tb values(15 , 'adsfasdf')
insert into tb values(16 , 'adsfasdf')goselect id , ((id - 1)%6) + 1 话务员 from tb drop table tb/*
id 话务员
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
7 1
8 2
9 3
10 4
11 5
12 6
13 1
14 2
15 3
16 4(所影响的行数为 16 行)
*/
insert into tb values(1 , 'adsfasdf')
insert into tb values(2 , 'adsfasdf')
insert into tb values(3 , 'adsfasdf')
insert into tb values(4 , 'adsfasdf')
insert into tb values(5 , 'adsfasdf')
insert into tb values(6 , 'adsfasdf')
insert into tb values(7 , 'adsfasdf')
insert into tb values(8 , 'adsfasdf')
insert into tb values(9 , 'adsfasdf')
insert into tb values(10 , 'adsfasdf')
insert into tb values(11 , 'adsfasdf')
insert into tb values(12 , 'adsfasdf')
insert into tb values(13 , 'adsfasdf')
insert into tb values(14 , 'adsfasdf')
insert into tb values(15 , 'adsfasdf')
insert into tb values(16 , 'adsfasdf')goselect id , ((id - 1)%6) + 1 话务员 from tb drop table tb/*
id 话务员
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
7 1
8 2
9 3
10 4
11 5
12 6
13 1
14 2
15 3
16 4(所影响的行数为 16 行)
*/
insert into tb values(1 , 'adsfasdf')
insert into tb values(2 , 'adsfasdf')
insert into tb values(3 , 'adsfasdf')
insert into tb values(4 , 'adsfasdf')
insert into tb values(5 , 'adsfasdf')
insert into tb values(6 , 'adsfasdf')
insert into tb values(7 , 'adsfasdf')
insert into tb values(8 , 'adsfasdf')
insert into tb values(9 , 'adsfasdf')
insert into tb values(10 , 'adsfasdf')
insert into tb values(11 , 'adsfasdf')
insert into tb values(12 , 'adsfasdf')
insert into tb values(13 , 'adsfasdf')
insert into tb values(14 , 'adsfasdf')
insert into tb values(15 , 'adsfasdf')
insert into tb values(16 , 'adsfasdf')goselect id , ((id - 1)%6) + 1 px,
case ((id - 1)%6) + 1
when 1 then 'A'
when 2 then 'B'
when 3 then 'C'
when 4 then 'D'
when 5 then 'E'
when 6 then 'F'
end 话务员
from tb drop table tb/*
id px 话务员
----------- ----------- ----
1 1 A
2 2 B
3 3 C
4 4 D
5 5 E
6 6 F
7 1 A
8 2 B
9 3 C
10 4 D
11 5 E
12 6 F
13 1 A
14 2 B
15 3 C
16 4 D(所影响的行数为 16 行)
*/
insert into tb values(1 , 'adsfasdf')
insert into tb values(2 , 'adsfasdf')
insert into tb values(3 , 'adsfasdf')
insert into tb values(4 , 'adsfasdf')
insert into tb values(5 , 'adsfasdf')
insert into tb values(6 , 'adsfasdf')
insert into tb values(7 , 'adsfasdf')
insert into tb values(8 , 'adsfasdf')
insert into tb values(9 , 'adsfasdf')
insert into tb values(10 , 'adsfasdf')
insert into tb values(11 , 'adsfasdf')
insert into tb values(12 , 'adsfasdf')
insert into tb values(13 , 'adsfasdf')
insert into tb values(14 , 'adsfasdf')
insert into tb values(15 , 'adsfasdf')
insert into tb values(16 , 'adsfasdf')goselect 话务员 , count(*) cnt from
(
select id , ((id - 1)%6) + 1 px,
case ((id - 1)%6) + 1
when 1 then 'A'
when 2 then 'B'
when 3 then 'C'
when 4 then 'D'
when 5 then 'E'
when 6 then 'F'
end 话务员
from tb
) t
group by 话务员drop table tb/*
话务员 cnt
---- -----------
A 3
B 3
C 3
D 3
E 2
F 2(所影响的行数为 6 行)
*/
insert into tb values(1 )
insert into tb values(2 )
insert into tb values(3 )
insert into tb values(4 )
insert into tb values(5 )
insert into tb values(6 )
insert into tb values(7 )
insert into tb values(8 )
insert into tb values(9 )
insert into tb values(10)
insert into tb values(11 )
insert into tb values(12 )
insert into tb values(13 )
insert into tb values(14 )
insert into tb values(15 )
insert into tb values(16 )
insert into tb values(17 )
insert into tb values(18 )go
create table hb(id int, names varchar(10))
insert into hb values(1 , 'aaaaaaa')
insert into hb values(2 , 'bbbbbbb')
insert into hb values(3 , 'ccccccc')
insert into hb values(4 , 'dddddddd')godeclare @num int --总数量
declare @h int --话务员
declare @avg int
set @num = (select count(*) from tb)
set @h = (select count(*) from hb)
select @avg = @num%@h --余数
if(@avg =0)
begin
select distinct @num as 总数量,@h as 话务员,(@num/@h) as 单数量
endelse
begin
select distinct @num as 总数量,@h as 话务员,((@num-(@num%@h))/@h)as 单数量, @avg as 余数
end
drop table tb
drop table hb
declare @num int
declare @h int
declare @avg int
set @num = (select count(*) from tb)
set @h = (select count(*) from hb)
select @avg = @num%@h
if(@avg =0)
begin
select distinct names, (@num/@h) as 单数量 from hb
endelse
begin
if(@avg = @h)
select distinct names,((@num-(@num%@h))/@h)+1 as 单数量 from hb
else if(@avg <@h)
begin
declare @dm int
set @dm = ((@num-(@num%@h))/@h)+1
select distinct names, @dm as 单数量 from hb where id>=1 and id<= @avg
union
select distinct names,((@num-(@num%@h))/@h) as 单数量 from hb where id> @avg
end
end
names 单数量
---------- -----------
aaaaaaa 5
bbbbbbb 5
ccccccc 4
dddddddd 4(4 行受影响)