JY1.0/sql/inventory_demand_performance_optimize_20260714.sql

-- 搴撳瓨鏌ヨ闇€姹傞噺鎬ц兘浼樺寲锛氱洿杩炲熀纭€琛ㄥ垽鏂鍗曞畬鎴愮姸鎬侊紝鍑忓皯閲嶈鍥惧睍寮€
-- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00;

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Ld_闆朵欢鍩烘湰浠禵鐗╂枡缁勪欢_20260714' AND object_id = OBJECT_ID(N'dbo.PDM_闆朵欢鏄庣粏琛╛鍩烘湰浠?))
BEGIN
    CREATE NONCLUSTERED INDEX IX_Ld_闆朵欢鍩烘湰浠禵鐗╂枡缁勪欢_20260714
    ON dbo.PDM_闆朵欢鏄庣粏琛╛鍩烘湰浠?(鐗╂枡娴佹按鍙? 缁勪欢娴佹按鍙?
    INCLUDE (闆朵欢鏁伴噺, 宸查鏁伴噺);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Ld_闆朵欢鏍囧噯澶栬喘_鐗╂枡缁勪欢_20260714' AND object_id = OBJECT_ID(N'dbo.PDM_闆朵欢鏄庣粏琛╛鏍囧噯浠跺拰澶栬喘浠?))
BEGIN
    CREATE NONCLUSTERED INDEX IX_Ld_闆朵欢鏍囧噯澶栬喘_鐗╂枡缁勪欢_20260714
    ON dbo.PDM_闆朵欢鏄庣粏琛╛鏍囧噯浠跺拰澶栬喘浠?(鐗╂枡娴佹按鍙? 缁勪欢娴佹按鍙?
    INCLUDE (闆朵欢鏁伴噺, 宸查鏁伴噺);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Ld_涓存椂棰嗘枡_鐗╂枡缁勪欢_20260714' AND object_id = OBJECT_ID(N'dbo.杞﹂棿瑁呴厤绠＄悊_瑁呴厤鎵ц_涓存椂棰嗘枡鏄庣粏'))
BEGIN
    CREATE NONCLUSTERED INDEX IX_Ld_涓存椂棰嗘枡_鐗╂枡缁勪欢_20260714
    ON dbo.杞﹂棿瑁呴厤绠＄悊_瑁呴厤鎵ц_涓存椂棰嗘枡鏄庣粏 (鐗╂枡娴佹按鍙? 缁勪欢娴佹按鍙?
    INCLUDE (瀹為鏁?;
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Ld_棰嗘枡鏄庣粏_鍩烘湰浠禵20260714' AND object_id = OBJECT_ID(N'dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?))
BEGIN
    CREATE NONCLUSTERED INDEX IX_Ld_棰嗘枡鏄庣粏_鍩烘湰浠禵20260714
    ON dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?(鍩烘湰浠舵祦姘村彿)
    INCLUDE (宸查鏁伴噺);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Ld_棰嗘枡鏄庣粏_鏍囧噯澶栬喘_20260714' AND object_id = OBJECT_ID(N'dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?))
BEGIN
    CREATE NONCLUSTERED INDEX IX_Ld_棰嗘枡鏄庣粏_鏍囧噯澶栬喘_20260714
    ON dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?(鏍囧噯浠跺拰澶栬喘浠舵祦姘村彿)
    INCLUDE (宸查鏁伴噺);
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Ld_棰嗘枡鏄庣粏_鐗╂枡缁勪欢_20260714' AND object_id = OBJECT_ID(N'dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?))
BEGIN
    CREATE NONCLUSTERED INDEX IX_Ld_棰嗘枡鏄庣粏_鐗╂枡缁勪欢_20260714
    ON dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?(鐗╂枡娴佹按鍙? 缁勪欢娴佹按鍙?
    INCLUDE (宸查鏁伴噺);
END;
GO

ALTER VIEW dbo.鐗╂枡绠＄悊_闇€姹傞噺_瑙嗗浘
AS
WITH 待发货且部件全完成订单 AS
(
    SELECT o.订单编号
    FROM dbo.合同管理_订单信息_基础表 o
    INNER JOIN dbo.PDM_项目名称 p ON o.项目流水号 = p.项目流水号
    INNER JOIN dbo.PDM_组件名称_视图 c ON p.项目流水号 = c.项目流水号
    WHERE ISNULL(o.是否提交, 0) = 5
    GROUP BY o.订单编号
    HAVING SUM(CASE WHEN ISNULL(c.产品状态, 0) = 4 OR ISNULL(c.完成数量, 0) >= ISNULL(c.数量, 0) THEN 1 ELSE 0 END) = COUNT(*)
),
组件订单 AS
(
    SELECT
        c.缁勪欢娴佹按鍙?,
        c.鏄惁鍒嗛厤,
        o.璁㈠崟绫诲瀷,
        o.鏄惁鎻愪氦,
        p.鏄惁鍚敤
    FROM dbo.PDM_缁勪欢鍚嶇О c
    INNER JOIN dbo.PDM_鏈哄簥鍚嶇О m ON c.鏈哄簥娴佹按鍙?= m.鏈哄簥娴佹按鍙?    INNER JOIN dbo.PDM_椤圭洰鍚嶇О p ON m.椤圭洰娴佹按鍙?= p.椤圭洰娴佹按鍙?    INNER JOIN dbo.鍚堝悓绠＄悊_璁㈠崟淇℃伅_鍩虹琛?o ON p.椤圭洰娴佹按鍙?= o.椤圭洰娴佹按鍙?
    WHERE NOT EXISTS (SELECT 1 FROM 待发货且部件全完成订单 x WHERE x.订单编号 = o.订单编号) -- 修改人:Ld 修改时间:2026-07-24 14:12:57; 待发货且部件全完成的订单平掉库存需求
),
闇€姹?AS
(
    SELECT b.鐗╂枡娴佹按鍙? SUM(ISNULL(b.闆朵欢鏁伴噺, 0)) AS 闇€姹傞噺
    FROM dbo.PDM_闆朵欢鏄庣粏琛╛鍩烘湰浠?b
    INNER JOIN 缁勪欢璁㈠崟 co ON b.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?    WHERE co.鏄惁鍒嗛厤 = 1
      AND co.璁㈠崟绫诲瀷 <> 3
      AND NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 鐩存帴鐢ㄥ熀纭€瀛楁鎺掗櫎宸插畬鎴愯鍗?    GROUP BY b.鐗╂枡娴佹按鍙?
    UNION ALL

    SELECT s.鐗╂枡娴佹按鍙? SUM(ISNULL(s.闆朵欢鏁伴噺, 0)) AS 闇€姹傞噺
    FROM dbo.PDM_闆朵欢鏄庣粏琛╛鏍囧噯浠跺拰澶栬喘浠?s
    INNER JOIN 缁勪欢璁㈠崟 co ON s.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?    WHERE co.鏄惁鍒嗛厤 = 1
      AND co.璁㈠崟绫诲瀷 <> 3
      AND NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 鐩存帴鐢ㄥ熀纭€瀛楁鎺掗櫎宸插畬鎴愯鍗?    GROUP BY s.鐗╂枡娴佹按鍙?
    UNION ALL

    SELECT t.鐗╂枡娴佹按鍙? SUM(ISNULL(t.瀹為鏁? 0)) AS 闇€姹傞噺
    FROM dbo.杞﹂棿瑁呴厤绠＄悊_瑁呴厤鎵ц_涓存椂棰嗘枡鏄庣粏 t
    LEFT JOIN 缁勪欢璁㈠崟 co ON t.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?    WHERE t.鐗╂枡娴佹按鍙?IS NOT NULL
      AND NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 涓存椂棰嗘枡鍚屽彛寰勬帓闄ゅ凡瀹屾垚璁㈠崟
    GROUP BY t.鐗╂枡娴佹按鍙?),
闇€姹傛眹鎬?AS
(
    SELECT 鐗╂枡娴佹按鍙? SUM(闇€姹傞噺) AS 闇€姹傞噺
    FROM 闇€姹?    GROUP BY 鐗╂枡娴佹按鍙?),
宸查 AS
(
    SELECT b.鐗╂枡娴佹按鍙? SUM(ISNULL(l.宸查鏁伴噺, 0)) AS 宸查鏁?    FROM dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?l
    INNER JOIN dbo.PDM_闆朵欢鏄庣粏琛╛鍩烘湰浠?b ON l.鍩烘湰浠舵祦姘村彿 = b.鍩烘湰浠舵祦姘村彿
    INNER JOIN 缁勪欢璁㈠崟 co ON b.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?    WHERE NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 鎵ｅ噺宸查鏁版椂鍚屾鎺掗櫎宸插畬鎴愯鍗?    GROUP BY b.鐗╂枡娴佹按鍙?
    UNION ALL

    SELECT s.鐗╂枡娴佹按鍙? SUM(ISNULL(l.宸查鏁伴噺, 0)) AS 宸查鏁?    FROM dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?l
    INNER JOIN dbo.PDM_闆朵欢鏄庣粏琛╛鏍囧噯浠跺拰澶栬喘浠?s ON l.鏍囧噯浠跺拰澶栬喘浠舵祦姘村彿 = s.鏍囧噯浠跺拰澶栬喘浠舵祦姘村彿
    INNER JOIN 缁勪欢璁㈠崟 co ON s.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?    WHERE NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 鎵ｅ噺宸查鏁版椂鍚屾鎺掗櫎宸插畬鎴愯鍗?    GROUP BY s.鐗╂枡娴佹按鍙?
    UNION ALL

    SELECT l.鐗╂枡娴佹按鍙? SUM(ISNULL(l.宸查鏁伴噺, 0)) AS 宸查鏁?    FROM dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?l
    LEFT JOIN 缁勪欢璁㈠崟 co ON l.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?    WHERE l.鐗╂枡娴佹按鍙?IS NOT NULL
      AND ISNULL(l.鐗╂枡娴佹按鍙? 0) <> 0
      AND ISNULL(l.鍩烘湰浠舵祦姘村彿, 0) = 0
      AND ISNULL(l.鏍囧噯浠跺拰澶栬喘浠舵祦姘村彿, 0) = 0
      AND NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 涓存椂浠舵墸鍑忓凡棰嗘暟鏃跺悓姝ユ帓闄ゅ凡瀹屾垚璁㈠崟
    GROUP BY l.鐗╂枡娴佹按鍙?),
宸查姹囨€?AS
(
    SELECT 鐗╂枡娴佹按鍙? SUM(宸查鏁? AS 宸查鏁?    FROM 宸查
    GROUP BY 鐗╂枡娴佹按鍙?)
SELECT
    d.鐗╂枡娴佹按鍙?
    CASE WHEN d.闇€姹傞噺 > ISNULL(l.宸查鏁? 0) THEN d.闇€姹傞噺 - ISNULL(l.宸查鏁? 0) ELSE 0 END AS 闇€姹傞噺
FROM 闇€姹傛眹鎬?d
LEFT JOIN 宸查姹囨€?l ON d.鐗╂枡娴佹按鍙?= l.鐗╂枡娴佹按鍙?
GO

ALTER PROCEDURE [dbo].[鐗╂枡绠＄悊_闇€姹傞噺_鏇存柊]
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM dbo.鐗╂枡绠＄悊_闇€姹傞噺;

    INSERT INTO dbo.鐗╂枡绠＄悊_闇€姹傞噺
    SELECT 鐗╂枡娴佹按鍙? 闇€姹傞噺
    FROM dbo.鐗╂枡绠＄悊_闇€姹傞噺_瑙嗗浘
    WHERE 闇€姹傞噺 > 0;
END;
GO

ALTER PROCEDURE [dbo].[浠撳偍绠＄悊_鎶ョ己琛ヨ揣_闇€姹傞噺鏄庣粏_鏌ヨ]
    @鐗╂枡娴佹按鍙?INT = 15448
AS
BEGIN
    SET NOCOUNT ON;
    ;WITH 待发货且部件全完成订单 AS
    (
        SELECT o.订单编号
        FROM dbo.合同管理_订单信息_基础表 o
        INNER JOIN dbo.PDM_项目名称 p ON o.项目流水号 = p.项目流水号
        INNER JOIN dbo.PDM_组件名称_视图 c ON p.项目流水号 = c.项目流水号
        WHERE ISNULL(o.是否提交, 0) = 5
        GROUP BY o.订单编号
        HAVING SUM(CASE WHEN ISNULL(c.产品状态, 0) = 4 OR ISNULL(c.完成数量, 0) >= ISNULL(c.数量, 0) THEN 1 ELSE 0 END) = COUNT(*)
    ),
    组件订单 AS
    (
        SELECT
            c.缁勪欢娴佹按鍙?,
            o.璁㈠崟缂栧彿,
            c.鏄惁鍒嗛厤,
            o.璁㈠崟绫诲瀷,
            o.鏄惁鎻愪氦,
            p.鏄惁鍚敤,
            ISNULL(k.绠€绉? '') AS 绠€绉?
        FROM dbo.PDM_缁勪欢鍚嶇О c
        INNER JOIN dbo.PDM_鏈哄簥鍚嶇О m ON c.鏈哄簥娴佹按鍙?= m.鏈哄簥娴佹按鍙?
        INNER JOIN dbo.PDM_椤圭洰鍚嶇О p ON m.椤圭洰娴佹按鍙?= p.椤圭洰娴佹按鍙?
        INNER JOIN dbo.鍚堝悓绠＄悊_璁㈠崟淇℃伅_鍩虹琛?o ON p.椤圭洰娴佹按鍙?= o.椤圭洰娴佹按鍙?
        LEFT JOIN dbo.瀹㈡埛鍏崇郴绠＄悊绯荤粺_瀹㈡埛鍚嶇О k ON p.瀹㈡埛娴佹按鍙?= k.瀹㈡埛娴佹按鍙?
        WHERE NOT EXISTS (SELECT 1 FROM 待发货且部件全完成订单 x WHERE x.订单编号 = o.订单编号) -- 修改人:Ld 修改时间:2026-07-24 14:12:57; 待发货且部件全完成的订单平掉库存需求
    ),
    缁勪欢璁㈠崟 AS
    (
        SELECT
            c.缁勪欢娴佹按鍙?
            o.璁㈠崟缂栧彿,
            c.鏄惁鍒嗛厤,
            o.璁㈠崟绫诲瀷,
            o.鏄惁鎻愪氦,
            p.鏄惁鍚敤,
            ISNULL(k.绠€绉? '') AS 绠€绉?        FROM dbo.PDM_缁勪欢鍚嶇О c
        INNER JOIN dbo.PDM_鏈哄簥鍚嶇О m ON c.鏈哄簥娴佹按鍙?= m.鏈哄簥娴佹按鍙?        INNER JOIN dbo.PDM_椤圭洰鍚嶇О p ON m.椤圭洰娴佹按鍙?= p.椤圭洰娴佹按鍙?        INNER JOIN dbo.鍚堝悓绠＄悊_璁㈠崟淇℃伅_鍩虹琛?o ON p.椤圭洰娴佹按鍙?= o.椤圭洰娴佹按鍙?        LEFT JOIN dbo.瀹㈡埛鍏崇郴绠＄悊绯荤粺_瀹㈡埛鍚嶇О k ON p.瀹㈡埛娴佹按鍙?= k.瀹㈡埛娴佹按鍙?    ),
    闇€姹傛槑缁?AS
    (
        SELECT
            co.璁㈠崟缂栧彿,
            b.缁勪欢娴佹按鍙?
            b.鐗╂枡娴佹按鍙?
            SUM(ISNULL(b.闆朵欢鏁伴噺, 0)) AS 闇€姹傞噺,
            N'鐢熶骇璁㈠崟闇€姹? AS 鏉ユ簮
        FROM dbo.PDM_闆朵欢鏄庣粏琛╛鍩烘湰浠?b
        INNER JOIN 缁勪欢璁㈠崟 co ON b.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?        WHERE co.鏄惁鍒嗛厤 = 1
          AND co.璁㈠崟绫诲瀷 <> 3
          AND NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 鏄庣粏涓嶆樉绀哄凡瀹屾垚璁㈠崟
          AND (@鐗╂枡娴佹按鍙?IS NULL OR b.鐗╂枡娴佹按鍙?= @鐗╂枡娴佹按鍙?
        GROUP BY co.璁㈠崟缂栧彿, b.缁勪欢娴佹按鍙? b.鐗╂枡娴佹按鍙?
        UNION ALL

        SELECT
            co.璁㈠崟缂栧彿,
            s.缁勪欢娴佹按鍙?
            s.鐗╂枡娴佹按鍙?
            SUM(ISNULL(s.闆朵欢鏁伴噺, 0)) AS 闇€姹傞噺,
            N'鐢熶骇璁㈠崟闇€姹? AS 鏉ユ簮
        FROM dbo.PDM_闆朵欢鏄庣粏琛╛鏍囧噯浠跺拰澶栬喘浠?s
        INNER JOIN 缁勪欢璁㈠崟 co ON s.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?        WHERE co.鏄惁鍒嗛厤 = 1
          AND co.璁㈠崟绫诲瀷 <> 3
          AND NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 鏄庣粏涓嶆樉绀哄凡瀹屾垚璁㈠崟
          AND (@鐗╂枡娴佹按鍙?IS NULL OR s.鐗╂枡娴佹按鍙?= @鐗╂枡娴佹按鍙?
        GROUP BY co.璁㈠崟缂栧彿, s.缁勪欢娴佹按鍙? s.鐗╂枡娴佹按鍙?
        UNION ALL

        SELECT
            co.璁㈠崟缂栧彿,
            t.缁勪欢娴佹按鍙?
            t.鐗╂枡娴佹按鍙?
            SUM(ISNULL(t.瀹為鏁? 0)) AS 闇€姹傞噺,
            N'涓存椂棰嗘枡闇€姹? AS 鏉ユ簮
        FROM dbo.杞﹂棿瑁呴厤绠＄悊_瑁呴厤鎵ц_涓存椂棰嗘枡鏄庣粏 t
        LEFT JOIN 缁勪欢璁㈠崟 co ON t.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?        WHERE t.鐗╂枡娴佹按鍙?IS NOT NULL
          AND NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 涓存椂棰嗘枡鏄庣粏涓嶆樉绀哄凡瀹屾垚璁㈠崟
          AND (@鐗╂枡娴佹按鍙?IS NULL OR t.鐗╂枡娴佹按鍙?= @鐗╂枡娴佹按鍙?
        GROUP BY co.璁㈠崟缂栧彿, t.缁勪欢娴佹按鍙? t.鐗╂枡娴佹按鍙?    ),
    宸查鏄庣粏 AS
    (
        SELECT
            co.璁㈠崟缂栧彿,
            b.缁勪欢娴佹按鍙?
            b.鐗╂枡娴佹按鍙?
            SUM(ISNULL(l.宸查鏁伴噺, 0)) AS 宸查鏁?        FROM dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?l
        INNER JOIN dbo.PDM_闆朵欢鏄庣粏琛╛鍩烘湰浠?b ON l.鍩烘湰浠舵祦姘村彿 = b.鍩烘湰浠舵祦姘村彿
        INNER JOIN 缁勪欢璁㈠崟 co ON b.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?        WHERE NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 鏄庣粏鎵ｅ噺宸查鏁版椂鍚屾鎺掗櫎宸插畬鎴愯鍗?          AND (@鐗╂枡娴佹按鍙?IS NULL OR b.鐗╂枡娴佹按鍙?= @鐗╂枡娴佹按鍙?
        GROUP BY co.璁㈠崟缂栧彿, b.缁勪欢娴佹按鍙? b.鐗╂枡娴佹按鍙?
        UNION ALL

        SELECT
            co.璁㈠崟缂栧彿,
            s.缁勪欢娴佹按鍙?
            s.鐗╂枡娴佹按鍙?
            SUM(ISNULL(l.宸查鏁伴噺, 0)) AS 宸查鏁?        FROM dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?l
        INNER JOIN dbo.PDM_闆朵欢鏄庣粏琛╛鏍囧噯浠跺拰澶栬喘浠?s ON l.鏍囧噯浠跺拰澶栬喘浠舵祦姘村彿 = s.鏍囧噯浠跺拰澶栬喘浠舵祦姘村彿
        INNER JOIN 缁勪欢璁㈠崟 co ON s.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?        WHERE NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 鏄庣粏鎵ｅ噺宸查鏁版椂鍚屾鎺掗櫎宸插畬鎴愯鍗?          AND (@鐗╂枡娴佹按鍙?IS NULL OR s.鐗╂枡娴佹按鍙?= @鐗╂枡娴佹按鍙?
        GROUP BY co.璁㈠崟缂栧彿, s.缁勪欢娴佹按鍙? s.鐗╂枡娴佹按鍙?
        UNION ALL

        SELECT
            co.璁㈠崟缂栧彿,
            l.缁勪欢娴佹按鍙?
            l.鐗╂枡娴佹按鍙?
            SUM(ISNULL(l.宸查鏁伴噺, 0)) AS 宸查鏁?        FROM dbo.杞﹂棿瑁呴厤绠＄悊_棰嗘枡鍗曟槑缁?l
        LEFT JOIN 缁勪欢璁㈠崟 co ON l.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?        WHERE l.鐗╂枡娴佹按鍙?IS NOT NULL
          AND ISNULL(l.鐗╂枡娴佹按鍙? 0) <> 0
          AND ISNULL(l.鍩烘湰浠舵祦姘村彿, 0) = 0
          AND ISNULL(l.鏍囧噯浠跺拰澶栬喘浠舵祦姘村彿, 0) = 0
          AND NOT (ISNULL(co.鏄惁鎻愪氦, 0) = 6 AND ISNULL(co.鏄惁鍚敤, 0) = 1) -- 淇敼浜?Ld 淇敼鏃堕棿:2026-07-14 16:35:00; 涓存椂浠舵槑缁嗘墸鍑忓凡棰嗘暟鏃跺悓姝ユ帓闄ゅ凡瀹屾垚璁㈠崟
          AND (@鐗╂枡娴佹按鍙?IS NULL OR l.鐗╂枡娴佹按鍙?= @鐗╂枡娴佹按鍙?
        GROUP BY co.璁㈠崟缂栧彿, l.缁勪欢娴佹按鍙? l.鐗╂枡娴佹按鍙?    ),
    宸查姹囨€?AS
    (
        SELECT 璁㈠崟缂栧彿, 缁勪欢娴佹按鍙? 鐗╂枡娴佹按鍙? SUM(宸查鏁? AS 宸查鏁?        FROM 宸查鏄庣粏
        GROUP BY 璁㈠崟缂栧彿, 缁勪欢娴佹按鍙? 鐗╂枡娴佹按鍙?    ),
    鏄庣粏 AS
    (
        SELECT
            d.璁㈠崟缂栧彿,
            d.缁勪欢娴佹按鍙?
            d.鐗╂枡娴佹按鍙?
            d.闇€姹傞噺,
            ISNULL(l.宸查鏁? 0) AS 宸查鏁?
            CASE WHEN d.闇€姹傞噺 > ISNULL(l.宸查鏁? 0) THEN d.闇€姹傞噺 - ISNULL(l.宸查鏁? 0) ELSE 0 END AS 鏈€缁堥渶姹傞噺,
            d.鏉ユ簮
        FROM 闇€姹傛槑缁?d
        LEFT JOIN 宸查姹囨€?l
            ON d.鐗╂枡娴佹按鍙?= l.鐗╂枡娴佹按鍙?           AND d.缁勪欢娴佹按鍙?= l.缁勪欢娴佹按鍙?           AND ISNULL(d.璁㈠崟缂栧彿, '') = ISNULL(l.璁㈠崟缂栧彿, '')
    )
    SELECT
        d.璁㈠崟缂栧彿,
        d.缁勪欢娴佹按鍙?
        d.鐗╂枡娴佹按鍙?
        d.闇€姹傞噺,
        d.宸查鏁?
        d.鏈€缁堥渶姹傞噺,
        ISNULL(co.绠€绉? '') AS 绠€绉?
        d.鏉ユ簮
    FROM 鏄庣粏 d
    LEFT JOIN 缁勪欢璁㈠崟 co ON d.缁勪欢娴佹按鍙?= co.缁勪欢娴佹按鍙?    ORDER BY d.璁㈠崟缂栧彿, d.缁勪欢娴佹按鍙? d.鏉ユ簮;

    ;WITH 姹囨€?AS
    (
        SELECT 鐗╂枡娴佹按鍙? 闇€姹傞噺
        FROM dbo.鐗╂枡绠＄悊_闇€姹傞噺_瑙嗗浘
        WHERE (@鐗╂枡娴佹按鍙?IS NULL OR 鐗╂枡娴佹按鍙?= @鐗╂枡娴佹按鍙?
    )
    SELECT
        鐗╂枡娴佹按鍙?
        闇€姹傞噺 AS 鎬婚渶姹傞噺,
        0 AS 鎬诲凡棰嗘暟,
        闇€姹傞噺 AS 鎬绘渶缁堥渶姹傞噺
    FROM 姹囨€?    ORDER BY 鐗╂枡娴佹按鍙?
END;
GO