postgresql 从不同条件中选择一条记录

给定这两个表：

研究图纸：

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');

 idstudyprint | empresa | remoteaddress 
--------------+---------+---------------
            1 | TEST    | 
            2 | GAM     | 
            3 | GAM     | 
            4 | TEST    | 192.168.0.100
            5 | TEST    | 192.168.0.25

打印配置文件

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', '');

 idprintprofile | empresa | remoteaddress 
----------------+---------+---------------
              1 | PDF     | 
              2 | HPR     | 
              3 | GAM     | 
              4 | TEST    | 192.168.0.100
              5 | 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 | empresa | idprintprofile | remoteaddress 
--------------+---------+----------------+---------------
            1 | TEST    |              5 | 
            2 | GAM     |              3 | 
            3 | GAM     |              3 | 
            4 | TEST    |              5 | 192.168.0.100
            4 | TEST    |              4 | 192.168.0.100
            5 | TEST    |              5 | 192.168.0.25

由于存在studyprint中的远程地址与printprofiles中的远程地址不匹配的情况，在这些情况下，选择必须是匹配的empresa（在我的示例中，empresa=5的远程地址为空，应适合所有不匹配的远程地址），例如：

idstudyprint | empresa | idprintprofile | remoteaddress 
--------------+---------+----------------+---------------
            1 | TEST    |              5 | 
            2 | GAM     |              3 | 
            3 | GAM     |              3 | 
            4 | TEST    |              4 | 192.168.0.100
            5 | TEST    |              5 | 192.168.0.25

实际上，你没有写完整详细的解释，但我还是根据你写的数据写了查询。结果是你想要的。如果你有任何其他问题，请告诉我，我可以帮助你。
我更改了我的查询：

select 
    t1.idstudyprint, 
    t1.empresa, 
    case when t2.idprintprofile is null then t3.idprintprofile else t2.idprintprofile end as idprintprofile, 
    t1.remoteaddress 
from studyprint t1 
left join 
    printprofiles t2 
    on (t1.empresa = t2.empresa and t1.remoteaddress = t2.remoteaddress) 
left join 
    printprofiles t3 
    on t2.idprintprofile is null and t1.empresa = t3.empresa and t3.remoteaddress='' 
    
--Result: 
idstudyprint | empresa | idprintprofile | remoteaddress |
-------------+---------+----------------+---------------+
           1 | TEST    |              5 |               |
           2 | GAM     |              3 |               |
           3 | GAM     |              3 |               |
           4 | TEST    |              4 | 192.168.0.100 |
           5 | TEST    |              5 | 192.168.0.25  |

postgresql 从不同条件中选择一条记录

1条答案

相关问题

热门标签

最新问答