-- 曲轴材料不良率分析（修复UNION排序问题）
WITH
-- 日期验证CTE（为每个表单独创建）
DateValidation_b AS (
SELECT
[日期] AS 原始日期,
CASE
WHEN ISDATE([日期]) = 1 THEN TRY_CAST([日期] AS DATE)
WHEN ISDATE(REPLACE([日期], '/', '-')) = 1 THEN TRY_CAST(REPLACE([日期], '/', '-') AS DATE)
ELSE NULL
END AS ValidDate
FROM [dbo].[V_报废不良]
),
DateValidation_p AS (
SELECT
[日期] AS 原始日期,
CASE
WHEN ISDATE([日期]) = 1 THEN TRY_CAST([日期] AS DATE)
WHEN ISDATE(REPLACE([日期], '/', '-')) = 1 THEN TRY_CAST(REPLACE([日期], '/', '-') AS DATE)
ELSE NULL
END AS ValidDate
FROM [dbo].[V_加工线每月生产台数]
),
-- 2025年月度数据
MonthlyData2025 AS (
SELECT
FORMAT(dv_b.ValidDate, 'yyyy-MM') AS 年月,
SUM(CONVERT(INT, b.数量)) AS 不良数量,
SUM(p.实绩) AS 生产台数,
-- 添加排序辅助列
FORMAT(dv_b.ValidDate, 'yyyy-MM') AS 原始年月,
2 AS SortOrder -- 月度数据排序值
FROM [dbo].[V_报废不良] b
INNER JOIN DateValidation_b dv_b
ON b.日期 = dv_b.原始日期
AND dv_b.ValidDate IS NOT NULL
INNER JOIN [dbo].[V_加工线每月生产台数] p
INNER JOIN DateValidation_p dv_p
ON p.日期 = dv_p.原始日期
AND dv_p.ValidDate IS NOT NULL
ON dv_b.ValidDate >= DATEADD(DAY, 1, EOMONTH(dv_p.ValidDate, -1))
AND dv_b.ValidDate <= EOMONTH(dv_p.ValidDate)
AND p.线别 = '曲轴'
WHERE
b.生产线 = '曲轴'
AND LEFT(b.报废原因, 4) = '材料不良'
AND b.产线 = 'TNGA12'
AND YEAR(dv_b.ValidDate) = 2025
GROUP BY FORMAT(dv_b.ValidDate, 'yyyy-MM'), dv_b.ValidDate
),
-- 2025年季度数据
QuarterlyData2025 AS (
SELECT
CONCAT('Q', DATEPART(QUARTER, dv_b.ValidDate), '-', YEAR(dv_b.ValidDate)) AS 年月,
SUM(CONVERT(INT, b.数量)) AS 不良数量,
SUM(p.实绩) AS 生产台数,
-- 添加排序辅助列
CONVERT(VARCHAR(7), dv_b.ValidDate, 120) AS 原始年月, -- 保留年月信息
1 AS SortOrder -- 季度数据排序值
FROM [dbo].[V_报废不良] b
INNER JOIN DateValidation_b dv_b
ON b.日期 = dv_b.原始日期
AND dv_b.ValidDate IS NOT NULL
INNER JOIN [dbo].[V_加工线每月生产台数] p
INNER JOIN DateValidation_p dv_p
ON p.日期 = dv_p.原始日期
AND dv_p.ValidDate IS NOT NULL
ON dv_b.ValidDate >= DATEADD(DAY, 1, EOMONTH(dv_p.ValidDate, -1))
AND dv_b.ValidDate <= EOMONTH(dv_p.ValidDate)
AND p.线别 = '曲轴'
WHERE
b.生产线 = '曲轴'
AND LEFT(b.报废原因, 4) = '材料不良'
AND b.产线 = 'TNGA12'
AND YEAR(dv_b.ValidDate) = 2025
GROUP BY
YEAR(dv_b.ValidDate),
DATEPART(QUARTER, dv_b.ValidDate),
dv_b.ValidDate
),
-- 2023-2024年度平均数据
YearlyAvgData AS (
SELECT
CONCAT(years.年度, '平均') AS 年月,
AVG(COALESCE(bad_data.月不良数量, 0)) AS 不良数量,
AVG(COALESCE(prod_data.月生产台数, 0)) AS 生产台数,
-- 添加排序辅助列
CONCAT(years.年度, '01') AS 原始年月, -- 伪日期用于排序
0 AS SortOrder -- 年度平均数据排序值
FROM (
SELECT 2023 AS 年度
UNION ALL SELECT 2024
) years
LEFT JOIN (
SELECT
YEAR(dv_b.ValidDate) AS 年度,
MONTH(dv_b.ValidDate) AS 月份,
SUM(CONVERT(INT, b.数量)) AS 月不良数量
FROM [dbo].[V_报废不良] b
INNER JOIN DateValidation_b dv_b
ON b.日期 = dv_b.原始日期
WHERE
dv_b.ValidDate IS NOT NULL
AND b.生产线 = '曲轴'
AND LEFT(b.报废原因, 4) = '材料不良'
AND b.产线 = 'TNGA12'
AND YEAR(dv_b.ValidDate) IN (2023, 2024)
GROUP BY YEAR(dv_b.ValidDate), MONTH(dv_b.ValidDate)
) bad_data ON years.年度 = bad_data.年度
LEFT JOIN (
SELECT
YEAR(dv_p.ValidDate) AS 年度,
MONTH(dv_p.ValidDate) AS 月份,
SUM(p.实绩) AS 月生产台数
FROM [dbo].[V_加工线每月生产台数] p
INNER JOIN DateValidation_p dv_p
ON p.日期 = dv_p.原始日期
WHERE
dv_p.ValidDate IS NOT NULL
AND p.线别 = '曲轴'
AND YEAR(dv_p.ValidDate) IN (2023, 2024)
GROUP BY YEAR(dv_p.ValidDate), MONTH(dv_p.ValidDate)
) prod_data
ON years.年度 = prod_data.年度
AND bad_data.月份 = prod_data.月份
GROUP BY years.年度
)
-- 最终结果集（正确排序）
SELECT
年月,
'材料不良-曲轴毛坯无齿' AS 报废原因,
不良数量,
生产台数,
CASE
WHEN 生产台数 > 0 THEN ROUND((不良数量 * 100.0) / 生产台数, 4)
ELSE 0
END AS 不良率,
CASE SortOrder
WHEN 0 THEN '年度平均'
WHEN 1 THEN '季度'
WHEN 2 THEN '月度'
END AS 数据类型
FROM (
-- 合并所有数据集
SELECT 年月, 不良数量, 生产台数, 原始年月, SortOrder FROM MonthlyData2025
UNION ALL
SELECT 年月, 不良数量, 生产台数, 原始年月, SortOrder FROM QuarterlyData2025
UNION ALL
SELECT 年月, 不良数量, 生产台数, 原始年月, SortOrder FROM YearlyAvgData
) combined
-- 使用SELECT列表中的列进行排序
ORDER BY
SortOrder, -- 排序优先级（0=年度平均,1=季度,2=月度）
CASE
WHEN SortOrder = 0 THEN 原始年月 -- 年度平均按年份排序
ELSE TRY_CAST(原始年月 + '-01' AS DATE) -- 转换为日期排序
END DESC;
报错：从字符串转换日期和/或时间时，转换失败
博客介绍了日期前加in还是on的区分方法，in后加季节、年份等，on后加具体某一天。还说明了二者含义、用法的不同，以及日期的读法，包括年份、月份、日的读法，同时提及英美时间表达的差异。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
