如何将查询结果中的列结果与表中的列值匹配，并从同一表中获取更多列？ [英] How to match a column result from query result to a table column value and get more columns from same table?

问题描述

这是我以前的

更新

这是我要尝试的

 选择 PROJECT_ID作为PROJECT_NO，
 CASE_NO 
 FROM LOG_METADATA lm 
 JOIN 
（SELECT DISTINCT 
 ON（fa_identifier）fa_identifier，
 fas.case_no AS CASE_NO 
 FROM filter_analysis_store fas 
 LEFT JOIN share_analysis_store sas使用（fa_identifier）
 fas.created_by ='[e-mail]'
 OR sas.shared_to ='[e-mail]'
）res使用（  CASE_NO）; 
  

解决方案

您不需要为此的子选择，只需仔细选择输出列即可：

 从filter_analysis_store fas 
中选择不同的lm.project_id，case_no 
左使用（fa_identifier）
加入share_analysis_store sas使用（case_no）
加入log_metadata lm其中fas.created_by ='[e-mail]'
或sas.shared_to ='[e-mail] '; 
  

如果您不想使用 case_no 出现在多个 project_id s下，对（case_no）lm.project_id，case_no 进行选择。您可以控制 project_id 应该在哪个 case_no 出现的情况下使用 ORDER BY 在这种情况下。

This is a continuation of my previous question

The query is given below, taken from this answer

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

Result

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

Another table name is "LOG_METADATA", for simplicity sake I am providing only few columns for this table

id,PROJECT_ID,CASE_NO
1,2-0007362,02029418
2,2-0007362,01998455
3,1-5379513,02039596
...

So for a given project I need to list all case nos, here project is an array and each project can have multiple case nos if we interpret it visually. Something like below screenshot in UI.

Update

Here is what I am trying out

SELECT "PROJECT_ID" AS PROJECT_NO,
  "CASE_NO"
FROM "LOG_METADATA" lm
JOIN
  (SELECT DISTINCT
  ON (fa_identifier) fa_identifier,
    fas.case_no AS "CASE_NO"
  FROM filter_analysis_store fas
  LEFT JOIN share_analysis_store sas USING (fa_identifier)
  WHERE fas.created_by = '[e-mail]'
  OR sas.shared_to     = '[e-mail]'
  ) res USING ("CASE_NO");

解决方案

You don't need a sub-select for that, just select your output columns carefully:

select    distinct lm.project_id, case_no
from      filter_analysis_store fas
left join share_analysis_store sas using (fa_identifier)
join      log_metadata lm using (case_no)
where     fas.created_by = '[e-mail]'
or        sas.shared_to  = '[e-mail]';

If you don't want to a case_no appear under multiple project_ids, do a select distinct on (case_no) lm.project_id, case_no instead. You could control under which project_id should case_nos appear with ORDER BY in that case.

这篇关于如何将查询结果中的列结果与表中的列值匹配，并从同一表中获取更多列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！