只读产品表SQL去重查询：按Serial去重，优先保留Label首字母A-J记录

Got it, let's tackle this problem step by step. The main issues with your original query are that you can't reference window function results directly in the WHERE clause (SQL execution order prevents that) and we need a reliable way to prioritize records with labels starting from A-J when duplicates exist.

Solution Approach #

The core idea is to assign a ranking to each record within its serial group: records meeting the A-J label condition get higher priority (lower rank number), then we pick only the top-ranked record per serial.

Working Query #

Here's a solution using a CTE (Common Table Expression) to first calculate rankings, then filter for the top record in each group:

WITH ranked_products AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY serial 
            ORDER BY 
                -- Prioritize labels starting with A-J (assign 0 to put them first)
                CASE WHEN LEFT(label, 1) BETWEEN 'A' AND 'J' THEN 0 ELSE 1 END,
                -- Tiebreaker: pick the record with the smallest id (adjust to label/other fields if needed)
                id
        ) AS rn
    FROM products
    -- Uncomment below if you only want records where type='computer'
    -- WHERE type = 'computer'
)
SELECT id, serial, label, type
FROM ranked_products
WHERE rn = 1;

Breakdown of the Query #

1. CTE (ranked_products):
   • We add a rn (rank number) column to every record.
   • PARTITION BY serial groups records by their serial number, so we handle duplicates per serial.
   • The ORDER BY clause ensures:
     • Records with labels starting from A-J are sorted first (the CASE statement assigns 0 to these records, which comes before 1 for non-matching ones).
     • For records in the same priority group (e.g., two A-J labeled records for serial 777), we use id as a tiebreaker to select the oldest record—you can replace this with label or another field if you prefer a different tiebreaker.
2. Outer SELECT:
   • We filter only records where rn = 1, which gives us exactly one record per serial: either the highest-priority (A-J) one, or any record if none meet the condition.

Expected Result #

Running this query against your test data will return exactly the output you want:

id | serial | label  |   type   
----+--------+--------+----------
  1 | 111    | A1     | computer
  2 | 222    | B2     | computer
  4 | 333    | D4     | computer
  5 | 555    | E5     | computer
  6 | 666    | X6     | computer
  7 | 777    | G7     | computer
  9 | 888    | I8     | computer
 10 | 999    | J9     | screen

Why Your Original Query Failed #

Window functions (like COUNT() OVER()) are computed after the WHERE clause runs in SQL's execution order. That's why you couldn't reference nbserial directly in the WHERE clause—using a CTE or subquery lets us compute those values first, then filter on them.

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