2026-10-25-dual-identity-diagnostic.sql 3.3 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879
  1. -- ============================================================
  2. -- 双身份诊断(只读,零风险):family_members 重复行 + 外键引用分布
  3. -- 运行位置:prod 只读从库 / 备份库(严禁在主库盲改,本文件不含任何写操作)
  4. -- 日期:2026-10-25
  5. -- 关联:docs/superpowers/plans/2026-10-25-task-complete-500-bug.md (T24-C)
  6. -- docs/superpowers/plans/2026-10-25-unified-member-selector.md (T25)
  7. -- ============================================================
  8. -- 1) 同一 (user_id, family_id) 的多余成员行(核心重复)
  9. SELECT user_id, family_id, COUNT(*) AS dup_cnt, GROUP_CONCAT(id ORDER BY id) AS member_ids
  10. FROM family_members
  11. GROUP BY user_id, family_id
  12. HAVING COUNT(*) > 1
  13. ORDER BY dup_cnt DESC, family_id;
  14. -- 2) 聚焦"家长本人"型:user_id 指向有 openid 的可登录用户,且存在多条
  15. SELECT fm.id, fm.family_id, fm.user_id, fm.nickname, fm.status, fm.is_admin,
  16. fm.total_points, fm.streak_days, fm.created_at,
  17. (u.openid IS NOT NULL) AS has_openid
  18. FROM family_members fm
  19. JOIN users u ON u.id = fm.user_id AND u.openid IS NOT NULL
  20. WHERE (fm.user_id, fm.family_id) IN (
  21. SELECT user_id, family_id FROM family_members
  22. GROUP BY user_id, family_id HAVING COUNT(*) > 1
  23. )
  24. ORDER BY fm.user_id, fm.family_id, fm.id;
  25. -- 3a) 重复行在 tasks.family_member_id 的引用分布
  26. SELECT fm.user_id, fm.family_id, fm.id AS member_id,
  27. (SELECT COUNT(*) FROM tasks t WHERE t.family_member_id = fm.id) AS task_cnt
  28. FROM family_members fm
  29. WHERE (fm.user_id, fm.family_id) IN (
  30. SELECT user_id, family_id FROM family_members
  31. GROUP BY user_id, family_id HAVING COUNT(*) > 1
  32. )
  33. ORDER BY fm.user_id, fm.family_id, fm.id;
  34. -- 3b) 重复行在 task_executions.member_id 的引用分布
  35. SELECT fm.id AS member_id,
  36. (SELECT COUNT(*) FROM task_executions te WHERE te.member_id = fm.id) AS exec_cnt
  37. FROM family_members fm
  38. WHERE (fm.user_id, fm.family_id) IN (
  39. SELECT user_id, family_id FROM family_members
  40. GROUP BY user_id, family_id HAVING COUNT(*) > 1
  41. )
  42. ORDER BY fm.user_id, fm.family_id, fm.id;
  43. -- 3c) 重复行在 points_log.child_id 的引用分布
  44. SELECT fm.id AS member_id,
  45. (SELECT COUNT(*) FROM points_log pl WHERE pl.child_id = fm.id) AS points_cnt
  46. FROM family_members fm
  47. WHERE (fm.user_id, fm.family_id) IN (
  48. SELECT user_id, family_id FROM family_members
  49. GROUP BY user_id, family_id HAVING COUNT(*) > 1
  50. )
  51. ORDER BY fm.user_id, fm.family_id, fm.id;
  52. -- 4) 重复行引用总量(决定保留哪条:引用最多者优先保留)
  53. SELECT fm.user_id, fm.family_id, fm.id AS member_id,
  54. (SELECT COUNT(*) FROM tasks t WHERE t.family_member_id = fm.id)
  55. + (SELECT COUNT(*) FROM task_executions te WHERE te.member_id = fm.id)
  56. + (SELECT COUNT(*) FROM points_log pl WHERE pl.child_id = fm.id) AS ref_total
  57. FROM family_members fm
  58. WHERE (fm.user_id, fm.family_id) IN (
  59. SELECT user_id, family_id FROM family_members
  60. GROUP BY user_id, family_id HAVING COUNT(*) > 1
  61. )
  62. ORDER BY fm.user_id, fm.family_id, ref_total DESC;
  63. -- 5) 受影响家庭 / 用户总数(汇报用)
  64. SELECT COUNT(DISTINCT family_id) AS affected_families,
  65. COUNT(DISTINCT user_id) AS affected_users,
  66. SUM(dup_cnt) - COUNT(*) AS redundant_rows
  67. FROM (
  68. SELECT user_id, family_id, COUNT(*) AS dup_cnt
  69. FROM family_members
  70. GROUP BY user_id, family_id
  71. HAVING COUNT(*) > 1
  72. ) x;