如何在SQL中按特定条件筛选去重，保留分组后的单行数据？

Hey there! I see the issue you're facing—your DISTINCT query is returning multiple rows because even though all columns except Zone are identical, DISTINCT considers all selected columns when determining uniqueness. Let's fix this with a couple of reliable approaches:

Approach 1: Use GROUP BY with Aggregation #

Group all the columns that should be unique, then use an aggregate function like MIN() or MAX() to pick one Zone value from the duplicates. This is simple and works for most cases:

SELECT 
  E.RM_Name, 
  E.RM_Mobile,
  E.ZSM_NAme,
  E.ZSM_Mobile,
  E.SM_Name, 
  E.SM_Mobile,
  MIN(E.Zone) AS Zone -- Replace MIN with MAX if you want the largest Zone value instead
FROM tbl_employee E 
WHERE DAY(RM_DOB) = 25 AND MONTH(RM_DOB) = 6
GROUP BY 
  E.RM_Name, 
  E.RM_Mobile,
  E.ZSM_NAme,
  E.ZSM_Mobile,
  E.SM_Name, 
  E.SM_Mobile;

How it works: #

• The GROUP BY clause combines all rows where the specified columns (everything except Zone) are identical.
• MIN(E.Zone) grabs the alphabetically smallest Zone value from the grouped rows—use MAX() if you prefer the largest instead.

Approach 2: Use Window Functions for Precise Control #

If you need to pick a specific row (not just min/max of Zone), use ROW_NUMBER() to assign a unique number to each duplicate group, then filter for the first row. This lets you define exactly which row to keep:

WITH RankedEmployees AS (
  SELECT 
    E.RM_Name, 
    E.RM_Mobile,
    E.ZSM_NAme,
    E.ZSM_Mobile,
    E.SM_Name, 
    E.SM_Mobile,
    E.Zone,
    -- Assign row numbers within each group of identical non-Zone columns
    ROW_NUMBER() OVER (
      PARTITION BY E.RM_Name, E.RM_Mobile, E.ZSM_NAme, E.ZSM_Mobile, E.SM_Name, E.SM_Mobile
      ORDER BY E.Zone -- Adjust this to sort by another column (like RM_DOB) if needed
    ) AS rn
  FROM tbl_employee E 
  WHERE DAY(RM_DOB) = 25 AND MONTH(RM_DOB) = 6
)
SELECT 
  RM_Name, 
  RM_Mobile,
  ZSM_NAme,
  ZSM_Mobile,
  SM_Name, 
  SM_Mobile,
  Zone
FROM RankedEmployees
WHERE rn = 1; -- Only keep the first row from each group

How it works: #

• PARTITION BY splits the data into groups where all non-Zone columns match.
• ROW_NUMBER() numbers each row in the group based on the ORDER BY clause (here, sorted by Zone).
• We filter for rn = 1 to get just the first row from each group.

Why Your Original Query Didn't Work #

Your previous query selects all rows where RM_Name appears multiple times, but it doesn't restrict the result to one row per group. It still returns every matching row, which is why you saw duplicates.

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