2

## 輪班分配員工在負責維修的站點

01需要2位員工
02需要3位員工
03需要2位員工

``````declare @Emp table(
Emp_ID int
,Emp_Name nvarchar(50)
)
insert into @Emp
values(1,'AA')
,(2,'BB')
,(3,'CC')
,(4,'DD')

declare @Site table(
Site_ID int
,Site_Name nvarchar(50)
,Site_Num int
)
insert into @Site
values(1,'01',2)
,(2,'02',3)
,(3,'03',2)

declare @Users table(
Users_Date date
,Users_Site nvarchar(50)
,Users_EmpID int
,Users_Emp nvarchar(50)
)
declare @StartDate date,@EndDate date,@SetDate date,@i int,@Count int
declare @j int,@Sub_Count int,@SetSite nvarchar(50),@SetNum int
set @StartDate = '2018/7/27'
set @EndDate = '2018/7/28'
set @Count = DateDiff(day,@StartDate,@EndDate)
set @i = 0
``````

SQL參考~

``````while(@i<=@Count)
begin

set @j = 1

select @Sub_Count = count(*)
from @Site

while(@j<=@Sub_Count)
begin

select @SetSite = Site_Name
,@SetNum = Site_Num
from @Site
where Site_ID = @j

insert into @Users
select top (@SetNum)
@SetDate
,@SetSite
,Emp_ID
,Emp_Name
from (
select *
,isNull((
select count(*)
from @Users
where Users_EmpID = Emp_ID
),0) as Emp_Num
from @Emp
) as k
order by Emp_Num

set @j = @j + 1
end

set @i = @i + 1
end

select Users_Date
,Users_Site
,Users_Emp
from @Users
``````

db<>fiddle 線上測試DEMO

rogeryao比較厲害~這次SQL直接算出循環排班~哈

01 站點是08:00~12:00
02 站點是13:00~17:00
03 站點是18:00~22:00

newkevin iT邦高手 1 級 ‧ 2018-07-28 12:45:11 檢舉

3-4個地方 離粉遠

newkevin iT邦高手 1 級 ‧ 2018-07-29 14:25:23 檢舉

super747 iT邦研究生 5 級 ‧ 2018-08-03 08:09:57 檢舉

### 3 個回答

1
rogeryao
iT邦高手 6 級 ‧ 2018-07-27 19:02:07

http://sqlfiddle.com/#!18/f8707c/2

CREATE TABLE Emp ([Num] varchar(2)) ;
INSERT INTO Emp ([Num]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ;

CREATE TABLE Center ([ID] varchar(2), [EmpQty] int) ;
INSERT INTO Center ([ID], [EmpQty]) VALUES ('01', 2), ('02', 3), ('03', 2) ;

CREATE TABLE WorkDate ([SchDate] varchar(10)) ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-27') ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-28') ;
INSERT INTO WorkDate ([SchDate]) VALUES ('2018-07-29') ;

select d.Row1,d.SchDate,d.ID,e.Num
from (
select ROW_NUMBER() OVER(ORDER BY f.SchDate,c.ID) AS Row1,f.SchDate,c.ID,c.EmpQty
from WorkDate as f ,(
select b.ID,b.EmpQty
from Center as a
Cross Apply Center as b
where 1=1
and a.EmpQty<=b.EmpQty
) as c
) as d
left join
(select (ROW_NUMBER() OVER(ORDER BY Num asc) %4 ) AS Row2,Num from Emp) as e
on d.Row1 % 4 = e.Row2

``````declare @Site table(
Site_Name nvarchar(2)
,Site_Num int
)
insert into @Site
values('01',2)
,('02',3)
,('03',2)
,('04',2)

select b.Site_Name
,b.Site_Num
from @Site as a
Cross apply @Site as b
where a.Site_Num <= b.Site_Num
``````

``````站點 人數
01 2
01 2
``````

``````declare @Site table(
Site_Name nvarchar(2)
,Site_Num int
)
insert into @Site
values('01',2)
,('02',3)
,('03',2)
,('04',2)

select Site_Name
,Site_Num
from (
select Row_Number()Over(Partition by b.Site_Name order by b.Site_Name) as Sort
,b.Site_Name
,b.Site_Num
from @Site as a
Cross apply @Site as b
) as k
where Sort <= Site_Num

--Cross apply 相同道理
select Site_Name
,Site_Num
from (
select Row_Number()Over(Partition by b.Site_Name order by b.Site_Name) as Sort
,b.Site_Name
,b.Site_Num
from @Site as a
, @Site as b
) as k
where Sort <= Site_Num

``````

rogeryao iT邦高手 6 級 ‧ 2018-07-28 13:01:40 檢舉

CREATE TABLE Emp ([Num] varchar(2)) ;
INSERT INTO Emp ([Num]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ;

# select d.Row1,d.SchDate,d.ID,e.Num from ( select ROW_NUMBER() OVER(ORDER BY f.SchDate,c.ID) AS Row1,f.SchDate,c.ID,c.EmpQty from WorkDate as f ,( select a.ID,a.EmpQty from Center as a left join ( select ROW_NUMBER() OVER(ORDER BY number asc) as RowNum from ( select number from master..spt_values ) as BaseData) as g on a.EmpQty>=g.RowNum ) as c ) as d left join (select (ROW_NUMBER() OVER(ORDER BY Num asc) %4 ) AS Row2,Num from Emp) as e on d.Row1 % 4 = e.Row2

http://sqlfiddle.com/#!18/c806f/2

rogeryao iT邦高手 6 級 ‧ 2018-07-28 14:52:09 檢舉

CREATE TABLE Emp ([Num] varchar(2)) ;
INSERT INTO Emp ([Num]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ;

CREATE TABLE Center ([ID] varchar(2), [EmpQty] int) ;
INSERT INTO Center ([ID], [EmpQty]) VALUES ('01', 2), ('02', 3), ('03', 2), ('04', 2), ('05', 4) ;

# select d.Row1,d.SchDate,d.ID,e.Num from ( select ROW_NUMBER() OVER(ORDER BY f.SchDate,c.ID) AS Row1,f.SchDate,c.ID,c.EmpQty from WorkDate as f ,( select a.ID,a.EmpQty from Center as a left join ( select ROW_NUMBER() OVER(ORDER BY Num asc) as RowNum from ( select Num from Emp ) as BaseData) as g on a.EmpQty>=g.RowNum ) as c ) as d left join (select (ROW_NUMBER() OVER(ORDER BY Num asc) %4 ) AS Row2,Num from Emp) as e on d.Row1 % 4 = e.Row2

http://sqlfiddle.com/#!18/b6c3c/37

1.利用master..spt_values資料呀~第一次看到這麼用@@~
2.利用員工數去配~第2點比較合理~不然同一個站點~員工就配了2次~

2

iT邦大師 1 級 ‧ 2018-07-27 14:53:40

### db<>fiddle 線上測試DEMO

TEST DDL:

``````CREATE TABLE Emp ([ID] varchar(2)) ; INSERT INTO Emp ([ID]) VALUES ('AA'), ('BB'), ('CC'), ('DD') ;

CREATE TABLE Center ([ID] int, [EmpQty] int) ; INSERT INTO Center ([ID], [EmpQty]) VALUES (01, 2), (02, 3), (03, 2) ;

CREATE TABLE Sch ([Datetime] datetime, [CenterID] int, [EmpID] varchar(2)) ;
``````

SP:

``````CREATE PROCEDURE SP_SCH_Center_ByEmp
@v_date datetime
AS

declare @v_firstEmp nvarchar(2) =  (
--找出昨天誰排班最少，藉由group + rownumber count,接著篩選出rank = 1的資料
select [EmpID]
from (
select [EmpID]
,row_number() over (order by count(1),[EmpID]) rnk
from Sch
where [Datetime] = (@v_date-1)
group by [EmpID]
) T where rnk = 1
);
declare @v_empCount int = (select count(1) from Emp);

--如果@v_firstEmp沒資料，以第一個員工為準
--如果@v_firstEmp有資料，以前天排最少班的第一個員工為準
if @v_firstEmp is null begin
select @v_firstEmp = (select top 1 [ID] from Emp);
end;

--先把當天Center資料展出來(使用遞迴)
WITH CTE AS (
SELECT 1 as Number,ID,EmpQty from Center
UNION ALL
SELECT Number+1,ID,EmpQty
FROM CTE
WHERE Number < EmpQty
)
--幫員工做一個排序
,CTE2 as (
select * , row_number() over (order by [ID] ) rnk from Emp
)
--Center資料做一個排序，特別使用站點排名 + @v_firstEmp員工的排名-2 做%的基準數
,CTE3 as (
SELECT @v_date [Datetime],ID [CenterID]
,( row_number() over (order by [ID])
+ (select rnk - 2 from CTE2 where [ID] = @v_firstEmp )
) rnk
FROM CTE
)
insert into sch
select T1.[Datetime],T1.[CenterID],T2.[ID]
from CTE3 T1
left join CTE2 T2 on ((T1.rnk) % (@v_empCount))  = T2.rnk-1;

``````

``````exec SP_SCH_Center_ByEmp '2018-07-27';
exec SP_SCH_Center_ByEmp '2018-07-28';

select * from SCH;
``````

Datetime CenterID EmpID
2018/7/27 1 AA
2018/7/27 1 BB
2018/7/27 2 CC
2018/7/27 2 DD
2018/7/27 2 AA
2018/7/27 3 BB
2018/7/27 3 CC
2018/7/28 1 DD
2018/7/28 1 AA
2018/7/28 2 BB
2018/7/28 2 CC
2018/7/28 2 DD
2018/7/28 3 AA
2018/7/28 3 BB
2018/7/29 1 CC
2018/7/29 1 DD
2018/7/29 2 AA
2018/7/29 2 BB
2018/7/29 2 CC
2018/7/29 3 DD
2018/7/29 3 AA

7/27
AA BB CC 都是2個站點
DD 是1個站點

(因為可能請假不在)

ok~我也把我寫的方式~放出來參考@@"

1

iT邦新手 2 級 ‧ 2018-07-27 22:51:46
``````create table ithelp180727_emp (
id serial primary key
,   ename text not null
);

insert into ithelp180727_emp (ename) values
('AA'), ('BB'), ('CC'), ('DD');

create table ithelp180727_stat (
sid serial primary key
,   matching int not null
);

insert into ithelp180727_stat values
(1, 2), (2, 3), (3, 2);

--
with   st1 as (
select max(matching) mm
from ithelp180727_stat
), st2 as (
select count(*) as cnt
from ithelp180727_emp
), st3 as (
select date '2018-07-27' + d as dt
, sid
from st1
, generate_series(1, st1.mm) as i
, generate_series(1, i) as j
, generate_series(0, 1) as d
, ithelp180727_stat s
where s.matching = i
), st4 as (
select *
, row_number() over(order by dt, sid) rn
from st3
), st5 as (
select dt, sid, rn
, ntile(st2.cnt::integer) over(order by mod(rn::integer - 1, st2.cnt::integer)) as asgnemp
from st4
, st2
)
select dt, sid, ename
from st5
, ithelp180727_emp e
where st5.asgnemp = e.id
order by dt, sid, rn
;

dt     | sid | ename
------------+-----+-------
2018-07-27 |   1 | AA
2018-07-27 |   1 | BB
2018-07-27 |   2 | CC
2018-07-27 |   2 | DD
2018-07-27 |   2 | AA
2018-07-27 |   3 | BB
2018-07-27 |   3 | CC
2018-07-28 |   1 | DD
2018-07-28 |   1 | AA
2018-07-28 |   2 | BB
2018-07-28 |   2 | CC
2018-07-28 |   2 | DD
2018-07-28 |   3 | AA
2018-07-28 |   3 | BB
(14 筆資料列)

----------------------

insert into ithelp180727_stat values (4,1);
-- 指令一樣
with   st1 as (
select max(matching) mm
from ithelp180727_stat
), st2 as (
select count(*) as cnt
from ithelp180727_emp
), st3 as (
select date '2018-07-27' + d as dt
, sid
from st1
, generate_series(1, st1.mm) as i
, generate_series(1, i) as j
, generate_series(0, 1) as d
, ithelp180727_stat s
where s.matching = i
), st4 as (
select *
, row_number() over(order by dt, sid) rn
from st3
), st5 as (
select dt, sid, rn
, ntile(st2.cnt::integer) over(order by mod(rn::integer - 1, st2.cnt::integer)) as asgnemp
from st4
, st2
)
select dt, sid, ename
from st5
, ithelp180727_emp e
where st5.asgnemp = e.id
order by dt, sid, rn
;

dt     | sid | ename
------------+-----+-------
2018-07-27 |   1 | AA
2018-07-27 |   1 | BB
2018-07-27 |   2 | CC
2018-07-27 |   2 | DD
2018-07-27 |   2 | AA
2018-07-27 |   3 | BB
2018-07-27 |   3 | CC
2018-07-27 |   4 | DD
2018-07-28 |   1 | AA
2018-07-28 |   1 | BB
2018-07-28 |   2 | CC
2018-07-28 |   2 | DD
2018-07-28 |   2 | AA
2018-07-28 |   3 | BB
2018-07-28 |   3 | CC
2018-07-28 |   4 | DD
(16 筆資料列)
``````