| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879 |
- -- ============================================================
- -- 双身份诊断(只读,零风险):family_members 重复行 + 外键引用分布
- -- 运行位置:prod 只读从库 / 备份库(严禁在主库盲改,本文件不含任何写操作)
- -- 日期:2026-10-25
- -- 关联:docs/superpowers/plans/2026-10-25-task-complete-500-bug.md (T24-C)
- -- docs/superpowers/plans/2026-10-25-unified-member-selector.md (T25)
- -- ============================================================
- -- 1) 同一 (user_id, family_id) 的多余成员行(核心重复)
- SELECT user_id, family_id, COUNT(*) AS dup_cnt, GROUP_CONCAT(id ORDER BY id) AS member_ids
- FROM family_members
- GROUP BY user_id, family_id
- HAVING COUNT(*) > 1
- ORDER BY dup_cnt DESC, family_id;
- -- 2) 聚焦"家长本人"型:user_id 指向有 openid 的可登录用户,且存在多条
- SELECT fm.id, fm.family_id, fm.user_id, fm.nickname, fm.status, fm.is_admin,
- fm.total_points, fm.streak_days, fm.created_at,
- (u.openid IS NOT NULL) AS has_openid
- FROM family_members fm
- JOIN users u ON u.id = fm.user_id AND u.openid IS NOT NULL
- WHERE (fm.user_id, fm.family_id) IN (
- SELECT user_id, family_id FROM family_members
- GROUP BY user_id, family_id HAVING COUNT(*) > 1
- )
- ORDER BY fm.user_id, fm.family_id, fm.id;
- -- 3a) 重复行在 tasks.family_member_id 的引用分布
- SELECT fm.user_id, fm.family_id, fm.id AS member_id,
- (SELECT COUNT(*) FROM tasks t WHERE t.family_member_id = fm.id) AS task_cnt
- FROM family_members fm
- WHERE (fm.user_id, fm.family_id) IN (
- SELECT user_id, family_id FROM family_members
- GROUP BY user_id, family_id HAVING COUNT(*) > 1
- )
- ORDER BY fm.user_id, fm.family_id, fm.id;
- -- 3b) 重复行在 task_executions.member_id 的引用分布
- SELECT fm.id AS member_id,
- (SELECT COUNT(*) FROM task_executions te WHERE te.member_id = fm.id) AS exec_cnt
- FROM family_members fm
- WHERE (fm.user_id, fm.family_id) IN (
- SELECT user_id, family_id FROM family_members
- GROUP BY user_id, family_id HAVING COUNT(*) > 1
- )
- ORDER BY fm.user_id, fm.family_id, fm.id;
- -- 3c) 重复行在 points_log.child_id 的引用分布
- SELECT fm.id AS member_id,
- (SELECT COUNT(*) FROM points_log pl WHERE pl.child_id = fm.id) AS points_cnt
- FROM family_members fm
- WHERE (fm.user_id, fm.family_id) IN (
- SELECT user_id, family_id FROM family_members
- GROUP BY user_id, family_id HAVING COUNT(*) > 1
- )
- ORDER BY fm.user_id, fm.family_id, fm.id;
- -- 4) 重复行引用总量(决定保留哪条:引用最多者优先保留)
- SELECT fm.user_id, fm.family_id, fm.id AS member_id,
- (SELECT COUNT(*) FROM tasks t WHERE t.family_member_id = fm.id)
- + (SELECT COUNT(*) FROM task_executions te WHERE te.member_id = fm.id)
- + (SELECT COUNT(*) FROM points_log pl WHERE pl.child_id = fm.id) AS ref_total
- FROM family_members fm
- WHERE (fm.user_id, fm.family_id) IN (
- SELECT user_id, family_id FROM family_members
- GROUP BY user_id, family_id HAVING COUNT(*) > 1
- )
- ORDER BY fm.user_id, fm.family_id, ref_total DESC;
- -- 5) 受影响家庭 / 用户总数(汇报用)
- SELECT COUNT(DISTINCT family_id) AS affected_families,
- COUNT(DISTINCT user_id) AS affected_users,
- SUM(dup_cnt) - COUNT(*) AS redundant_rows
- FROM (
- SELECT user_id, family_id, COUNT(*) AS dup_cnt
- FROM family_members
- GROUP BY user_id, family_id
- HAVING COUNT(*) > 1
- ) x;
|