sql案例一

需求1

汇总bs_quotation_lease_term_detail表每个报价单中每一组单元租期去重后的总天数，例如：
第一组： 单元A 租期：2025-03-10 ~ 2025-08-31
第二组： 单元B 租期：2025-03-18 ~ 2025-09-30
优化前方案：

select quotation_no,count(DISTINCT b.day) from bs_quotation_lease_term_detail a 
join ibp_day b  
where a.is_del=0 and b.day >= a.renting_date and b.day <= a.lease_end_date  group by a.quotation_no;

优化后方案：

select a.quotation_no,total_days from bs_quotation_base   a,
(SELECT 
    quotation_no,
    SUM(DATEDIFF(merged_end, merged_start) + 1) AS total_days
FROM (
    SELECT 
        quotation_no,
        MIN(renting_date) AS merged_start,
        MAX(lease_end_date) AS merged_end
    FROM (
        SELECT 
            *,
            -- 关键：动态判断是否为新合同或新组
            @group := CASE 
                WHEN @prev_contract != quotation_no THEN 1  -- 新合同时重置组号
                WHEN renting_date > @prev_end THEN @group + 1
                ELSE @group 
            END AS group_id,
            -- 更新当前合同的结束日期
            @prev_end := CASE 
                WHEN @prev_contract != quotation_no THEN lease_end_date  -- 新合同时初始化结束日期
                WHEN renting_date > @prev_end THEN lease_end_date
                ELSE GREATEST(@prev_end, lease_end_date)
            END,
            -- 记录当前合同ID
            @prev_contract := quotation_no
        FROM bs_quotation_lease_term_detail 
        CROSS JOIN (SELECT @group := 0, @prev_end := NULL, @prev_contract := NULL) vars
        where is_del=0 and renting_date is not null and lease_end_date is not null 
                                -- and quotation_no = 'BJH6-20201130-102'
        ORDER BY quotation_no, renting_date  -- 按合同分组后按日期排序
    ) AS tmp
    GROUP BY quotation_no, group_id  -- 按合同和组号双重分组
) AS merged_periods
GROUP BY quotation_no having 1=1) b where a.quotation_no=b.quotation_no and a.is_del=0;  

需求2

报价单合同租期天数

select a.quotation_no,total_days from bs_quotation_base   a
left join
(SELECT 
    quotation_no,
    SUM(DATEDIFF(merged_end, merged_start) + 1) AS total_days
FROM (
    SELECT 
        quotation_no,
        MIN(renting_date) AS merged_start,
        MAX(lease_end_date) AS merged_end
    FROM (
        SELECT 
            *,
            -- 关键：动态判断是否为新合同或新组
            @group := CASE 
                WHEN @prev_contract != quotation_no THEN 1  -- 新合同时重置组号
                WHEN renting_date > @prev_end THEN @group + 1
                ELSE @group 
            END AS group_id,
            -- 更新当前合同的结束日期
            @prev_end := CASE 
                WHEN @prev_contract != quotation_no THEN lease_end_date  -- 新合同时初始化结束日期
                WHEN renting_date > @prev_end THEN lease_end_date
                ELSE GREATEST(@prev_end, lease_end_date)
            END,
            -- 记录当前合同ID
            @prev_contract := quotation_no
        FROM bs_quotation_lease_term_detail 
        CROSS JOIN (SELECT @group := 0, @prev_end := NULL, @prev_contract := NULL) vars
        where is_del=0 and renting_date is not null and lease_end_date is not null 
				-- and quotation_no = 'BJH6-20201130-102'
        ORDER BY quotation_no, renting_date  -- 按合同分组后按日期排序
    ) AS tmp
    GROUP BY quotation_no, group_id  -- 按合同和组号双重分组
) AS merged_periods
GROUP BY quotation_no having 1=1) b on  a.quotation_no=b.quotation_no where a.is_del=0;