内部联接在配置单元中不提供结果
内部联接在配置单元中不提供结果
提问于 2020-11-17 10:28:26
我正试着加入四人组。一个是主表，并将其他3个表与该主表连接起来。数据如下图所示：
预期输出。
下面是我开发的查询，它总是返回0行。
select COALESCE(TableB.Date, TableC.Date, TableD.Date),
COALESCE(count(key1),0),
COALESCE(count(key2),0),
COALESCE(count(key3),0)
FROM TableA A JOIN TableB B on A.Date = B.Date
JOIN TableC C on A.Date = C.Date
JOIN TableD D on A.Date = D.Date
Group by COALESCE(TableB.Date, TableC.Date, TableD.Date);当我用TableA在每个表上运行单独的查询(参见下面的查询)时，它会返回数据，但当我连接所有3个表时，它不会返回任何数据。
select TableB.Date, count(key1)
FROM TableA A JOIN TableB B on A.Date = B.Date
Group by TableB.Date;我不确定出了什么问题，有人能帮我理解join query的问题在哪里吗？
谢谢，巴布
回答 3
Stack Overflow用户
发布于 2020-11-17 15:19:50
你可以试试这个-
select A.Date,
COALESCE(count(key1),0),
COALESCE(count(key2),0),
COALESCE(count(key3),0)
FROM TableA A
LEFT JOIN TableB B on A.Date = B.Date
LEFT JOIN TableC C on A.Date = C.Date
LEFT JOIN TableD D on A.Date = D.Date
Group by A.Date;我使用left join而不是inner join，因为我们需要表A中的所有数据。而且第一列应该来自表A，也就是驱动器表。
Stack Overflow用户
发布于 2020-11-17 15:35:27
对子查询进行联接，然后进行分组，例如，
with join_table as (select COALESCE(TableB.Date, TableC.Date, TableD.Date) as Date,
key1, key2, key3
FROM TableA A JOIN TableB B on A.Date = B.Date
JOIN TableC C on A.Date = C.Date
JOIN TableD D on A.Date = D.Date
)
select Date,
COALESCE(count(key1),0),
COALESCE(count(key2),0),
COALESCE(count(key3),0)
from join_table
group by Date实际上，您不需要在内部联接中合并联接键。
Stack Overflow用户
发布于 2020-11-18 01:05:42
我认为你正在寻找完全的外部连接。下面是示例代码
select COALESCE(a.Date, b.Date, c.Date,d.Date),
COALESCE(sum(key1),0),
COALESCE(sum(key2),0),
COALESCE(sum(key3),0)
(select distinct date as date from tableA ) as a
full outer join
(select date ,sum(key1) as key1 from tableB group by date) as b
on a.date=b.date
full outer join
(select date ,sum(key2) as key2 from tableB group by date) as c
on a.date=c.date
full outer join
(select date ,sum(key3) as key2 from tableB group by date) as d
on a.date=d.date
Group by COALESCE(a.Date, b.Date, c.Date,d.Date);`页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64868488
复制相关文章
相似问题
腾讯云开发者
