如何将嵌套SELECT的SQL查询改写为JOIN语句以提升性能？

Absolutely! Those nested subqueries can create unnecessary overhead, especially on large datasets. Let’s first clarify what your original query is trying to do: it’s counting the number of records where each row is the latest (max dateTo) entry for its combination of Id, Status, and Code, while also filtering for Status = 1 and Id between 12 and 31307.

Here are two better approaches to replace those nested subqueries, both of which should perform much better:

Option 1: Use a Window Function (Cleanest & Often Most Efficient) #

Window functions let you rank records within groups without messy nested subqueries. This approach scans the table once, ranks each record in its (Id, Status, Code) group by dateTo descending, then counts only the top-ranked (latest) records:

SELECT COUNT(*) AS count
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Id, Status, Code ORDER BY dateTo DESC) AS record_rank
    FROM USERS.Names
    WHERE Status = 1 
      AND Id >= 12 
      AND Id < 31308
) ranked_records
WHERE record_rank = 1;

If there’s a chance multiple records in the same group have the exact same maximum dateTo and you want to count all of them, replace ROW_NUMBER() with RANK() instead.

Option 2: Use a JOIN with Aggregated Subquery #

This approach first precomputes the maximum dateTo for each (Id, Status, Code) group, then joins back to the original table to fetch matching records:

SELECT COUNT(DISTINCT p.rowid) AS count
FROM USERS.Names p
JOIN (
    SELECT 
        Id, 
        Status, 
        Code, 
        MAX(dateTo) AS latest_dateTo
    FROM USERS.Names
    WHERE Status = 1 
      AND Id >= 12 
      AND Id < 31308
    GROUP BY Id, Status, Code
) latest ON p.Id = latest.Id 
         AND p.Status = latest.Status 
         AND p.Code = latest.Code 
         AND p.dateTo = latest.latest_dateTo
WHERE p.Status = 1 
  AND p.Id >= 12 
  AND p.Id < 31308;

The COUNT(DISTINCT p.rowid) ensures we don’t double-count if multiple records share the same maximum dateTo in a group. If your data guarantees dateTo is unique per (Id, Status, Code) group, you can safely use COUNT(*) instead.

Pro Tip for Even Better Performance #

Add a composite index tailored to this query to speed up filtering, grouping, and joining:

CREATE INDEX idx_names_status_id_code_dateto ON USERS.Names (Status, Id, Code, dateTo);

This index will let the database quickly find the relevant rows and compute the max dateTo without scanning the entire table.

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