错误信息 SQL错误 (1305)：FUNCTION bj_database.calculate_shift_overlap does not exist
-- 删除已存在的视图
DROP VIEW IF EXISTS bj_database.view_work_order_list_filter;
-- 创建或替换视图
CREATE OR REPLACE VIEW bj_database.view_work_order_list_filter AS
WITH RECURSIVE
-- 生成10天时间维度（包含过去2天 + 未来7天）
date_dimension AS (
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS target_date
UNION ALL
SELECT DATE_ADD(target_date, INTERVAL 1 DAY)
FROM date_dimension
LIMIT 10
),
-- 生成所有班次维度（白班和夜班）
shift_dimension AS (
SELECT
target_date,
'白班' AS shift_type,
TIMESTAMP(target_date, '08:00:00') AS shift_start,
TIMESTAMP(target_date, '20:00:00') AS shift_end
FROM date_dimension
UNION ALL
SELECT
target_date,
'夜班',
TIMESTAMP(target_date, '20:00:00'),
TIMESTAMP(DATE_ADD(target_date, INTERVAL 1 DAY), '08:00:00')
FROM date_dimension
),
-- 获取工单基础信息（含责任人逻辑）
work_order_params AS (
SELECT
w.workOrderNo AS `指令`,
w.prodplanId AS `批次`,
w.lineName AS `线体`,
w.itemNo AS `单板代码`,
w.itemName AS `单板名称`,
w.taskQty AS `计划总数`,
w.outputQty AS `当前产出`,
GREATEST(TIMESTAMPDIFF(HOUR, w.scheduleStartDate, w.scheduleEndDate), 0.1) AS total_duration_hours,
COALESCE(
pc.计调员,
CASE
WHEN w.factoryName = '河源' AND w.craftSection IS NOT NULL AND LOCATE('背板', w.craftSection) > 0 THEN '徐捷如'
WHEN w.factoryName = '河源' AND w.itemName REGEXP '^PPC|^PA|^PD|^PN|^ZXSDR BS8800 C100 PSUB0' THEN '何东'
WHEN w.factoryName = '河源' AND w.externalType IN ('ES', 'MSR(R10)', 'CLS', 'IC', 'MSTP') THEN '钟慧珊'
WHEN w.factoryName = '河源' AND w.externalType = '5G-WS' THEN '无线'
WHEN w.factoryName = '河源' AND w.externalType IN ('CR', 'CSR(R10)', 'DCCS', 'IPRAN-C', 'ECSR') THEN '孙树冬'
WHEN w.factoryName = '河源' AND w.externalType IN (
'5G_CMxU', '5G_COLT', '5G_CONT', 'A10', 'CHS', 'CPE', 'DSL', 'EODN', 'J10', 'ONT', 'POL',
'PON_MxU', 'PON_OLT', 'PONG', 'POND', 'OTN', 'ITPS', 'MSAP', 'CLOUD', 'CT', 'ESI', 'IAD',
'iCDN', 'uSmartView', 'VCS', 'VCT'
) THEN '肖应仲'
WHEN w.factoryName = '河源' AND w.externalType IN ('C-SPN', 'IPRAN-A', 'SPN', 'D-SPN', 'IPRAN', 'IPTN') THEN '徐捷如'
WHEN w.factoryName = '河源' AND w.externalType IN ('CDCG', 'DC_P', 'DPS', 'POWER', 'PS_S', 'SSE', 'TSE', '3GPLAT', 'DC_S', 'SE') THEN '祁思佳'
WHEN w.factoryName = '河源' AND w.externalType = 'RRS' THEN '何东'
WHEN w.factoryName = '深圳' AND w.externalType IN ('ITPS', 'RSS') THEN '江启一'
WHEN w.factoryName = '深圳' AND w.externalType IN ('OTN', 'WDM') THEN '杨港会'
WHEN w.factoryName = '深圳' AND w.externalType = 'CP' THEN '周旭升'
ELSE '未分配'
END
) AS `责任人`,
w.factoryName AS `工厂`,
w.craftSection AS `主工序`,
w.scheduleStartDate AS `计划开始时间`,
w.scheduleEndDate AS `计划结束时间`,
IFNULL(l.`线体`, '') != '' AS is_valid_line
FROM bj_database.work_order_daily w
LEFT JOIN (
SELECT DISTINCT `单板代码`, `计调员`
FROM bj_database.part_category
) pc ON w.itemNo = pc.`单板代码`
LEFT JOIN (
SELECT DISTINCT `线体`
FROM bj_database.lineteamleader
) l ON w.lineName = l.`线体`
WHERE w.scheduleStartDate IS NOT NULL
AND w.scheduleEndDate IS NOT NULL
AND w.taskQty > 0
AND w.itemNo IS NOT NULL
AND w.itemName IS NOT NULL
),
planned_by_shift AS (
SELECT
wp.`指令`,
s.target_date,
s.shift_type,
ROUND(
COALESCE(
(CAST(wp.`计划总数` AS DECIMAL(10,2)) *
GREATEST(
0,
TIMESTAMPDIFF(MINUTE,
GREATEST(s.shift_start, wp.`计划开始时间`),
LEAST(s.shift_end, wp.`计划结束时间`)
) / 60.0
) / wp.total_duration_hours
), 0
), 2
) AS plannedOutput
FROM work_order_params wp
JOIN shift_dimension s
ON wp.`计划开始时间` < s.shift_end
AND wp.`计划结束时间` > s.shift_start
),
-- 获取工单的历史实际产出
actual_by_shift AS (
SELECT
wp.`指令`,
s.target_date,
s.shift_type,
SUM(wp.`当前产出`) AS actualOutput,
ROUND(
COALESCE(
(CAST(MAX(wp.`计划总数`) AS DECIMAL(10,2)) - CAST(MAX(wp.`当前产出`) AS DECIMAL(10,2))) *
calculate_shift_overlap(
s.shift_start,
s.shift_end,
wp.`计划开始时间`,
wp.`计划结束时间`
),
0
), 2
) AS targetOutput
FROM work_order_params wp
JOIN shift_dimension s
ON wp.`计划开始时间` < s.shift_end
AND wp.`计划结束时间` > s.shift_start
AND wp.`当前产出` > 0
AND s.target_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND CURDATE()
GROUP BY wp.`指令`, s.target_date, s.shift_type
),
-- 合并计划与实际产出
combined_by_shift AS (
SELECT
p.`指令`,
p.target_date,
p.shift_type,
p.plannedOutput,
COALESCE(a.actualOutput, 0) AS actualOutput,
COALESCE(a.targetOutput, 0) AS targetOutput
FROM planned_by_shift p
LEFT JOIN actual_by_shift a
ON p.`指令` = a.`指令`
AND p.target_date = a.target_date
AND p.shift_type = a.shift_type
),
-- 最终输出：按时间班次展示完整排产数据
final_output AS (
SELECT
s.target_date AS `日期`,
s.shift_type AS `班次类型`,
COALESCE(wp.`批次`, '空排产') AS `批次`,
COALESCE(wp.`线体`, '未分配') AS `线体`,
COALESCE(wp.`单板代码`, '无') AS `单板代码`,
COALESCE(wp.`单板名称`, '无') AS `单板名称`,
COALESCE(wp.`计划总数`, 0) AS `计划总数`,
COALESCE(wp.`当前产出`, 0) AS `当前产出`,
COALESCE(wp.`计划总数` - wp.`当前产出`, 0) AS `剩余数量`,
COALESCE(wp.`责任人`, '未分配') AS `责任人`,
COALESCE(wp.`工厂`, '未知') AS `工厂`,
COALESCE(wp.`主工序`, '无') AS `主工序`,
COALESCE(wp.total_duration_hours, 0) AS `总工时(小时)`,
wp.is_valid_line AS `是否有效线体`,
-- 前天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '白班' THEN cs.actualOutput END), 0) AS `前天白班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '白班' THEN cs.targetOutput END), 0) AS `前天白班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `前天白班计划产出`,
-- 昨天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '白班' THEN cs.actualOutput END), 0) AS `昨天白班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '白班' THEN cs.targetOutput END), 0) AS `昨天白班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `昨天白班计划产出`,
-- 今天 白班
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '白班' THEN cs.actualOutput END), 0) AS `今天白班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '白班' THEN cs.targetOutput END), 0) AS `今天白班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `今天白班计划产出`,
-- 今天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '夜班' THEN cs.actualOutput END), 0) AS `今天夜班实际产出`,
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '夜班' THEN cs.targetOutput END), 0) AS `今天夜班目标产出`,
COALESCE(MAX(CASE WHEN cs.target_date = CURDATE() AND cs.shift_type = '夜班' THEN cs.plannedOutput END), 0) AS `今天夜班计划产出`,
-- 明天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `明天白班计划产出`,
-- 明天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND cs.shift_type = '夜班' THEN cs.plannedOutput END), 0) AS `明天夜班计划产出`,
-- 后天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `后天白班计划产出`,
-- 后天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 2 DAY) AND cs.shift_type = '夜班' THEN cs.plannedOutput END), 0) AS `后天夜班计划产出`,
-- 第3天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 3 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `第3天白班计划产出`,
-- 第3天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 3 DAY) AND cs.shift_type = '夜班' THEN cs.plannedOutput END), 0) AS `第3天夜班计划产出`,
-- 第4天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 4 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `第4天白班计划产出`,
-- 第4天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 4 DAY) AND cs.shift_type = '夜班' THEN cs.plannedOutput END), 0) AS `第4天夜班计划产出`,
-- 第5天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 5 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `第5天白班计划产出`,
-- 第5天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 5 DAY) AND cs.shift_type = '夜班' THEN cs.plannedOutput END), 0) AS `第5天夜班计划产出`,
-- 第6天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 6 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `第6天白班计划产出`,
-- 第6天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 6 DAY) AND cs.shift_type = '夜班' THEN cs.plannedOutput END), 0) AS `第6天夜班计划产出`,
-- 第7天 白班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 7 DAY) AND cs.shift_type = '白班' THEN cs.plannedOutput END), 0) AS `第7天白班计划产出`,
-- 第7天 夜班
COALESCE(MAX(CASE WHEN cs.target_date = DATE_ADD(CURDATE(), INTERVAL 7 DAY) AND cs.shift_type = '夜班' THEN cs.plannedOutput END), 0) AS `第7天夜班计划产出`
FROM shift_dimension s
LEFT JOIN work_order_params wp ON 1=1
LEFT JOIN combined_by_shift cs
ON wp.`指令` = cs.`指令`
AND s.target_date = cs.target_date
AND s.shift_type = cs.shift_type
GROUP BY s.target_date, s.shift_type, wp.`批次`, wp.`线体`, wp.`单板代码`, wp.`单板名称`, wp.`计划总数`, wp.`当前产出`, wp.`责任人`, wp.`工厂`, wp.`主工序`, wp.total_duration_hours, wp.is_valid_line
)
-- 最终输出
SELECT * FROM final_output
ORDER BY `日期`, `班次类型`;
博主前两天重装了系统，然而昨天晚上进行调试时，却怎么都无法调试成功，博主为此感到郁闷。
扫一扫
7523
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
