antissoft / lvqianmeiye_ERP

-- ============================================
-- 批量更新品项明细表和业绩表的科技部归类字段
-- ============================================
-- 说明：此脚本用于批量更新以下表的 F_KjbCategory 字段
-- 1. lq_kd_pxmx（开单品项明细表）
-- 2. lq_kd_jksyj（开单健康师业绩表）
-- 3. lq_kd_kjbsyj（开单科技部老师业绩表）
-- 4. lq_hytk_mx（退卡品项明细表）
-- 5. lq_hytk_jksyj（退卡健康师业绩表）
-- 6. lq_hytk_kjbsyj（退卡科技部老师业绩表）
-- 
-- 数据来源：从关联的项目资料表（lq_xmzl）的 F_BeautyType 字段获取科技部归类
-- 
-- 关联关系：
-- - lq_kd_pxmx.px = lq_xmzl.F_Id
-- - lq_kd_jksyj.F_ItemId 或通过 lq_kd_pxmx 关联 = lq_xmzl.F_Id
-- - lq_kd_kjbsyj.F_ItemId 或通过 lq_kd_pxmx 关联 = lq_xmzl.F_Id
-- - lq_hytk_mx.px = lq_xmzl.F_Id
-- - lq_hytk_jksyj.F_ItemId 或通过 lq_hytk_mx 关联 = lq_xmzl.F_Id
-- - lq_hytk_kjbsyj.F_ItemId 或通过 lq_hytk_mx 关联 = lq_xmzl.F_Id
-- 
-- 更新逻辑：
-- - 更新所有记录（不判断是否有效）
-- - 只更新关联的项目资料存在且F_BeautyType字段有值的记录
-- - 从 lq_xmzl.F_BeautyType 字段获取科技部归类

-- ============================================
-- 1. 更新开单品项明细表的科技部归类字段
-- ============================================
UPDATE lq_kd_pxmx pxmx
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET pxmx.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != '';

-- ============================================
-- 2. 更新开单健康师业绩表的科技部归类字段
-- ============================================
-- 方式1：通过品项ID直接关联
UPDATE lq_kd_jksyj jksyj
INNER JOIN lq_xmzl xmzl ON jksyj.F_ItemId = xmzl.F_Id
SET jksyj.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != ''
  AND jksyj.F_ItemId IS NOT NULL
  AND jksyj.F_ItemId != '';

-- 方式2：通过开单品项明细表关联（当F_ItemId为空时）
UPDATE lq_kd_jksyj jksyj
INNER JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET jksyj.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != ''
  AND (jksyj.F_ItemId IS NULL OR jksyj.F_ItemId = '')
  AND jksyj.F_kdpxid IS NOT NULL
  AND jksyj.F_kdpxid != '';

-- ============================================
-- 3. 更新开单科技部老师业绩表的科技部归类字段
-- ============================================
-- 方式1：通过品项ID直接关联
UPDATE lq_kd_kjbsyj kjbsyj
INNER JOIN lq_xmzl xmzl ON kjbsyj.F_ItemId = xmzl.F_Id
SET kjbsyj.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != ''
  AND kjbsyj.F_ItemId IS NOT NULL
  AND kjbsyj.F_ItemId != '';

-- 方式2：通过开单品项明细表关联（当F_ItemId为空时）
UPDATE lq_kd_kjbsyj kjbsyj
INNER JOIN lq_kd_pxmx pxmx ON kjbsyj.F_kdpxid = pxmx.F_Id
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET kjbsyj.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != ''
  AND (kjbsyj.F_ItemId IS NULL OR kjbsyj.F_ItemId = '')
  AND kjbsyj.F_kdpxid IS NOT NULL
  AND kjbsyj.F_kdpxid != '';

-- ============================================
-- 4. 更新退卡品项明细表的科技部归类字段
-- ============================================
UPDATE lq_hytk_mx mx
INNER JOIN lq_xmzl xmzl ON mx.px = xmzl.F_Id
SET mx.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != '';

-- ============================================
-- 5. 更新退卡健康师业绩表的科技部归类字段
-- ============================================
-- 方式1：通过品项ID直接关联
UPDATE lq_hytk_jksyj jksyj
INNER JOIN lq_xmzl xmzl ON jksyj.F_ItemId = xmzl.F_Id
SET jksyj.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != ''
  AND jksyj.F_ItemId IS NOT NULL
  AND jksyj.F_ItemId != '';

-- 方式2：通过退卡品项明细表关联（当F_ItemId为空时）
UPDATE lq_hytk_jksyj jksyj
INNER JOIN lq_hytk_mx mx ON jksyj.F_tkpxid = mx.F_Id
INNER JOIN lq_xmzl xmzl ON mx.px = xmzl.F_Id
SET jksyj.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != ''
  AND (jksyj.F_ItemId IS NULL OR jksyj.F_ItemId = '')
  AND jksyj.F_tkpxid IS NOT NULL
  AND jksyj.F_tkpxid != '';

-- ============================================
-- 6. 更新退卡科技部老师业绩表的科技部归类字段
-- ============================================
-- 方式1：通过品项ID直接关联
UPDATE lq_hytk_kjbsyj kjbsyj
INNER JOIN lq_xmzl xmzl ON kjbsyj.F_ItemId = xmzl.F_Id
SET kjbsyj.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != ''
  AND kjbsyj.F_ItemId IS NOT NULL
  AND kjbsyj.F_ItemId != '';

-- 方式2：通过退卡品项明细表关联（当F_ItemId为空时）
UPDATE lq_hytk_kjbsyj kjbsyj
INNER JOIN lq_hytk_mx mx ON kjbsyj.F_tkpxid = mx.F_Id
INNER JOIN lq_xmzl xmzl ON mx.px = xmzl.F_Id
SET kjbsyj.F_KjbCategory = xmzl.F_BeautyType
WHERE xmzl.F_BeautyType IS NOT NULL
  AND xmzl.F_BeautyType != ''
  AND (kjbsyj.F_ItemId IS NULL OR kjbsyj.F_ItemId = '')
  AND kjbsyj.F_tkpxid IS NOT NULL
  AND kjbsyj.F_tkpxid != '';

-- ============================================
-- 7. 验证更新结果
-- ============================================
-- 查看各表更新后的统计信息
-- 
-- lq_kd_pxmx 更新后的统计信息
-- SELECT 
--     '开单品项明细' AS 表名,
--     F_KjbCategory AS 科技部归类,
--     COUNT(*) AS 记录数
-- FROM lq_kd_pxmx
-- WHERE F_KjbCategory IS NOT NULL
-- GROUP BY F_KjbCategory
-- ORDER BY 记录数 DESC;

-- lq_kd_jksyj 更新后的统计信息
-- SELECT 
--     '开单健康师业绩' AS 表名,
--     F_KjbCategory AS 科技部归类,
--     COUNT(*) AS 记录数
-- FROM lq_kd_jksyj
-- WHERE F_KjbCategory IS NOT NULL
-- GROUP BY F_KjbCategory
-- ORDER BY 记录数 DESC;

-- lq_kd_kjbsyj 更新后的统计信息
-- SELECT 
--     '开单科技部老师业绩' AS 表名,
--     F_KjbCategory AS 科技部归类,
--     COUNT(*) AS 记录数
-- FROM lq_kd_kjbsyj
-- WHERE F_KjbCategory IS NOT NULL
-- GROUP BY F_KjbCategory
-- ORDER BY 记录数 DESC;

-- lq_hytk_mx 更新后的统计信息
-- SELECT 
--     '退卡品项明细' AS 表名,
--     F_KjbCategory AS 科技部归类,
--     COUNT(*) AS 记录数
-- FROM lq_hytk_mx
-- WHERE F_KjbCategory IS NOT NULL
-- GROUP BY F_KjbCategory
-- ORDER BY 记录数 DESC;

-- lq_hytk_jksyj 更新后的统计信息
-- SELECT 
--     '退卡健康师业绩' AS 表名,
--     F_KjbCategory AS 科技部归类,
--     COUNT(*) AS 记录数
-- FROM lq_hytk_jksyj
-- WHERE F_KjbCategory IS NOT NULL
-- GROUP BY F_KjbCategory
-- ORDER BY 记录数 DESC;

-- lq_hytk_kjbsyj 更新后的统计信息
-- SELECT 
--     '退卡科技部老师业绩' AS 表名,
--     F_KjbCategory AS 科技部归类,
--     COUNT(*) AS 记录数
-- FROM lq_hytk_kjbsyj
-- WHERE F_KjbCategory IS NOT NULL
-- GROUP BY F_KjbCategory
-- ORDER BY 记录数 DESC;

-- ============================================
-- 8. 查看未更新的记录数（关联的项目资料不存在或fl4为空）
-- ============================================
-- 
-- lq_kd_pxmx 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_kd_pxmx pxmx
-- LEFT JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
-- WHERE xmzl.F_Id IS NULL 
--    OR xmzl.F_BeautyType IS NULL 
--    OR xmzl.F_BeautyType = '';

-- lq_kd_jksyj 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_kd_jksyj jksyj
-- LEFT JOIN lq_xmzl xmzl ON jksyj.F_ItemId = xmzl.F_Id
-- LEFT JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id
-- LEFT JOIN lq_xmzl xmzl2 ON pxmx.px = xmzl2.F_Id
-- WHERE (xmzl.F_Id IS NULL OR xmzl.F_BeautyType IS NULL OR xmzl.F_BeautyType = '')
--   AND (xmzl2.F_Id IS NULL OR xmzl2.F_BeautyType IS NULL OR xmzl2.F_BeautyType = '');

-- lq_kd_kjbsyj 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_kd_kjbsyj kjbsyj
-- LEFT JOIN lq_xmzl xmzl ON kjbsyj.F_ItemId = xmzl.F_Id
-- LEFT JOIN lq_kd_pxmx pxmx ON kjbsyj.F_kdpxid = pxmx.F_Id
-- LEFT JOIN lq_xmzl xmzl2 ON pxmx.px = xmzl2.F_Id
-- WHERE (xmzl.F_Id IS NULL OR xmzl.F_BeautyType IS NULL OR xmzl.F_BeautyType = '')
--   AND (xmzl2.F_Id IS NULL OR xmzl2.F_BeautyType IS NULL OR xmzl2.F_BeautyType = '');

-- lq_hytk_mx 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_hytk_mx mx
-- LEFT JOIN lq_xmzl xmzl ON mx.px = xmzl.F_Id
-- WHERE xmzl.F_Id IS NULL 
--    OR xmzl.F_BeautyType IS NULL 
--    OR xmzl.F_BeautyType = '';

-- lq_hytk_jksyj 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_hytk_jksyj jksyj
-- LEFT JOIN lq_xmzl xmzl ON jksyj.F_ItemId = xmzl.F_Id
-- LEFT JOIN lq_hytk_mx mx ON jksyj.F_tkpxid = mx.F_Id
-- LEFT JOIN lq_xmzl xmzl2 ON mx.px = xmzl2.F_Id
-- WHERE (xmzl.F_Id IS NULL OR xmzl.F_BeautyType IS NULL OR xmzl.F_BeautyType = '')
--   AND (xmzl2.F_Id IS NULL OR xmzl2.F_BeautyType IS NULL OR xmzl2.F_BeautyType = '');

-- lq_hytk_kjbsyj 未更新记录数
-- SELECT COUNT(*) AS 未更新记录数
-- FROM lq_hytk_kjbsyj kjbsyj
-- LEFT JOIN lq_xmzl xmzl ON kjbsyj.F_ItemId = xmzl.F_Id
-- LEFT JOIN lq_hytk_mx mx ON kjbsyj.F_tkpxid = mx.F_Id
-- LEFT JOIN lq_xmzl xmzl2 ON mx.px = xmzl2.F_Id
-- WHERE (xmzl.F_Id IS NULL OR xmzl.F_BeautyType IS NULL OR xmzl.F_BeautyType = '')
--   AND (xmzl2.F_Id IS NULL OR xmzl2.F_BeautyType IS NULL OR xmzl2.F_BeautyType = '');