优化此过程ALTER PROCEDURE us_MatDynReqTable
@start_date DATE, -- 报表起始日期
@end_date DATE, -- 报表结束日期
@part_number NVARCHAR(50) = NULL -- 料号查询条件（可选，为空时查全部）
AS
BEGIN
SET NOCOUNT ON; -- 不返回计数信息，提升性能
-- 变量声明：存储动态日期列（用于PIVOT）
DECLARE @pivot_dates NVARCHAR(MAX) = '';
-- 步骤1：提取日期范围内的所有【需求日+到货日】，生成PIVOT所需的动态列（确保有到货的日期也会显示）
SELECT
@pivot_dates = @pivot_dates + QUOTENAME(CONVERT(NVARCHAR(10), Date, 23)) + ','
FROM (
-- 合并需求日期和到货日期，去重后作为表头
SELECT DISTINCT RequireDate AS Date
FROM MPHdScribeSub
WHERE RequireDate BETWEEN @start_date AND @end_date
AND (@part_number IS NULL OR PartNum = @part_number) -- 料号过滤
UNION -- 用UNION去重，避免重复日期
SELECT DISTINCT e.BuildDate AS Date
FROM MINdOutQnty d
INNER JOIN MINdOutMain e ON d.PaperNum = e.PaperNum -- 关联入库主表获取BuildDate
LEFT JOIN MINdMatInfo t2 ON d.PartNum = t2.Partnum -- 物料分类过滤
WHERE e.BuildDate BETWEEN @start_date AND @end_date
AND (@part_number IS NULL OR d.PartNum = @part_number) -- 料号过滤
) AS AllDates
WHERE Date IS NOT NULL -- 排除空日期
ORDER BY Date; -- 按日期排序
-- 移除末尾多余的逗号（无数据时退出）
IF LEN(@pivot_dates) > 0
SET @pivot_dates = LEFT(@pivot_dates, LEN(@pivot_dates) - 1);
ELSE
BEGIN
PRINT '指定日期范围及料号条件下无需求或到货数据';
RETURN;
END
-- 步骤2：准备基础数据（拆分需求和到货，分别绑定到对应日期）
IF OBJECT_ID('tempdb..#table002') IS NOT NULL
DROP TABLE #table002;
CREATE TABLE #table002 (
label NVARCHAR(10), -- 料号分类
number NVARCHAR(50), -- 完整料号
xDate DATE, -- 日期（需求日或到货日）
DemandQTY DECIMAL(18,2), -- 需求数量（仅需求日有值）
PresentQty DECIMAL(18,2) -- 到货数量（仅到货日有值）
);
-- 插入数据：分两部分（需求记录 + 到货记录），用UNION ALL合并
INSERT INTO #table002
SELECT
label = SUBSTRING(a.PartNum, 2, 2),
number = a.PartNum,
xDate = format(a.RequireDate,'yyyy-MM-dd'), -- 需求日期
DemandQTY = CONVERT(DECIMAL(18,2), SUM(ISNULL(a.Qnty, 0))), -- 需求数量汇总
PresentQty = 0 -- 此行为需求记录，到货数为0
FROM MPHdScribeSub a
LEFT JOIN MINdMatInfo t2 ON a.PartNum = t2.Partnum -- 物料分类过滤
WHERE
a.RequireDate BETWEEN @start_date AND @end_date
AND (@part_number IS NULL OR a.PartNum = @part_number) -- 料号过滤
AND t2.matclass IN ('A','A1','B')
GROUP BY SUBSTRING(a.PartNum, 2, 2), a.PartNum, format(a.RequireDate,'yyyy-MM-dd')
UNION ALL -- 合并需求记录和到货记录
SELECT
label = SUBSTRING(d.PartNum, 2, 2),
number = d.PartNum,
xDate = format(e.BuildDate,'yyyy-MM-dd'), -- 到货日期（绑定到入库单的BuildDate）
DemandQTY = 0, -- 此行为到货记录，需求数为0
PresentQty = CONVERT(DECIMAL(18,2), SUM(ISNULL(d.UOMQnty, 0))) -- 到货数量汇总
FROM MINdOutQnty d
INNER JOIN MINdOutMain e ON d.PaperNum = e.PaperNum -- 关联入库主表获取BuildDate
LEFT JOIN MINdMatInfo t2 ON d.PartNum = t2.Partnum -- 物料分类过滤
WHERE
e.BuildDate BETWEEN @start_date AND @end_date -- 按到货日期过滤
AND (@part_number IS NULL OR d.PartNum = @part_number) -- 料号过滤
AND t2.matclass IN ('A','A1','B')
GROUP BY SUBSTRING(d.PartNum, 2, 2), d.PartNum, format(e.BuildDate,'yyyy-MM-dd');
-- 因为UNION ALL可能导致同一料号在同一日期有两条记录（一条需求、一条到货），需要合并
WITH MergedData AS (
SELECT
label,
number,
xDate,
SUM(DemandQTY) AS TotalDemand, -- 同一日期的需求总和
SUM(PresentQty) AS TotalPresent -- 同一日期的到货总和
FROM #table002
GROUP BY label, number, xDate
);
-- 动态生成PIVOT查询，按日期列展示
DECLARE @dynamic_sql NVARCHAR(MAX) ='';
SET @dynamic_sql = N'
SELECT
label, -- 料号分类
number -- 完整料号
, ' + @pivot_dates + N' -- 动态日期列（包含需求日和到货日）
FROM (
SELECT
label,
number,
CONVERT(NVARCHAR(10), xDate, 23) AS date_str,
CONCAT(N''需求:'', TotalDemand, N''到货:'', TotalPresent) AS qty_info
FROM MergedData
) AS Src
PIVOT (
MAX(qty_info) -- 聚合显示单元格内容
FOR date_str IN (' + @pivot_dates + N') -- 按所有日期列PIVOT
) AS Pvt
ORDER BY label, number; -- 按分类和料号排序
';
-- 执行动态SQL
EXEC sp_executesql @dynamic_sql;
-- 清理临时表
IF OBJECT_ID('tempdb..#table002') IS NOT NULL
DROP TABLE #table002;
END
GO
本文详细介绍了如何在数据库中查询非分片表和分片表的partnum。对于非分片表，可以通过查询systables或systabnames表来获取；而对于分片表，则需要结合sys tables和sysfragments表进行查询。此外，还提供了命令行查询partnum的方法。
扫一扫
544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
