之前我有以下查询，它为ProductionFacility='Unassigned'提供了任何给定日期的计数，并且还排除了如果任何UniqueFormId分配给其他ProductionFacility的行

SELECT 'Unassigned',0,'2023-12-04',
        COUNT(DISTINCT UniqueFormId )   ,    
        COUNT(DISTINCT UniqueFormId )       
        FROM detail
        WHERE ProductionFacility='Unassigned'and DATE(StatusDateTime)<='2023-12-04'
        and UniqueFormId not in ( select DISTINCT UniqueFormId from detail as exd where DATE(exd.StatusDateTime) <= '2023-12-04' and
        exd.ProductionStatusNo!=1 )

字符串
为了提高性能，我将not in（排除逻辑）更改为带null检查的左连接，并且我必须从1st到31st运行查询。所以我使用date_range表进行左连接，如下所示：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Dashboard_Unassigned_Insert`(
IN p_StartDate DATE,
IN p_UnassignedProductionFaciltiy nvarchar(500)
)
BEGIN
        truncate table unassigned ; 
        DROP TEMPORARY TABLE IF EXISTS date_range;
        CREATE TEMPORARY TABLE date_range AS
        SELECT DATE(p_StartDate) + INTERVAL (n-1) DAY AS StatusDate
        FROM (
          SELECT ROW_NUMBER() OVER () AS n
          FROM detail 
          LIMIT 31 
        ) AS StatusDate;
        
        
        INSERT INTO unassigned(ProductionStatus,ProductionStatusNo,StatusDate,DayCount,CumulativeCount)
         
        SELECT 
            COALESCE(result.ProductionStatus, 'Unassigned') AS ProductionStatus,
            COALESCE(result.ProductionStatusNo, 0) AS ProductionStatusNo,
            dr.StatusDate, 
            COALESCE(result.DayCount, 0) AS DayCount,
            COALESCE(result.CumulativeCount, 0) AS CumulativeCount
        FROM date_range  as dr
        LEFT JOIN  (
                SELECT 
                    'Unassigned' AS ProductionStatus,  
                    0 AS ProductionStatusNo,   
                    d.StatusDate, 
                    COUNT(DISTINCT d.UniqueFormId) as DayCount, 
                    SUM(COUNT(DISTINCT d.UniqueFormId)) OVER (ORDER BY d.StatusDate) as CumulativeCount 
                FROM 
                    detail d 
                LEFT JOIN ( 
                    SELECT DISTINCT UniqueFormId, StatusDate
                    FROM detail exd 
                    WHERE exd.ProductionStatusNo != 1 
                ) exd ON d.UniqueFormId = exd.UniqueFormId AND exd.StatusDate <= d.StatusDate
                WHERE 
                    d.ProductionFacility = 'KY_KIT' 
                    AND exd.UniqueFormId IS NULL 
                GROUP BY 
                    d.StatusDate
                ORDER BY 
                    d.StatusDate) as result  ON dr.StatusDate=result.StatusDate ;
END$$
DELIMITER ;

型
但现在我面临的问题是，当没有行在详细表让说5日没有行未分配然后它显示为零，但它应该是相同的计数，因为我在4日，第二件事6日所有这些都被分配到一些其他productionfacility然后它应该是零，因为没有更多的未分配记录。