如何从Postgresql中UNION的第一个表中获取列？ [英] How to get a column from first table on UNION in postgresql?

问题描述

我需要提取 [e-mail]或shared_to [e-mail]创建的所有文件。以下是获得结果所需的2个表。

I have a requirement to fetch all files which is created_by "[e-mail]" or shared_to "[e-mail]". Below are the 2 tables which is needed to achieve the result.

filter_analysis_store

filter_analysis_store

fa_identifier,created_by,case_no
FA000179,[e-mail],01998455
FA000183,[e-mail],01998455
FA000184,[e-mail],02039960
FA000185,[e-mail],02039960
FA000187,[e-mail],02039596
FA000189,[e-mail],02039960
FA000190,[e-mail],02029418
FA000191,[e-mail],02029418
FA000192,[e-mail],02039596
FA000193,[e-mail],02039596
FA000194,[e-mail],02039596
FA000195,[e-mail],01912596
FA000199,[e-mail],02039596

share_analysis_store

share_analysis_store

fa_identifier,shared_by,shared_to
FA000173,[e-mail],[e-mail]
FA000196,[e-mail],[e-mail]
FA000180,[e-mail],[e-mail]
FA000198,[e-mail],[e-mail]

我使用 UNION提出了一个查询，该查询给了我结果我必须为第二个查询提供空字符串。

I used "UNION" to come up with a query which gives me the result but I had to give empty string for 2nd query. How to get respective case_no for missing ones?

select fas.fa_identifier, fas.case_no as case_no from filter_analysis_store fas  
where created_by like '[e-mail]' UNION 
select sas.fa_identifier, '' from share_analysis_store sas
where shared_to like '[e-mail]';

以上查询的输出

FA000184    02039960
FA000183    01998455
FA000193    02039596
FA000199    02039596
FA000180    
FA000189    02039960
FA000195    01912596
FA000185    02039960
FA000194    02039596
FA000190    02029418
FA000191    02029418
FA000173    
FA000192    02039596
FA000198    
FA000179    01998455
FA000187    02039596
FA000196    

推荐答案

您不需要 UNION 生成行，是由或创建并共享到 [e-mail] （简单的 LEFT JOIN 就足够了（ LEFT ，因为 fa_identifier 可能永远也不会共享）

You don't need a UNION to produce rows, which are created by or shared to "[e-mail]", a simple LEFT JOIN could suffice (LEFT, because a fa_identifier may never even get shared).

select    distinct on (fa_identifier) fa_identifier, f.case_no
from      filter_analysis_store f
left join share_analysis_store s using (fa_identifier)
where     f.created_by = '[e-mail]'
or        s.shared_to  = '[e-mail]'

与的区别仅需要产生每个 fa_identifier 一次。如果那是 filter_analysis_store 中的主键（或至少是其中的唯一键），则选择不同的fa_identifier f.case_no 应该足够了。

distinct on is required to produce every fa_identifier only once. If that is the primary key in filter_analysis_store (or at least a unique key in that), then select distinct fa_identifier, f.case_no should be enough.

这篇关于如何从Postgresql中UNION的第一个表中获取列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！