iT邦幫忙

3

平均分配員工在負責的維修站點

雖然這題自己解完@@..

來問問看各位大神的解法~/images/emoticon/emoticon01.gif

假設有5位員工為AA, BB, CC, DD, EE

維修站為01~13

https://ithelp.ithome.com.tw/upload/images/20180723/20061369OadtuxJdtT.png

試問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

因為答覆是暐翰先回的~所以我就把解答給他了~其他都非常感謝^^~

看更多先前的討論...收起先前的討論...
暐翰 iT邦大師 7 級 ‧ 2018-07-23 15:51:56 檢舉
每個站點只能一個人?
純真的人 iT邦研究生 1 級 ‧ 2018-07-23 16:00:21 檢舉
有1~13站~每個站都是1個專門負責的@@
純真的人 iT邦研究生 1 級 ‧ 2018-07-23 16:03:22 檢舉
因為那個站出了問題~就要找那個負責人@@a
員工固定只有五位嗎XD
純真的人 iT邦研究生 1 級 ‧ 2018-07-23 17:18:05 檢舉
暫定五位呀~
當然可以新增10~100位去分配~那個SQL會自動計算人數的^^a
哈哈哈 那我應該有一個很笨的方法,
之後大大可以PO在技術文章當留念啊XD
純真的人 iT邦研究生 1 級 ‧ 2018-07-23 22:51:25 檢舉
哈~~
4
暐翰
iT邦大師 7 級 ‧ 2018-07-23 18:18:13
最佳解答

使用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');

db<>fiddle


看更多先前的回應...收起先前的回應...
純真的人 iT邦研究生 1 級 ‧ 2018-07-23 18:39:59 檢舉

喔喔~~跟我的寫法不一樣~
明天再來貼我的寫法XD

fysh711426 iT邦研究生 5 級 ‧ 2018-07-23 21:32:11 檢舉

我也是想到用 %

我也是想到用%/images/emoticon/emoticon37.gif
方法和大大的差不多

暐翰 iT邦大師 7 級 ‧ 2018-07-23 22:26:07 檢舉
4
一級屠豬士
iT邦新手 3 級 ‧ 2018-07-23 21:22:20
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 筆資料列)
純真的人 iT邦研究生 1 級 ‧ 2018-07-23 22:28:53 檢舉

喔喔~增加亂數排列的方式~/images/emoticon/emoticon42.gif

2
神Q超人
iT邦新手 2 級 ‧ 2018-07-23 21:38:36

我直接拿暐翰大大提供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

解法在這裡

純真的人 iT邦研究生 1 級 ‧ 2018-07-23 22:30:32 檢舉

看起來少很多步驟呢~~/images/emoticon/emoticon81.gif

我要發表回答

立即登入回答