如何检测值在每个表中是否存在

Question

我想检测每个表中value是否存在。我有以下表格

tableA

code value
a    1
b    2
c    3

tableB

code value
a    4
c    5 
d    6

tableC

code value
e    10  
f    11
g    12  

我想设置变量。如果我设置值为value = 'a'，我想要的结果如下:

value = a在tableA中存在，因此设置tableA为1;tableC中不存在value = a，因此设置0值。

code tableA tableB tableC valueA valueB valueC
a    1      1      0      1      4      null

我尝试了如下操作，但是我不知道如何连接另一个表。

EXISTS (SELECT 1 FROM tableA WHERE code = 'a')

是否有更聪明的方法来实现这一点?如果有人有什么意见，请告诉我由于

Answer 1

我可能只是加入您正在寻找的三个数据集。您可以使用伪完全外部连接(因为没有真正的连接标准)或聚合的交叉连接来实现此目的:

with a as (select min(value) as val from tablea where code = 'a')
, b as (select min(value) as val from tableb where code = 'a')
, c as (select min(value) as val from tablec where code = 'a')
select
a.val is not null as code_exists_in_a,
b.val is not null as code_exists_in_b,
c.val is not null as code_exists_in_c,
a.val as value_in_a,
b.val as value_in_b,
c.val as value_in_c
from a cross join b cross join c;

(对于完整的外连接，您可以将select min(value) as val替换为select code, value as val，将from a cross join b cross join c替换为from a full outer join b using (code) full outer join c using (code))。

免责声明:如果表中的值可以为空，并且您希望将其显示为value存在，但value为空，则必须为此调整上述查询。

Answer 2

假设code在每个表上是唯一的

您可以使用full outer join来返回在左(tableA)或右(tableB)中有匹配的所有记录:

Select coalesce(a.code, b.code, c.code) as code,
MAX(case when a.code is not null then 1 else 0 end)  as tableA,
MAX(case when b.code is not null then 1 else 0 end)  as tableB,
MAX(case when c.code is not null then 1 else 0 end)  as tableC,
MAX(coalesce(a.value,0))  as valueA,
MAX(coalesce(b.value,0))  as valueB,
MAX(coalesce(c.value,0)) as valueC
from tableA a
full outer join TableB b on a.code = b.code
full outer join TableC c on c.code = b.code
where a.code = 'a' or b.code = 'a' or c.code = 'a'
group by coalesce(a.code, b.code, c.code)

或者在full outer join上使用USING代替ON:

Select coalesce(a.code, b.code, c.code) as code,
case when a.code is not null then 1 else 0 end as tableA,
case when b.code is not null then 1 else 0 end as tableB,
case when c.code is not null then 1 else 0 end as tableC,
coalesce(a.value,0) as valueA,
coalesce(b.value,0) as valueB,
coalesce(c.value,0) as valueC
from tableA a
full outer join TableB b using (code)
full outer join TableC c using (code)
where a.code = 'a' or b.code = 'a' or c.code = 'a'

结果:

code    tablea  tableb  tablec  valuea  valueb  valuec
a       1       1       0       1       4       null

演示

Answer 3

selvazi的回答很好很干净。但是，如果在任何表中都找不到该值，则不会返回结果行。如果值不止一次出现在一个或多个表中，则显示多个结果行。因此，最好先制作一个响应表，然后在响应表中查找答案:

select 
req.code, 
resp."tableA", 
resp."tableB", 
resp."tableC", 
resp."valueA", 
resp."valueB", 
resp."valueC" 
from 
(select 'a' as code) req 
left join
(Select 
coalesce(codeA, codeB, codeC) as code,
max(case when codeA is not null then 1 else 0 end) as "tableA",
max(case when codeB is not null then 1 else 0 end) as "tableB",
max(case when codeC is not null then 1 else 0 end) as "tableC",
string_agg(distinct valueA, ', ') as "valueA",
string_agg(distinct valueB, ', ') as "valueB",
string_agg(distinct valueC, ', ') as "valueC"
from 
(select 
* 
from 
(select code as codeA, value as valueA from tableA) a
full outer join 
(select code as codeB, value as valueB from tableB) b 
on a.codeA = b.codeB
full outer join 
(select code as codeC, value as valueC from tableC) c 
on c.codeC = b.codeB) 
as subresp 
group by 
coalesce(codeA, codeB, codeC)
) resp 
on req.code = resp.code

在本例中，我假设您希望从表中返回所有值，因此我对它们进行字符串聚合。你可以随意用最小值、最大值或任何你喜欢的值来代替它。

数据库的完整性