从两个表创建第三个表 [英] Create a third table from two tables

问题描述

我必须使用两个表来创建第三个表。 TableA不包含所有必需的信息，因为它仅列出了人们用来了解我们公司的资源，而TableB列出了人们可以利用的所有可能的资源。我希望TableC为未使用的源显示0％。在postgreSQL中怎么可能？

I have two tables I have to utilize to create a third table. TableA does not contain all the information required since it only lists sources people used to learn about our company while TableB has all the possible sources listed that people can utilize. I want TableC to show 0% for the sources which were not used. How is this possible in postgreSQL?

select "Source", to_char(100 * count(*) / sum(count(*)) over (), '990%') as "The Ratio"
from TableA 
group by "Source";

Source:       The Ratio:

Website         55%
TV              25%
Radio           20%


Select * from Table2:


Source:

Website
TV
Radio
BillBoard
Referral

结果我谦虚地请求以下方面的帮助：

Result I humbly request assistance with:

Source:                The Ratio:

Website                 55%
TV                      25% 
Radio                   20%
BillBoard                0%
Referral                 0%

推荐答案

将结果与table2合并，如下所示：

Left join the result with table2 like this:

select t2."Source", to_char(coalesce(t1."The Ratio",0),'990%') "The Ratio"
from table2 t2 left outer join
  (select "Source", 100 * count(*) / sum(count(*)) over ()  "The Ratio"
   from TableA 
   group by "Source") t1
on t1."Source" = t2."Source";

这篇关于从两个表创建第三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！