SQL多条件匹配：从关联表中筛选唯一匹配记录的需求

有两张业务表，需求是为studyprint表的每一行匹配printprofiles表中最合适的记录：优先匹配同empresa且remoteaddress完全一致的条目，若无匹配项则选择同empresa且remoteaddress为空的条目，最终每个studyprint行仅对应一条printprofiles记录。

表结构与数据 #

studyprint表 #

create table studyprint(
    idstudyprint serial not null, 
    empresa varchar(4), 
    remoteaddress varchar(100), 
    primary key(idstudyprint)
);

insert into studyprint(empresa, remoteaddress) values('TEST', '');
insert into studyprint(empresa, remoteaddress) values('GAM', '');
insert into studyprint(empresa, remoteaddress) values('GAM', '');
insert into studyprint(empresa, remoteaddress) values('TEST', '192.168.0.100');
insert into studyprint(empresa, remoteaddress) values('TEST', '192.168.0.25');

表数据：

printprofiles表 #

create table printprofiles(
    idprintprofile serial not null, 
    empresa varchar(4), 
    remoteaddress varchar(100), 
    primary key(idprintprofile)
);

insert into printprofiles(empresa, remoteaddress) values('PDF', '');
insert into printprofiles(empresa, remoteaddress) values('HPR', '');
insert into printprofiles(empresa, remoteaddress) values('GAM', '');
insert into printprofiles(empresa, remoteaddress) values('TEST', '192.168.0.100');
insert into printprofiles(empresa, remoteaddress) values('TEST', '');

表数据：

原始查询问题 #

原始查询语句：

select                   
sp.idstudyprint, sp.empresa, pp.idprintprofile, sp.remoteaddress
from studyprint sp
join printprofiles pp on pp.empresa=sp.empresa
where
pp.remoteaddress = sp.remoteaddress or(pp.remoteaddress = '');

该查询会返回重复行（例如idstudyprint=4同时匹配了idprintprofile=4和5），无法满足“一行对应一个匹配项”的需求。

期望结果：

使用窗口函数RANK()为每个studyprint的匹配项排序，优先选择精确匹配的记录，再筛选排名第一的结果：

WITH ranked_profiles AS (
    SELECT
        sp.idstudyprint,
        sp.empresa,
        pp.idprintprofile,
        sp.remoteaddress,
        RANK() OVER (
            PARTITION BY sp.idstudyprint
            ORDER BY 
                CASE WHEN pp.remoteaddress = sp.remoteaddress THEN 1 ELSE 2 END
        ) AS profile_rank
    FROM studyprint sp
    JOIN printprofiles pp 
        ON pp.empresa = sp.empresa
        AND (pp.remoteaddress = sp.remoteaddress OR pp.remoteaddress = '')
)
SELECT idstudyprint, empresa, idprintprofile, remoteaddress
FROM ranked_profiles
WHERE profile_rank = 1;

逻辑说明 #

1. CTE子查询ranked_profiles：按empresa关联两张表，筛选出符合条件的匹配项（精确匹配或空地址），同时用RANK()给每个studyprint的匹配项排名：精确匹配的记录排名为1，空地址匹配的为2。
2. 主查询：仅保留每个studyprint中排名第一的记录，实现“优先精确匹配，无匹配则用空地址”的需求。

若存在同一studyprint对应多个同类型匹配（如多个同empresa的空地址记录），可替换RANK()为ROW_NUMBER()，会随机选取其中一条；若需保留所有同优先级匹配项，保留RANK()即可。

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