雖然這題自己解完@@..
來問問看各位大神的解法~
假設有5位員工為AA, BB, CC, DD, EE
維修站為01~13
試問SQL如何下呢?才能達成平均分配在維修站點
我的寫法可以參考看看囉~
declare @Emp table(
Emp_id int
,Emp_AD nvarchar(50)
)
declare @Site table(
Site_Num nvarchar(50)
)
declare @Count int,@i int
set @i = 1
insert into @Emp
values(1,'AA')
,(2,'BB')
,(3,'CC')
,(4,'DD')
,(5,'EE')
while(@i <= 13)
begin
insert into @Site
values(right('0' + Convert(varchar,@i),2))
set @i += 1
end
select @Count = count(*)
from @Emp
select Site_Num
,(
select Emp_AD
from @Emp
where Emp_id = (
case when Sort % @Count = 0
then @Count
else Sort % @Count
end
)
) as Emp_AD
from (
select Row_Number()Over(order by Site_Num) Sort
,Site_Num
from @Site
) as k
order by Site_Num
增加 一級屠豬士 的亂數做法XD
select Site_Num
,(
select Emp_AD
from @Emp
where Emp_id = (
case when Sort % @Count = 0
then @Count
else Sort % @Count
end
)
) as Emp_AD
from (
select Row_Number()Over(order by NEWID()) Sort
,Site_Num
from @Site
) as k
order by Site_Num
因為答覆是暐翰先回的~所以我就把解答給他了~其他都非常感謝^^~
已邀請的邦友 {{ invite_list.length }}/5
使用row_number + %mod方式
declare @empcount int = (select count(1) from emp);
with CTE as (
select row_number() over (order by empid) rnk ,*
from emp
),CTE2 as (
select row_number() over (order by CenterID) -1 rnk ,*
from Center
)
select T1.CenterID,T2.empid from CTE2 T1
left join CTE T2 on ((T1.rnk) % (@empcount)) = T2.rnk-1
order by T1.CenterID;
TEST DDL:
CREATE TABLE emp ([empid] varchar(7));
INSERT INTO emp ([empid]) VALUES ('AA'),('BB'),('CC'),('DD'),('EE');
CREATE TABLE Center ([CenterID] int);
INSERT INTO Center([CenterID]) VALUES('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('11'),('12'),('13');
create table ithelp180723_emp (
id serial primary key
, ename text not null
);
insert into ithelp180723_emp (ename) values
('AA'),('BB'),('CC'),('DD'),('EE');
create table ithelp180723_stan (
id serial primary key
, stid text not null
);
insert into ithelp180723_stan (stid)
select lpad(n::text, 2, '0')
from generate_series(1, 13) as gs(n);
-----------------
-- ntile() function
-- https://docs.microsoft.com/zh-tw/sql/t-sql/functions/ntile-transact-sql?view=sql-server-2017
with st1 as (
select count(*) as cnt
from ithelp180723_emp
), st2 as (
select stid
, ntile(st1.cnt::integer) over(order by stid) as asgnemp
from ithelp180723_stan
, st1
)
select stid
, ename
from st2
join ithelp180723_emp e
on st2.asgnemp = e.id;
stid | ename
------+-------
01 | AA
02 | AA
03 | AA
04 | BB
05 | BB
06 | BB
07 | CC
08 | CC
09 | CC
10 | DD
11 | DD
12 | EE
13 | EE
(13 筆資料列)
----------------------
-- Random style
with st1 as (
select count(*) as cnt
from ithelp180723_emp
), st2 as (
select stid
, ntile(st1.cnt::integer) over(order by random()) as asgnemp
from ithelp180723_stan
, st1
)
select stid
, ename
from st2
join ithelp180723_emp e
on st2.asgnemp = e.id;
stid | ename
------+-------
03 | AA
05 | AA
01 | AA
11 | BB
02 | BB
12 | BB
09 | CC
04 | CC
07 | CC
10 | DD
06 | DD
13 | EE
08 | EE
(13 筆資料列)
-------------------
-- Round robin Style
with st1 as (
select count(*) as cnt
from ithelp180723_emp
), st2 as (
select stid
, ntile(st1.cnt::integer) over(order by mod(stid::integer - 1, st1.cnt::integer)) as asgnemp
from ithelp180723_stan
, st1
)
select stid
, ename
from st2
join ithelp180723_emp e
on st2.asgnemp = e.id
order by stid;
stid | ename
------+-------
01 | AA
02 | BB
03 | CC
04 | DD
05 | EE
06 | AA
07 | BB
08 | CC
09 | DD
10 | EE
11 | AA
12 | BB
13 | CC
(13 筆資料列)
我直接拿暐翰大大提供DDL來串SQL，
我的方法和大大差不多，只是都把資料都混再一起寫了XD
CREATE TABLE emp ([empid] varchar(7));
INSERT INTO emp ([empid]) VALUES ('AA'),('BB'),('CC'),('DD'),('EE');
CREATE TABLE Center ([CenterID] int);
INSERT INTO Center([CenterID]) VALUES('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('11'),('12'),('13');
DECLARE @count varchar(30) = (SELECT COUNT(*) _count FROM emp);
SELECT CenterID, empid
FROM center m
LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY empid) empNo ,* FROM emp) empData ON (m.CenterID%@count)+1 = empData.empNo
iThome鐵人賽
看影片追技術