-- ============================================
-- 为 lq_hytk_mx 表添加会员ID字段
-- ============================================

-- 1. 添加会员ID字段
ALTER TABLE lq_hytk_mx 
ADD COLUMN F_MemberId VARCHAR(50) NULL COMMENT '会员id' AFTER F_BillingItemId;

-- 2. 创建索引以优化查询性能
CREATE INDEX idx_hytk_mx_member_id ON lq_hytk_mx(F_MemberId);

-- 3. 根据退卡信息表填充现有数据的会员ID（通过 F_RefundInfoId 关联）
UPDATE lq_hytk_mx mx
INNER JOIN lq_hytk_hytk hytk ON mx.F_RefundInfoId = hytk.F_Id
SET mx.F_MemberId = hytk.hy
WHERE mx.F_MemberId IS NULL 
  AND hytk.hy IS NOT NULL 
  AND hytk.hy != '';

-- 4. 根据开单品项明细表填充会员ID（如果退卡信息表中没有，则从开单品项明细表获取）
UPDATE lq_hytk_mx mx
INNER JOIN lq_kd_pxmx pxmx ON mx.F_BillingItemId = pxmx.F_Id
SET mx.F_MemberId = pxmx.F_MemberId
WHERE mx.F_MemberId IS NULL 
  AND pxmx.F_MemberId IS NOT NULL 
  AND pxmx.F_MemberId != '';