使用SQL Cursor按月统计租赁协议（法人翻倍）分组错误求助

原代码核心问题 #

原查询的错误在于分组逻辑和统计方式：

1. 外层同时按Z1.month和Z1.count分组，导致同一个月份下，法人租户（权重2）和个人租户（权重1）的记录被拆分成两组统计，无法得到单月总计数。
2. COUNT(*) * z1.count的计算逻辑错误——这会把同一权重组的协议数量乘以权重，而非对每个协议的权重直接求和。

修正后的代码 #

DECLARE @month VARCHAR(10) 
DECLARE @count INT
DECLARE cursorTest CURSOR FOR 
SELECT Z1.month, SUM(Z1.count) AS count
FROM (
    SELECT 
        MONTH([rental_start_date]) AS month, 
        CASE 
            WHEN t.tenant_code IN (SELECT [tenant_code] FROM [dbo].[Legal_entities]) THEN 2
            WHEN t.tenant_code IN (SELECT [tenant_code] FROM [dbo].[Individuals]) THEN 1
        END AS count
    FROM [dbo].[lease_agreements] l 
    INNER JOIN [dbo].[Tenants] t ON l.tenant_code = t.tenant_code
    INNER JOIN [dbo].[Real_estate] r ON l.number_of_object = r.number_of_object
    WHERE
        -- 修正条件优先级，确保面积判断逻辑正确
        (r.total_area < 30 OR r.total_area BETWEEN 30 AND 50)
        AND DATEDIFF(MONTH, l.rental_start_date, l.rental_end_date) > 1
        AND DATEDIFF(MONTH, l.rental_start_date, l.rental_end_date) % 3 = 0
) AS Z1
GROUP BY Z1.month  -- 仅按月份分组，汇总所有协议的权重
OPEN cursorTest
FETCH NEXT FROM cursorTest INTO @month, @count
WHILE @@FETCH_STATUS = 0 
BEGIN 
    PRINT 'Month: ' + @month + ', Count: ' + CAST(@count AS VARCHAR(10))
    FETCH NEXT FROM cursorTest INTO @month, @count
END 
CLOSE cursorTest 
DEALLOCATE cursorTest

关键修正说明 #

• 分组逻辑：外层仅按Z1.month分组，确保同一个月内的所有协议权重被统一汇总。
• 统计方式：用SUM(Z1.count)直接对每个协议的权重（1或2）求和，得到单月的正确总计数。
• 条件优化：给面积判断添加括号，修正逻辑优先级，避免OR条件与后续AND条件产生逻辑冲突。

内容的提问来源于stack exchange，提问作者OverCome231