-- 公用日期表CTE（2025年各月份）
WITH dom AS (
SELECT CONVERT(VARCHAR(6), DATEADD(MONTH, number, '2022-01-01'), 112) AS DateOfMonth
FROM master..spt_values
WHERE type = 'P'
AND CONVERT(VARCHAR(6), DATEADD(MONTH, number, '2022-01-01'), 112) LIKE '2025%'
)
-- 缸体产线数据
SELECT '缸体' AS 产线, rn, 年月, 不良类型, 不良数, 产量, 不良率
FROM (
-- 缸体产线原始SQL（保持内容不变）
SELECT '1' rn, '22年' AS 年月, '高压铸造' 不良类型, 83 不良数, 23564 产量, 0.10 不良率
UNION ALL
SELECT '1', '23年', '高压铸造', 113, 31025, 0.16
UNION ALL
SELECT '1', '24年', '高压铸造', 113, 31025, 0.141
UNION ALL
SELECT '2', '01', '高压铸造', 10, 0, 0.015
UNION ALL
SELECT '2', '04', '高压铸造', 25, 0, 0.02
UNION ALL
SELECT '3', '平均', '高压铸造', 1, 31025, 0.0153
UNION ALL
SELECT '2', RIGHT(dom.DateOfMonth, 2), td.不良类型, td.不良数, td.产量, CONVERT(DECIMAL(12,3), td.不良率*100)
FROM dom
LEFT JOIN (
SELECT '2' rn, '高压铸造' AS 不良类型, 年+月 AS 年月,
CONVERT(DECIMAL(12,0), 合格数) AS 产量,
CONVERT(DECIMAL(12,0), 不良数) AS 不良数,
CASE WHEN 合格数 = 0 THEN 0 ELSE CONVERT(DECIMAL(12,7), 不良数/合格数) END AS 不良率
FROM (
SELECT duty, 年, 月, SUM(合格数) 合格数, SUM(不良数) 不良数
FROM [192.168.10.116].GLKANBAN.dbo.V_铸造日报_C
WHERE duty = '高压铸造' AND 年 = '2025'
GROUP BY duty, 年, 月
) A
UNION ALL
SELECT '2', '返修品', t1.年月, t1.合格数 + t1.不良数 + 数量, 数量,
CONVERT(DECIMAL(12,7), 数量/(合格数))
FROM (
SELECT 年+月 AS 年月, SUM(合格数) 合格数, SUM(不良数) AS 不良数
FROM [192.168.10.116].GLKANBAN.dbo.V_铸造日报_C
WHERE duty = '高压铸造' AND 年 = '2025'
GROUP BY 年, 月
) t1
LEFT JOIN (
SELECT LEFT(REPLACE(日期, '/', ''),6) AS 年月,
ISNULL(SUM(数量), 1) AS 数量
FROM [192.168.10.116].GLKANBAN.dbo.T_铸造_缸体缸盖返修记录表
WHERE 类型 = '缸体毛坯' AND LEFT(日期, 4) = '2025'
GROUP BY LEFT(REPLACE(日期, '/', ''),6)
) t2 ON t1.年月 = t2.年月
WHERE t2.数量 IS NOT NULL
) td ON dom.DateOfMonth = td.年月
UNION ALL
SELECT '2', '平均', '高压铸造', CONVERT(DECIMAL(12,0), 不良数)/5,
CONVERT(DECIMAL(12,0), 合格数),
CASE WHEN 合格数 = 0 THEN 0 ELSE CONVERT(DECIMAL(12,7), 不良数/合格数)*100 END
FROM dom
LEFT JOIN (
SELECT '2' rn, '缸体报废' AS 不良类型, REPLACE(年月, '/', '') AS 年月,
SUM(成品) AS 产量, SUM(不良数) AS 不良数,
CASE WHEN SUM(成品) = 0 THEN 0 ELSE SUM(不良数)/SUM(成品) END AS 不良率
FROM (
SELECT LEFT(生产日期, 7) AS 年月,
SUM(CONVERT(DECIMAL(4,0), 完成品)) AS 成品,
SUM(CONVERT(DECIMAL(3,0), 不良数)) AS 不良数
FROM [192.168.10.116].GLKANBAN.[dbo].[V_铸造日报_M]
WHERE 生产日期 > '2023/12/31' AND 产线 = '缸体线'
GROUP BY LEFT(生产日期, 7)
) A
GROUP BY 年月
UNION ALL
SELECT '2', '返修品', t1.年月, t1.台数, t2.数量,
CONVERT(DECIMAL(12,7), t2.数量/(t1.台数 + t2.数量))
FROM (
SELECT REPLACE(年月, '/', '') AS 年月, SUM(成品) AS 台数
FROM (
SELECT LEFT(生产日期, 7) AS 年月,
SUM(CONVERT(DECIMAL(4,0), 完成品)) AS 成品
FROM [192.168.10.116].GLKANBAN.[dbo].[V_铸造日报_M]
WHERE LEFT(生产日期, 4) = '2025' AND 产线 = '缸体线'
GROUP BY LEFT(生产日期, 7)
) B
GROUP BY 年月
) t1
LEFT JOIN (
SELECT LEFT(REPLACE(日期, '/', ''),6) AS 年月,
ISNULL(COUNT(*), 0) AS 数量
FROM [192.168.10.116].[GLKANBAN].[dbo].[T_铸造_缸体缸盖返修记录表]
WHERE 类型 = '缸体' AND LEFT(日期, 4) = '2025'
GROUP BY LEFT(REPLACE(日期, '/', ''),6)
) t2 ON t1.年月 = t2.年月
WHERE t2.数量 IS NOT NULL
) td ON dom.DateOfMonth = td.年月
UNION ALL
) AS 缸体数据
UNION ALL -- 关键合并点
-- 缸盖产线数据
SELECT '缸盖' AS 产线, rn, 年月, 不良类型, 不良数, 产量, 不良率
FROM (
SELECT '1' rn, '22年' AS 年月, '低压铸造' AS 不良类型, 400 AS 不良数, 43782 AS 产量, 1.11 AS 不良率
UNION ALL
SELECT '1' rn, '22年' AS 年月, '返修品' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0 AS 不良率
UNION ALL
SELECT '1' rn, '23年' AS 年月, '低压铸造' AS 不良类型, 209 AS 不良数, 68233 AS 产量, 0.63 AS 不良率
UNION ALL
SELECT '1' rn, '23年' AS 年月, '返修品' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0 AS 不良率
UNION ALL
SELECT '1' rn, '24年' AS 年月, '低压铸造' AS 不良类型, 209 AS 不良数, 68233 AS 产量, 0.613 AS 不良率
UNION ALL
SELECT '1' rn, '24年' AS 年月, '返修品' AS 不良类型, 121 AS 不良数, 609591 AS 产量, 0.23 AS 不良率
UNION ALL
SELECT '2' rn, '01' AS 年月, '低压铸造' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0.02 AS 不良率
UNION ALL
SELECT '2' rn, '03' AS 年月, '低压铸造' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0.03 AS 不良率
UNION ALL
SELECT '2' rn, '04' AS 年月, '低压铸造' AS 不良类型, 1 AS 不良数, 1 AS 产量, -0.02 AS 不良率
UNION ALL
SELECT '2' rn, '07' AS 年月, '低压铸造' AS 不良类型, 1 AS 不良数, 1 AS 产量, -0.063 AS 不良率
UNION ALL
SELECT '2' rn, '09' AS 年月, '返修品' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '10' AS 年月, '返修品' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0 AS 不良率
UNION ALL
SELECT '3' rn, '平均' AS 年月, '低压铸造' AS 不良类型, 0 AS 不良数, 68233 AS 产量, 0.026 AS 不良率
UNION ALL
SELECT '3' rn, '平均' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 1 AS 产量, 0.03 AS 不良率
UNION ALL
SELECT '2' rn, RIGHT(dom.DateOfMonth, 2) AS 年月, 不良类型, 不良数, 产量, CONVERT(DECIMAL(12, 3), 不良率 * 100) AS 不良率
FROM dom
LEFT JOIN (
SELECT '2' rn, 产品 AS 不良类型, CONCAT(年, 月) AS 年月, CONVERT(DECIMAL(12, 0), 合格数) AS 产量,
CONVERT(DECIMAL(12, 0), 不良数) AS 不良数,
CASE WHEN 合格数 = 0 THEN 0
ELSE CONVERT(DECIMAL(12, 7), 不良数 / 合格数)
END AS 不良率
FROM (
SELECT duty AS 产品, 年, 月, SUM(合格数) AS 合格数, SUM(不良数) AS 不良数
FROM [192.168.10.116].GLKANBAN.dbo.V_铸造日报_C
WHERE duty = '低压铸造'
GROUP BY duty, 年, 月
) A
WHERE 年 = '2025'
UNION ALL
SELECT '2' rn, 类型 AS 不良类型, t1.年月, 合格数, 数量,
CONVERT(DECIMAL(12, 7), 数量 / 合格数) AS 不良率
FROM (
SELECT CONCAT(年, 月) AS 年月, SUM(合格数) AS 合格数, SUM(不良数) AS 不良数
FROM [192.168.10.116].GLKANBAN.dbo.V_铸造日报_C
WHERE duty = '低压铸造'
GROUP BY duty, 年, 月
) t1
LEFT JOIN (
SELECT LEFT(REPLACE(日期, '/', ''), 6) AS 年月,
ISNULL(SUM(数量), 1) AS 数量, '返修品' AS 类型
FROM [192.168.10.116].GLKANBAN.dbo.T_铸造_缸体缸盖返修记录表
WHERE 类型 = '缸盖毛坯' AND LEFT(日期, 4) = '2025'
GROUP BY LEFT(REPLACE(日期, '/', ''), 6)
) t2 ON t1.年月 = t2.年月
WHERE 类型 IS NOT NULL
) td ON dom.DateOfMonth = td.年月
UNION ALL
SELECT '2' rn, 月 AS 年月, 产品 AS 不良类型,
CONVERT(DECIMAL(12, 0), 不良数) / 7.3 AS 平均不良数,
CONVERT(DECIMAL(12, 0), 合格数) AS 合格数,
CASE WHEN 合格数 = 0 THEN 0
ELSE CONVERT(DECIMAL(12, 7), 不良数 / 合格数) * 100
END AS 平均不良率
FROM (
SELECT duty AS 产品, 年, '平均' AS 月,
SUM(合格数) AS 合格数, SUM(不良数) AS 不良数
FROM [192.168.10.116].GLKANBAN.dbo.V_铸造日报_C
WHERE duty = '低压铸造'
GROUP BY duty, 年
) A
WHERE 年 = '2025'
) AS low_pressure
UNION ALL
SELECT '缸盖' AS 产线, rn, 年月, 不良类型, 不良数, 产量, 不良率
FROM (
SELECT '1' rn, '22年' AS 年月, '低压铸造' AS 不良类型, 400 AS 不良数, 43782 AS 产量, 1.11 AS 不良率
UNION ALL
-- 缸盖产线数据 (原缸盖线)
SELECT '缸盖' AS 产线, rn, 年月, 不良类型, 不良数, 产量, 不良率
FROM (
SELECT '1' rn, '22年' AS 年月, '缸盖报废' AS 不良类型, 357 AS 不良数, 43782 AS 产量, 0.65 AS 不良率
UNION ALL
SELECT '1' rn, '22年' AS 年月, '返修品' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0 AS 不良率
UNION ALL
SELECT '1' rn, '23年' AS 年月, '缸盖报废' AS 不良类型, 160 AS 不良数, 68233 AS 产量, 0.29 AS 不良率
UNION ALL
SELECT '1' rn, '23年' AS 年月, '返修品' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0 AS 不良率
UNION ALL
SELECT '1' rn, '24年' AS 年月, '缸盖报废' AS 不良类型, 160 AS 不良数, 68233 AS 产量, 0.308 AS 不良率
UNION ALL
SELECT '1' rn, '24年' AS 年月, '返修品' AS 不良类型, 94 AS 不良数, 564760 AS 产量, 0.214 AS 不良率
UNION ALL
SELECT '2' rn, '01' AS 年月, '缸盖报废' AS 不良类型, 1 AS 不良数, 0 AS 产量, 0.015 AS 不良率
UNION ALL
SELECT '2' rn, '04' AS 年月, '缸盖报废' AS 不良类型, 45 AS 不良数, 0 AS 产量, 0.03 AS 不良率
UNION ALL
SELECT '2' rn, '07' AS 年月, '缸盖报废' AS 不良类型, 45 AS 不良数, 0 AS 产量, 0.05 AS 不良率
UNION ALL
SELECT '2' rn, '08' AS 年月, '缸盖报废' AS 不良类型, 45 AS 不良数, 0 AS 产量, 1.0 AS 不良率
UNION ALL
SELECT '2' rn, '02' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '04' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '08' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '09' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '10' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '3' rn, '平均' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 1 AS 产量, 0.001 AS 不良率
UNION ALL
SELECT '2' rn, RIGHT(dom.DateOfMonth, 2) AS 年月, 不良类型, 不良数, 产量, CONVERT(DECIMAL(12, 3), 不良率 * 100) AS 不良率
FROM dom
LEFT JOIN (
SELECT '2' AS rn, '缸盖报废' AS 不良类型, REPLACE(年月, '/', '') AS 年月,
SUM(成品) AS 产量, SUM(不良数) AS 不良数,
CASE WHEN SUM(成品) = 0 THEN 0
ELSE SUM(不良数) / SUM(成品)
END AS 不良率
FROM (
SELECT
CASE
WHEN 机型 LIKE '%2.0%' THEN 'TNGA2.0L'
WHEN 机型 LIKE '%1.5%' THEN 'TNGA1.5L'
ELSE 'TNGA2.5L'
END AS LINE,
LEFT(生产日期, 7) AS 年月,
SUM(CONVERT(DECIMAL(4, 0), 完成品)) AS 成品,
SUM(CONVERT(DECIMAL(3, 0), 不良数)) AS 不良数
FROM [192.168.10.116].GLKANBAN.[dbo].[V_铸造日报_M]
WHERE 生产日期 > '2023-12-31' AND 产线 = '缸盖线'
GROUP BY LEFT(生产日期, 7), 机型
) A
GROUP BY 年月 -- 此处保持所有缸盖产线原始UNION ALL语句...
SELECT '2' rn, RIGHT(dom.DateOfMonth, 2) AS 年月, 不良类型, 不良数, 产量, CONVERT(DECIMAL(12, 3), 不良率 * 100) AS 不良率
FROM dom
LEFT JOIN (...) td ON dom.DateOfMonth = td.年月
) AS low_pressure
UNION ALL
SELECT '缸盖' AS 产线, rn, 年月, 不良类型, 不良数, 产量, 不良率
FROM (
SELECT '1' rn, '22年' AS 年月, '缸盖报废' AS 不良类型, 357 AS 不良数, 43782 AS 产量, 0.65 AS 不良率
UNION ALL
-- 缸盖产线数据 (原缸盖线)
SELECT '缸盖' AS 产线, rn, 年月, 不良类型, 不良数, 产量, 不良率
FROM (
SELECT '1' rn, '22年' AS 年月, '缸盖报废' AS 不良类型, 357 AS 不良数, 43782 AS 产量, 0.65 AS 不良率
UNION ALL
SELECT '1' rn, '22年' AS 年月, '返修品' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0 AS 不良率
UNION ALL
SELECT '1' rn, '23年' AS 年月, '缸盖报废' AS 不良类型, 160 AS 不良数, 68233 AS 产量, 0.29 AS 不良率
UNION ALL
SELECT '1' rn, '23年' AS 年月, '返修品' AS 不良类型, 1 AS 不良数, 1 AS 产量, 0 AS 不良率
UNION ALL
SELECT '1' rn, '24年' AS 年月, '缸盖报废' AS 不良类型, 160 AS 不良数, 68233 AS 产量, 0.308 AS 不良率
UNION ALL
SELECT '1' rn, '24年' AS 年月, '返修品' AS 不良类型, 94 AS 不良数, 564760 AS 产量, 0.214 AS 不良率
UNION ALL
SELECT '2' rn, '01' AS 年月, '缸盖报废' AS 不良类型, 1 AS 不良数, 0 AS 产量, 0.015 AS 不良率
UNION ALL
SELECT '2' rn, '04' AS 年月, '缸盖报废' AS 不良类型, 45 AS 不良数, 0 AS 产量, 0.03 AS 不良率
UNION ALL
SELECT '2' rn, '07' AS 年月, '缸盖报废' AS 不良类型, 45 AS 不良数, 0 AS 产量, 0.05 AS 不良率
UNION ALL
SELECT '2' rn, '08' AS 年月, '缸盖报废' AS 不良类型, 45 AS 不良数, 0 AS 产量, 1.0 AS 不良率
UNION ALL
SELECT '2' rn, '02' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '04' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '08' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '09' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '2' rn, '10' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 0 AS 产量, 0 AS 不良率
UNION ALL
SELECT '3' rn, '平均' AS 年月, '返修品' AS 不良类型, 0 AS 不良数, 1 AS 产量, 0.001 AS 不良率
UNION ALL
SELECT '2' rn, RIGHT(dom.DateOfMonth, 2) AS 年月, 不良类型, 不良数, 产量, CONVERT(DECIMAL(12, 3), 不良率 * 100) AS 不良率
FROM dom
LEFT JOIN (
SELECT '2' AS rn, '缸盖报废' AS 不良类型, REPLACE(年月, '/', '') AS 年月,
SUM(成品) AS 产量, SUM(不良数) AS 不良数,
CASE WHEN SUM(成品) = 0 THEN 0
ELSE SUM(不良数) / SUM(成品)
END AS 不良率
FROM (
SELECT
CASE
WHEN 机型 LIKE '%2.0%' THEN 'TNGA2.0L'
WHEN 机型 LIKE '%1.5%' THEN 'TNGA1.5L'
ELSE 'TNGA2.5L'
END AS LINE,
LEFT(生产日期, 7) AS 年月,
SUM(CONVERT(DECIMAL(4, 0), 完成品)) AS 成品,
SUM(CONVERT(DECIMAL(3, 0), 不良数)) AS 不良数
FROM [192.168.10.116].GLKANBAN.[dbo].[V_铸造日报_M]
WHERE 生产日期 > '2023-12-31' AND 产线 = '缸盖线'
GROUP BY LEFT(生产日期, 7), 机型
) A
GROUP BY 年月
SELECT '2' rn, RIGHT(dom.DateOfMonth, 2) AS 年月, 不良类型, 不良数, 产量, CONVERT(DECIMAL(12, 3), 不良率 * 100) AS 不良率
FROM dom
LEFT JOIN (...) td ON dom.DateOfMonth = td.年月
) AS head_line
) AS 缸盖数据
-- 最终排序
ORDER BY 产线, rn, 年月;
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
