SQL UNION查询需求：子查询降序取TOP1后合并排序

架构老林

2026-5-21

Solution to Modify UNION Query for TOP 1 Subquery and Final Sorting

嘿，这事儿好办！要实现第二个查询只取按LPRReadDate降序的最新一条记录，再和第一个查询的结果合并后整体排序，我们只需要把第二个查询包装成一个子查询，先完成内部的排序和取数，再和第一个查询做合并，最后统一排序就行。

以下是修改后的完整SQL语句：

SELECT LEFT({dData}.PLATE,7) AS Plates, {dData}.SIGHTING_DT AS LPRReadDate, {dLov}.LOCATION AS LPRLocation 
FROM {dData} 
INNER JOIN {dLov} ON ({dData}.ROAD_NAME = {dLov}.ROAD_NAME) AND ({dData}.READER = {dLov}.READER) 
WHERE ({dData}.SIGHTING_DT >= @sdate AND {dData}.SIGHTING_DT <= @edate) 
  AND (LEN(COALESCE({dData}.PLATE,'')) = @slen1 OR LEN(COALESCE({dData}.PLATE,'')) = @slen2) 
  AND {dData}.PLATE NOT LIKE @rx1 
  AND {dData}.PLATE = @pf 
UNION 
SELECT * 
FROM (
    SELECT TOP 1 LEFT({vData}.PLATE,7) AS Plates, {vData}.SIGHTING_DT AS LPRReadDate, {vLov}.LOCATION AS LPRLocation 
    FROM {vData} 
    INNER JOIN {vLov} ON {vData}.CAMERA = {vLov}.CAMERA 
    WHERE ({vData}.SIGHTING_DT >= @sdate AND {vData}.SIGHTING_DT <= @edate) 
      AND (LEN(COALESCE({vData}.PLATE,'')) = @slen1 OR LEN(COALESCE({vData}.PLATE,'')) = @slen2) 
      AND {vData}.PLATE NOT LIKE @rx1 
      AND {vData}.PLATE = @pf 
    ORDER BY LPRReadDate DESC
) AS LatestVData
ORDER BY LPRReadDate

关键改动说明：

子查询处理第二个数据集：把第二个SELECT放到子查询LatestVData中，通过TOP 1配合ORDER BY LPRReadDate DESC，确保只筛选出该数据集里时间最新的一条记录。如果你的数据库是MySQL，记得把TOP 1换成LIMIT 1就行。
合并与最终排序：用UNION（自动去重）或者UNION ALL（不查重，性能更优）合并第一个查询的所有结果和子查询的单条记录，最后通过外层的ORDER BY LPRReadDate对合并后的所有数据统一排序。