T-SQL去重及计数与字段值对比问题咨询

Hey Chris, let's work through your query issues together—you're already halfway there by linking the Person and Contact tables, so let's tackle the duplicates and counting/comparison needs next!

First, duplicates almost always pop up here because of the one-to-many relationship between Person and Contact: a single participant can have multiple contact records, so joining the tables repeats the Person row for every matching Contact entry. Here are two solid ways to fix this:

1. Use DISTINCT for Quick De-duplication #

If you just need unique participant details (and don't need to count their contacts yet), slap DISTINCT right after your SELECT to eliminate duplicate Person rows:

SELECT DISTINCT p.*
FROM Person p
JOIN Contact c ON p.id = c.person_id -- Replace with your actual join key!
WHERE p.type IN ('O', 'I');

Note: This works for basic de-duplication, but it's less flexible if you need to add counting logic later.

2. Use GROUP BY for De-duplication + Aggregation #

If you want to de-duplicate and count contact records per participant in one go, GROUP BY is the way to go. It groups rows by unique Person attributes and lets you run aggregate functions like COUNT:

SELECT 
  p.id,
  p.name,
  p.type,
  COUNT(c.id) AS total_contacts
FROM Person p
LEFT JOIN Contact c ON p.id = c.person_id -- Use LEFT JOIN to keep participants with no contacts
WHERE p.type IN ('O', 'I')
GROUP BY p.id, p.name, p.type; -- Group by all non-aggregated Person fields

Swap back to INNER JOIN if you only want participants who have at least one contact record.

Now for the counting and comparison piece—let's cover common scenarios you might need:

Example 1: Filter Participants by Contact Count #

Say you want to find Class 1 (type='O') participants with more than 3 contact records. Use HAVING to filter after aggregation (you can't use WHERE on aggregated values):

SELECT 
  p.id,
  p.name,
  COUNT(c.id) AS total_contacts
FROM Person p
JOIN Contact c ON p.id = c.person_id
WHERE p.type = 'O'
GROUP BY p.id, p.name
HAVING COUNT(c.id) > 3;

Example 2: Compare Counts Across Classes #

If you want to compare average contact counts between Class 1 (O) and Class 2 (I), you can use a subquery to calculate per-participant counts first, then aggregate by class:

SELECT 
  CASE p.type 
    WHEN 'O' THEN 'Class 1'
    WHEN 'I' THEN 'Class 2'
  END AS class_name,
  AVG(sub.total_contacts) AS avg_contacts_per_participant
FROM (
  SELECT 
    p.id,
    p.type,
    COUNT(c.id) AS total_contacts
  FROM Person p
  LEFT JOIN Contact c ON p.id = c.person_id
  GROUP BY p.id, p.type
) AS sub
GROUP BY class_name;

Bonus: Fix Duplicates in the Person Table Itself #

If duplicates aren't coming from the join—meaning the Person table has duplicate participant records—use a window function to identify and clean them:

-- Find duplicate Person records (adjust PARTITION BY to match your unique identifier)
SELECT *
FROM (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY p.email, p.name ORDER BY p.created_at DESC) AS row_num
  FROM Person p
) AS dup_check
WHERE row_num > 1; -- These are duplicates you can delete or merge

内容的提问来源于stack exchange，提问作者Chris Rutherford