risk_control_platform/sql/risk_dept_store.sql

-- ============================================================
-- 把部门表的"终端叶子结点"标记为门店(dept_category='门店')
-- 逻辑:parent_id 不在 dept_id 集合里的 = 叶子节点
--       (即没有任何部门以这个 id 为 parent_id)
-- 同时支持幂等(已经设为门店的不再改)
-- ============================================================

USE risk_control_platform;

UPDATE sys_dept
SET dept_category = '门店'
WHERE del_flag = '0'
  AND status = '0'
  AND dept_id NOT IN (
      SELECT t.parent_id FROM (SELECT DISTINCT parent_id FROM sys_dept
                                WHERE parent_id IS NOT NULL AND parent_id <> 0 AND del_flag = '0') t
  )
  AND parent_id <> 0  -- 排除顶级虚拟节点
  AND (dept_category IS NULL OR dept_category = '' OR dept_category <> '门店');

-- 验证:看叶子节点被改了几个 + 抽样看几个
SELECT '--- 改后统计 ---' AS info;
SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN dept_category = '门店' THEN 1 ELSE 0 END) AS store_count,
    SUM(CASE WHEN dept_category IS NULL OR dept_category = '' THEN 1 ELSE 0 END) AS unset_count
FROM sys_dept
WHERE del_flag='0' AND status='0' AND parent_id <> 0;

SELECT '--- 抽样 5 个门店 ---' AS info;
SELECT dept_id, dept_name, parent_id, dept_category
FROM sys_dept
WHERE del_flag='0' AND status='0' AND dept_category='门店'
ORDER BY dept_id LIMIT 5;