设计班次

原贴： 点击打开链接

USE tempdb
GO
IF OBJECT_ID('bucket_data') IS NOT NULL DROP TABLE bucket_data
IF OBJECT_ID('schedule_data') IS NOT NULL DROP TABLE schedule_data
IF OBJECT_ID('schedule_bucket_mid') IS NOT NULL DROP TABLE schedule_bucket_mid
--1. 桶表
CREATE TABLE bucket_data (
	bucketNO VARCHAR(2) NOT NULL PRIMARY KEY,	--桶号
	bucketWeight INT NOT NULL			--桶重
)
--2. 班次表
CREATE TABLE schedule_data (
	scheduleId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	currDate DATETIME NOT NULL,		--当前日期
	flag INT NOT NULL				--班次 早班1，晚班2
)
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_schedule_data_currDate_flag ON schedule_data(currDate,flag)

--3. 班次桶中间表
CREATE TABLE schedule_bucket_mid (
	id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	scheduleId INT NOT NULL,		--班次ID
	bucketNO VARCHAR(2) NOT NULL,	--桶号
	weight INT NOT NULL				--总重
)

------- 插入测试数据
SET NOCOUNT ON
INSERT INTO bucket_data VALUES('A',5)
INSERT INTO bucket_data VALUES('B',5)
INSERT INTO bucket_data VALUES('C',2)
INSERT INTO bucket_data VALUES('D',3)
INSERT INTO bucket_data VALUES('E',3)
INSERT INTO bucket_data VALUES('F',2)

INSERT INTO schedule_data(currDate,flag) VALUES('2017-10-12', 1)
INSERT INTO schedule_data(currDate,flag) VALUES('2017-10-12', 2)
INSERT INTO schedule_data(currDate,flag) VALUES('2017-10-13', 1)

INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(1,'A',10)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(1,'B',15)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(1,'C',20)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(2,'A',20)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(2,'B',30)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(2,'D',20)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(2,'E',10)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(2,'F',10)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(3,'B',50)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(3,'C',30)
INSERT INTO schedule_bucket_mid(scheduleId,	bucketNO,[weight]) VALUES(3,'D',40)

--结果视图1
IF OBJECT_ID('view_result') IS NOT NULL DROP VIEW view_result
GO
CREATE VIEW view_result
AS
SELECT a.currDate AS [日期]
	,CASE a.flag WHEN 1 THEN '早班' WHEN 2 then '晚班' END AS [班次]
	,STUFF( (SELECT ','+b.bucketNO 
			 FROM schedule_bucket_mid AS B WHERE b.scheduleId=a.scheduleId FOR XML PATH('')),1,1,'' ) AS [桶号]
	,STUFF( (SELECT ','+CAST(b.[weight] AS VARCHAR(50)) 
			 FROM schedule_bucket_mid AS B WHERE b.scheduleId=a.scheduleId FOR XML PATH('')),1,1,'' ) AS [总重]
	,STUFF( (SELECT ','+CAST(c.bucketWeight AS VARCHAR(50)) 
			 FROM schedule_bucket_mid AS B INNER JOIN bucket_data AS c ON b.bucketNO=c.bucketNO 
			 WHERE b.scheduleId=a.scheduleId FOR XML PATH('')),1,1,'' ) AS [桶重]
	FROM schedule_data AS A
GO
--结果视图2
--每个班次的实际产量
IF OBJECT_ID('view_result2') IS NOT NULL DROP VIEW view_result2
GO
CREATE VIEW view_result2
AS
WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY b.bucketNO ORDER BY a.currDate,a.flag) AS rid
,a.scheduleId
,a.currDate
,a.flag
,b.bucketNO
,b.[weight]
FROM schedule_data A INNER JOIN schedule_bucket_mid AS B ON a.scheduleId=b.scheduleId
)
,cte2 AS(
SELECT a.scheduleId,a.currDate,a.flag,a.bucketNO,a.[weight]-ISNULL((
	SELECT b.[weight] FROM cte AS b WHERE a.rid=b.rid+1 AND a.bucketNO=b.bucketNO
),0) AS [weight]
FROM cte A
)
SELECT a.currDate AS [日期]
,CASE a.flag WHEN 1 THEN '早班' WHEN 2 then '晚班' END AS [班次]
,STUFF(( SELECT ','+b.bucketNO FROM cte2 b WHERE a.scheduleId=b.scheduleId FOR XML PATH('') ),1,1,'') AS [桶号]
,STUFF(( SELECT ','+CAST(b.[weight] AS VARCHAR(50)) FROM cte2 b WHERE a.scheduleId=b.scheduleId FOR XML PATH('') ),1,1,'') AS [实际产量]
,STUFF(( SELECT ','+CAST(c.bucketWeight AS VARCHAR(50)) FROM cte2 b INNER JOIN bucket_data AS c ON c.bucketNO=b.bucketNO 
         WHERE a.currDate=b.currDate AND a.flag=b.flag FOR XML PATH('') ),1,1,'') AS [桶重]
FROM schedule_data AS a
GO

SELECT * FROM view_result
/*
日期                    班次   桶号        总重                 桶重
----------------------- ---- ----------   ------------------  -------------
2017-10-12              早班   A,B,C         10,15,20           5,5,2
2017-10-12              晚班   A,B,D,E,F     20,30   ,20,10,10  5,5,3,3,2
2017-10-13              早班   B,C,D            50,30,40        5,2,3
*/
SELECT * FROM view_result2
/*
日期                    班次   桶号        实际产量             桶重
----------------------- ---- ----------   ------------------  -------------
2017-10-12              早班   A,B,C         10,15,20           5,5,2
2017-10-12              晚班   A,B,D,E,F     10,15,   20,10,10  5,5,3,3,2
2017-10-13              早班   B,C,D            20,10,20        5,2,3
*/