如何将带INNER JOIN的统计查询与含RANK函数的子查询结合？

需求说明 #

现有两个SQL查询：

• 查询A：关联[XC_DATA].[dbo].[xc_sites]和[XC_DATA].[dbo].[xc_data1]，统计最近7天的记录数、最早/最晚时间等站点数据
• 查询B：用RANK() OVER分区排序，获取xc_data1表中最近30天的传感器最新记录

需要将两个查询的结果横向合并，同时展示统计值与最新记录，使用UNION失败（因UNION要求结果集结构完全一致，而此处是横向拼接数据，非纵向合并）。

合并后的SQL代码 #

SELECT 
    a.station_id,
    a.sensorname,
    a.site_comment,
    a.SITE_LONG_NAME,
    a.IPADDRESS,
    a.result_count,
    b.Last_record,
    a.start_time,
    a.last_time
FROM (
    -- 查询A：最近7天站点统计数据
    SELECT 
        xc_data1.station_id, 
        xc_data1.sensorname, 
        xc_sites.site_comment,
        xc_sites.SITE_LONG_NAME,
        xc_sites.IPADDRESS,
        COUNT(xc_data1.time_tag) AS result_count,
        MIN(xc_data1.time_tag) AS start_time,
        MAX(xc_data1.time_tag) AS last_time
    FROM 
        [XC_DATA].[dbo].[xc_sites] 
    INNER JOIN 
        [XC_DATA].[dbo].[xc_data1] ON xc_sites.station_id = xc_data1.station_id
    WHERE
        time_tag > DATEADD(day, -7, GETDATE())
    GROUP BY 
        xc_data1.station_id, xc_data1.sensorname,
        xc_sites.site_comment, xc_sites.SITE_LONG_NAME, xc_sites.IPADDRESS
) a
INNER JOIN (
    -- 查询B：最近30天传感器最新记录（修正分区条件，加入sensorname确保每个传感器独立排序）
    SELECT 
        station_id, 
        sensorname, 
        orig_value AS Last_record
    FROM   
        (SELECT 
             station_id, sensorname, time_tag, orig_value,
             RANK() OVER (PARTITION BY station_id, sensorname ORDER BY time_tag DESC) AS rk
         FROM   
             [XC_DATA].[dbo].[xc_data1]
         WHERE time_tag > DATEADD(day, -30, GETDATE())
        ) t
    WHERE  
        rk = 1 
) b ON a.station_id = b.station_id AND a.sensorname = b.sensorname
ORDER BY 
    a.last_time DESC

关键说明 #

1. 替换UNION为INNER JOIN：UNION用于纵向拼接同结构结果集，此处需通过station_id和sensorname两个唯一标识字段，横向关联统计数据与最新记录
2. 修正查询B的分区条件：原查询仅按station_id分区，若同一站点有多个传感器，会导致排序错误，需加入sensorname确保每个传感器的记录独立排序

原查询结果与期望结果 #

查询A结果

查询B结果

期望结果

内容的提问来源于stack exchange，提问作者Mr. Geologist