-- ============================================
-- 查询所有会员的开单项目数和耗卡项目数
-- ============================================
-- 说明：统计每个会员在指定时间范围内的开单项目总数和耗卡项目总数

-- ============================================
-- 版本1：基础查询（不限制时间范围）
-- ============================================
SELECT 
    COALESCE(billing.F_MemberId, consume.F_MemberId) as member_id,
    COALESCE(billing.billing_project_count, 0) as billing_project_count,
    COALESCE(consume.consume_project_count, 0) as consume_project_count,
    (COALESCE(billing.billing_project_count, 0) - COALESCE(consume.consume_project_count, 0)) as remaining_project_count
FROM (
    -- 开单项目数（按会员分组）
    SELECT 
        px.F_MemberId,
        SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
    FROM lq_kd_pxmx px
    INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
    WHERE px.F_IsEffective = 1
        AND kd.F_IsEffective = 1
        AND px.F_MemberId IS NOT NULL
        AND px.F_MemberId != ''
    GROUP BY px.F_MemberId
) billing
FULL OUTER JOIN (
    -- 耗卡项目数（按会员分组）
    SELECT 
        xhpx.F_MemberId,
        SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
    FROM lq_xh_pxmx xhpx
    INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
    WHERE xhpx.F_IsEffective = 1
        AND xh.F_IsEffective = 1
        AND xhpx.F_MemberId IS NOT NULL
        AND xhpx.F_MemberId != ''
    GROUP BY xhpx.F_MemberId
) consume ON billing.F_MemberId = consume.F_MemberId
ORDER BY billing_project_count DESC;


-- ============================================
-- 版本2：带时间范围限制（推荐使用）
-- ============================================
-- 使用方法：修改下面的时间范围
SELECT 
    COALESCE(billing.F_MemberId, consume.F_MemberId) as member_id,
    COALESCE(billing.billing_project_count, 0) as billing_project_count,
    COALESCE(consume.consume_project_count, 0) as consume_project_count,
    (COALESCE(billing.billing_project_count, 0) - COALESCE(consume.consume_project_count, 0)) as remaining_project_count
FROM (
    -- 开单项目数（按会员分组）
    SELECT 
        px.F_MemberId,
        SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
    FROM lq_kd_pxmx px
    INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
    WHERE px.F_IsEffective = 1
        AND kd.F_IsEffective = 1
        AND px.F_MemberId IS NOT NULL
        AND px.F_MemberId != ''
        AND kd.kdrq >= '2025-10-01 00:00:00'  -- 开始时间（修改这里）
        AND kd.kdrq <= '2025-10-30 23:59:59'  -- 结束时间（修改这里）
    GROUP BY px.F_MemberId
) billing
FULL OUTER JOIN (
    -- 耗卡项目数（按会员分组）
    SELECT 
        xhpx.F_MemberId,
        SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
    FROM lq_xh_pxmx xhpx
    INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
    WHERE xhpx.F_IsEffective = 1
        AND xh.F_IsEffective = 1
        AND xhpx.F_MemberId IS NOT NULL
        AND xhpx.F_MemberId != ''
        AND xh.hksj >= '2025-10-01 00:00:00'  -- 开始时间（修改这里）
        AND xh.hksj <= '2025-10-30 23:59:59'  -- 结束时间（修改这里）
    GROUP BY xhpx.F_MemberId
) consume ON billing.F_MemberId = consume.F_MemberId
ORDER BY billing_project_count DESC;


-- ============================================
-- 版本3：兼容MySQL的写法（MySQL不支持FULL OUTER JOIN）
-- ============================================
SELECT 
    COALESCE(billing.F_MemberId, consume.F_MemberId) as member_id,
    COALESCE(billing.billing_project_count, 0) as billing_project_count,
    COALESCE(consume.consume_project_count, 0) as consume_project_count,
    (COALESCE(billing.billing_project_count, 0) - COALESCE(consume.consume_project_count, 0)) as remaining_project_count
FROM (
    -- 开单项目数（按会员分组）
    SELECT 
        px.F_MemberId,
        SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
    FROM lq_kd_pxmx px
    INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
    WHERE px.F_IsEffective = 1
        AND kd.F_IsEffective = 1
        AND px.F_MemberId IS NOT NULL
        AND px.F_MemberId != ''
        AND kd.kdrq >= '2025-10-01 00:00:00'  -- 开始时间（修改这里）
        AND kd.kdrq <= '2025-10-30 23:59:59'  -- 结束时间（修改这里）
    GROUP BY px.F_MemberId
) billing
LEFT JOIN (
    -- 耗卡项目数（按会员分组）
    SELECT 
        xhpx.F_MemberId,
        SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
    FROM lq_xh_pxmx xhpx
    INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
    WHERE xhpx.F_IsEffective = 1
        AND xh.F_IsEffective = 1
        AND xhpx.F_MemberId IS NOT NULL
        AND xhpx.F_MemberId != ''
        AND xh.hksj >= '2025-10-01 00:00:00'  -- 开始时间（修改这里）
        AND xh.hksj <= '2025-10-30 23:59:59'  -- 结束时间（修改这里）
    GROUP BY xhpx.F_MemberId
) consume ON billing.F_MemberId = consume.F_MemberId

UNION

SELECT 
    consume.F_MemberId as member_id,
    0 as billing_project_count,
    consume.consume_project_count,
    (0 - consume.consume_project_count) as remaining_project_count
FROM (
    -- 耗卡项目数（按会员分组）
    SELECT 
        xhpx.F_MemberId,
        SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
    FROM lq_xh_pxmx xhpx
    INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
    WHERE xhpx.F_IsEffective = 1
        AND xh.F_IsEffective = 1
        AND xhpx.F_MemberId IS NOT NULL
        AND xhpx.F_MemberId != ''
        AND xh.hksj >= '2025-10-01 00:00:00'  -- 开始时间（修改这里）
        AND xh.hksj <= '2025-10-30 23:59:59'  -- 结束时间（修改这里）
    GROUP BY xhpx.F_MemberId
) consume
LEFT JOIN (
    -- 开单项目数（按会员分组）
    SELECT 
        px.F_MemberId,
        SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count
    FROM lq_kd_pxmx px
    INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
    WHERE px.F_IsEffective = 1
        AND kd.F_IsEffective = 1
        AND px.F_MemberId IS NOT NULL
        AND px.F_MemberId != ''
        AND kd.kdrq >= '2025-10-01 00:00:00'  -- 开始时间（修改这里）
        AND kd.kdrq <= '2025-10-30 23:59:59'  -- 结束时间（修改这里）
    GROUP BY px.F_MemberId
) billing ON consume.F_MemberId = billing.F_MemberId
WHERE billing.F_MemberId IS NULL

ORDER BY billing_project_count DESC;


-- ============================================
-- 版本4：最简单版本（推荐，使用UNION ALL + GROUP BY实现FULL OUTER JOIN效果）
-- ============================================
SELECT 
    member_id,
    SUM(billing_project_count) as billing_project_count,
    SUM(consume_project_count) as consume_project_count,
    SUM(billing_project_count) - SUM(consume_project_count) as remaining_project_count
FROM (
    -- 开单项目数
    SELECT 
        px.F_MemberId as member_id,
        SUM(CAST(px.F_ProjectNumber AS DECIMAL(18,2))) as billing_project_count,
        0 as consume_project_count
    FROM lq_kd_pxmx px
    INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id
    WHERE px.F_IsEffective = 1
        AND kd.F_IsEffective = 1
        AND px.F_MemberId IS NOT NULL
        AND px.F_MemberId != ''
        AND kd.kdrq >= '2025-10-01 00:00:00'  -- 开始时间（修改这里）
        AND kd.kdrq <= '2025-10-30 23:59:59'  -- 结束时间（修改这里）
    GROUP BY px.F_MemberId
    
    UNION ALL
    
    -- 耗卡项目数
    SELECT 
        xhpx.F_MemberId as member_id,
        0 as billing_project_count,
        SUM(CAST(xhpx.F_ProjectNumber AS DECIMAL(18,2))) as consume_project_count
    FROM lq_xh_pxmx xhpx
    INNER JOIN lq_xh_hyhk xh ON xhpx.F_ConsumeInfoId = xh.F_Id
    WHERE xhpx.F_IsEffective = 1
        AND xh.F_IsEffective = 1
        AND xhpx.F_MemberId IS NOT NULL
        AND xhpx.F_MemberId != ''
        AND xh.hksj >= '2025-10-01 00:00:00'  -- 开始时间（修改这里）
        AND xh.hksj <= '2025-10-30 23:59:59'  -- 结束时间（修改这里）
    GROUP BY xhpx.F_MemberId
) all_data
GROUP BY member_id
ORDER BY billing_project_count DESC;