场景：我有 3 张表(高级、中级和较低级别(。"较高"表中的每个项目都可以在中间表中包含多行项目。下表可以包含聚合值，这些值连接到较高表的每个项目。
逻辑：目标是将中间表的每一行聚合到较高的表中(完全连接(，并将下表的项目聚合到这些行。所有三个表共享 keyA 列。
+-----+------------+-------------+--------------+
| keyA | HigherName | HigherValue | HigherStatus |
+-----+------------+-------------+--------------+
| 123 | Item1 | 100 | Active |
+-----+------------+-------------+--------------+
| 124 | Item2 | 105 | Inactive |
+-----+------------+-------------+--------------+
| 125 | Item3 | 96 | Inactive |
+-----+------------+-------------+--------------+
| 126 | Item4 | 104 | Inactive |
+-----+------------+-------------+--------------+
| 127 | Item5 | 89 | Active |
+-----+------------+-------------+--------------+
| 128 | Item6 | 97 | Inactive |
+-----+------------+-------------+--------------+
EX 中桌：
+-----+---------+----------+
| keyA | MidName | MidValue |
+-----+---------+----------+
| 123 | Mid1 | 55 |
+-----+---------+----------+
| 123 | Mid2 | 46 |
+-----+---------+----------+
| 123 | Mid3 | 49 |
+-----+---------+----------+
| 124 | Stat1 | 41 |
+-----+---------+----------+
| 124 | Stat2 | 59 |
+-----+---------+----------+
| 124 | Stat3 | 51 |
+-----+---------+----------+
| 125 | Gen1 | 56 |
+-----+---------+----------+
| 125 | Gen2 | 57 |
+-----+---------+----------+
| 126 | Test1 | 44 |
+-----+---------+----------+
| 127 | Lev1 | 42 |
+-----+---------+----------+
| 127 | Lev2 | 49 |
+-----+---------+----------+
| 127 | Lev3 | 50 |
+-----+---------+----------+
| 127 | Lev4 | 60 |
+-----+---------+----------+
| 128 | Spec1 | 61 |
+-----+---------+----------+
| 128 | Spec2 | 39 |
+-----+---------+----------+
EX下层桌子：
+-----+-----------+------------+
| keyA | LowerName | LowerValue |
+-----+-----------+------------+
| 123 | Sub1 | 12 |
+-----+-----------+------------+
| 123 | Sub1 | 14 |
+-----+-----------+------------+
| 123 | Sub1 | 13 |
+-----+-----------+------------+
| 124 | Sub2 | 13 |
+-----+-----------+------------+
| 124 | Sub2 | 10 |
+-----+-----------+------------+
| 124 | Sub2 | 11 |
+-----+-----------+------------+
| 125 | Sub3 | 19 |
+-----+-----------+------------+
| 125 | Sub3 | 14 |
+-----+-----------+------------+
| 126 | Sub4 | 15 |
+-----+-----------+------------+
| 127 | Sub5 | 14 |
+-----+-----------+------------+
| 127 | Sub5 | 13 |
+-----+-----------+------------+
| 127 | Sub5 | 11 |
+-----+-----------+------------+
| 127 | Sub5 | 10 |
+-----+-----------+------------+
| 128 | Sub6 | 11 |
+-----+-----------+------------+
| 128 | Sub6 | 12 |
+-----+-----------+------------+
防爆输出：
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| keyA | HigherName | HigherValue | HigherStatus | MidName | MidValue | LowerName | LowerValue |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 123 | Item1 | 100 | Active | Mid1 | 55 | Sub1 | 12 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 123 | Item1 | 100 | Active | Mid2 | 46 | Sub1 | 14 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 123 | Item1 | 100 | Active | Mid3 | 49 | Sub1 | 13 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 124 | Item2 | 105 | Inactive | Stat1 | 41 | Sub2 | 13 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 124 | Item2 | 105 | Inactive | Stat2 | 59 | Sub2 | 10 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 124 | Item2 | 105 | Inactive | Stat3 | 51 | Sub2 | 11 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 125 | Item3 | 96 | Inactive | Gen1 | 56 | Sub3 | 19 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 125 | Item3 | 96 | Inactive | Gen2 | 57 | Sub3 | 14 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 126 | Item4 | 104 | Inactive | Test1 | 44 | Sub4 | 15 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 127 | Item5 | 89 | Active | Lev1 | 42 | Sub5 | 14 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 127 | Item5 | 89 | Active | Lev2 | 49 | Sub5 | 13 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 127 | Item5 | 89 | Active | Lev3 | 50 | Sub5 | 11 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 127 | Item5 | 89 | Active | Lev4 | 60 | Sub5 | 10 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 128 | Item6 | 97 | Inactive | Spec1 | 61 | Sub6 | 11 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
| 128 | Item6 | 97 | Inactive | Spec2 | 39 | Sub6 | 12 |
+-----+------------+-------------+--------------+---------+----------+-----------+------------+
我做了什么：我试图使用完全联接来合并表。在高和中之间使用完全连接时，一切正常。但是当我尝试包含较低时，我收到错误：
'databaseA.higher.keyA' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
问题：我做错了什么，如何解决这个问题？
到目前为止的代码：
Select
[keyA ] = databaseA.higher.keyA,
[Higher Name ] = databaseA.higher.higher_name,
[Higher Value ] = databaseA.higher.higher_value,
[Higher Status ] = databaseA.higher.higher_status,
[Mid Name ] = databaseA.mid.mid_status,
[Mid Value ] = databaseA.mid.mid_value,
[Lower Name ] = databaseA.lowerA.LowerA_status,
[Lower Value ] = databaseA.lowerA.lowerA_value
from databaseA.higher
FULL JOIN databaseA.mid
on databaseA.higher.keyA = databaseA.mid.keyA
Full JOIN databaseA.lower
on databaseA.higher.keyA = databaseA.lower.keyA
where databaseA.higher.keyA in ('82487')
你来了：
select
h.*,
x.midname, x.midvalue, x.lowername, x.lowervalue
from higher h
left join (
select
coalesce(a.keya, b.keya) as keya,
a.midname, a.midvalue, b.lowername, b.lowervalue
from (
select *,
row_number() over(partition by keya order by midname) as rn
from mid
) a
full outer join (
select *,
row_number() over(partition by keya order by lowername) as rn
from lower
) b on a.keya = b.keya and a.rn = b.rn
) x on h.keya = x.keya
您需要使用人工键连接中下表(我添加了rn列(。然后，只需将较高的表与上一个结果集联接即可。