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

huangapple
go评论73阅读模式
英文:

Choosing one record ouf of different conditions

问题

根据这两个表：

studyprint：

  1. create table studyprint(
  2.     idstudyprint serial not null, 
  3.     empresa varchar(4), 
  4.     remoteaddress varchar(100), 
  5.     primary key(idstudyprint)
  6. );                                                           
  8. insert into studyprint(empresa, remoteaddress) values('TEST', '');
  9. insert into studyprint(empresa, remoteaddress) values('GAM', '');
  10. insert into studyprint(empresa, remoteaddress) values('GAM', '');
  11. insert into studyprint(empresa, remoteaddress) values('TEST', '192.168.0.100');
  12. insert into studyprint(empresa, remoteaddress) values('TEST', '192.168.0.25');
  14.  idstudyprint | empresa | remoteaddress 
  15. --------------+---------+---------------
  16.             1 | TEST    | 
  17.             2 | GAM     | 
  18.             3 | GAM     | 
  19.             4 | TEST    | 192.168.0.100
  20.             5 | TEST    | 192.168.0.25

printprofiles

  1. create table printprofiles(
  2.     idprintprofile serial not null, 
  3.     empresa varchar(4), 
  4.     remoteaddress varchar(100), 
  5.     primary key(idprintprofile)
  6. );
  8. insert into printprofiles(empresa, remoteaddress) values('PDF', '');
  9. insert into printprofiles(empresa, remoteaddress) values('HPR', '');
  10. insert into printprofiles(empresa, remoteaddress) values('GAM', '');
  11. insert into printprofiles(empresa, remoteaddress) values('TEST', '192.168.0.100');
  12. insert into printprofiles(empresa, remoteaddress) values('TEST', '');
  14.  idprintprofile | empresa | remoteaddress 
  15. ----------------+---------+---------------
  16.               1 | PDF     | 
  17.               2 | HPR     | 
  18.               3 | GAM     | 
  19.               4 | TEST    | 192.168.0.100
  20.               5 | TEST    |

我提出的第一个查询是：

  1. select                   
  2. sp.idstudyprint, sp.empresa, pp.idprintprofile, sp.remoteaddress
  3. from studyprint sp
  4. join printprofiles pp on pp.empresa=sp.empresa
  5. where
  6. pp.remoteaddress = sp.remoteaddress or(pp.remoteaddress = '');

其结果为：

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

由于studyprint中的remoteaddress与printprofiles中的remoteaddress不匹配的情况，对于这些情况，选择必须是匹配的empresa（在我的示例中，'empresa=5'的remoteaddress为空，应该适应所有不匹配的remoteaddress），例如：

  1.  idstudyprint | empresa | idprintprofile | remoteaddress 
  2. --------------+---------+----------------+---------------
  3.             1 | TEST    |              5 | 
  4.             2 | GAM     |              3 | 
  5.             3 | GAM     |              3 | 
  6.             4 | TEST    |              4 | 192.168.0.100
  7.             5 | TEST    |              5 | 192.168.0.25
英文:

Given these two tables:

studyprint:

  1. create table studyprint(
  2.     idstudyprint serial not null, 
  3.     empresa varchar(4), 
  4.     remoteaddress varchar(100), 
  5.     primary key(idstudyprint)
  6. );                                                           
  8. insert into studyprint(empresa, remoteaddress) values('TEST', '');
  9. insert into studyprint(empresa, remoteaddress) values('GAM', '');
  10. insert into studyprint(empresa, remoteaddress) values('GAM', '');
  11. insert into studyprint(empresa, remoteaddress) values('TEST', '192.168.0.100');
  12. insert into studyprint(empresa, remoteaddress) values('TEST', '192.168.0.25');
  14.  idstudyprint | empresa | remoteaddress 
  15. --------------+---------+---------------
  16.             1 | TEST    | 
  17.             2 | GAM     | 
  18.             3 | GAM     | 
  19.             4 | TEST    | 192.168.0.100
  20.             5 | TEST    | 192.168.0.25

printprofiles

  1. create table printprofiles(
  2.     idprintprofile serial not null, 
  3.     empresa varchar(4), 
  4.     remoteaddress varchar(100), 
  5.     primary key(idprintprofile)
  6. );
  8. insert into printprofiles(empresa, remoteaddress) values('PDF', '');
  9. insert into printprofiles(empresa, remoteaddress) values('HPR', '');
  10. insert into printprofiles(empresa, remoteaddress) values('GAM', '');
  11. insert into printprofiles(empresa, remoteaddress) values('TEST', '192.168.0.100');
  12. insert into printprofiles(empresa, remoteaddress) values('TEST', '');
  14.  idprintprofile | empresa | remoteaddress 
  15. ----------------+---------+---------------
  16.               1 | PDF     | 
  17.               2 | HPR     | 
  18.               3 | GAM     | 
  19.               4 | TEST    | 192.168.0.100
  20.               5 | TEST    |

The first query I came up with is this:

  1. select                   
  2. sp.idstudyprint, sp.empresa, pp.idprintprofile, sp.remoteaddress
  3. from studyprint sp
  4. join printprofiles pp on pp.empresa=sp.empresa
  5. where
  6. pp.remoteaddress = sp.remoteaddress or(pp.remoteaddress = '');

Which results in:

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

As there are cases where a remoteaddress in studyprint doesn't match a remoteaddress in printprofiles, in those cases the selection must be the empresa that matches (in my example, empresa=5 has remoteaddress empty, there should fit all the remoteaddresses that doesn't match), for example:

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

答案1

得分: 1

实际上，你并没有提供详细的说明，但是基于你提供的数据，我写了查询。结果符合你的要求。如果有其他问题，请随时告诉我，我可以帮忙。

我修改了我的查询：

  1. select 
  2.     t1.idstudyprint, 
  3.     t1.empresa, 
  4.     case when t2.idprintprofile is null then t3.idprintprofile else t2.idprintprofile end as idprintprofile, 
  5.     t1.remoteaddress 
  6. from studyprint t1 
  7. left join 
  8.     printprofiles t2 
  9.     on (t1.empresa = t2.empresa and t1.remoteaddress = t2.remoteaddress) 
  10. left join 
  11.     printprofiles t3 
  12.     on t2.idprintprofile is null and t1.empresa = t3.empresa and t3.remoteaddress='' 
  14. --结果： 
  15. idstudyprint | empresa | idprintprofile | remoteaddress |
  16. -------------+---------+----------------+---------------+
  17.            1 | TEST    |              5 |               |
  18.            2 | GAM     |              3 |               |
  19.            3 | GAM     |              3 |               |
  20.            4 | TEST    |              4 | 192.168.0.100 |
  21.            5 | TEST    |              5 | 192.168.0.25  |
英文:

Actually, you didn't write a full detailed explanation, but still, based on the data you wrote, I wrote the query. The result is what you want. Let me know if you have any other questions, I can help.

I changed my query:

  1. select 
  2.     t1.idstudyprint, 
  3.     t1.empresa, 
  4.     case when t2.idprintprofile is null then t3.idprintprofile else t2.idprintprofile end as idprintprofile, 
  5.     t1.remoteaddress 
  6. from studyprint t1 
  7. left join 
  8.     printprofiles t2 
  9.     on (t1.empresa = t2.empresa and t1.remoteaddress = t2.remoteaddress) 
  10. left join 
  11.     printprofiles t3 
  12.     on t2.idprintprofile is null and t1.empresa = t3.empresa and t3.remoteaddress='' 
  13.     
  14. --Result: 
  15. idstudyprint | empresa | idprintprofile | remoteaddress |
  16. -------------+---------+----------------+---------------+
  17.            1 | TEST    |              5 |               |
  18.            2 | GAM     |              3 |               |
  19.            3 | GAM     |              3 |               |
  20.            4 | TEST    |              4 | 192.168.0.100 |
  21.            5 | TEST    |              5 | 192.168.0.25  |

huangapple
  • 本文由 发表于 2023年2月8日 19:44:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/75385333.html
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定