sql - 连接同一个表中不同 id 的两行

Question

我想连接同一个表中不同 ID 的两行。

Fruit table:
name:    from_id:   to_id:
Apple    Spain      Italy

Country table:
id:      packaging_type:    packaging_quantity:
Italy    wood box           12
Spain    paper box          18

我想要这张 table :

Spain paper box 18 Italy wood box 12

我尝试了这样的事情:

SELECT a.packaging_type, a.packaging_quantity, b.packaging_type, b.packaging_quantity 
FROM fruit
    inner join country a on a.country.id = fruit.from_id
    inner join country b on b.country.id = fruit.to_id
WHERE fruit.name = 'Apple';

但是我得到了一个错误:

ORA-00904: "A"."COUNTRY"."ID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 2 Column: 12

Answer 1

您正在 INNER JOIN 中为县表设置别名。您无需在别名 a 或 s 后重复表名称。只需使用 a.id 而不是 a.county.id。

SELECT a.packaging_type, a.packaging_quantity, b.packaging_type, b.packaging_quantity 
FROM fruit
INNER JOIN country a ON a.id = fruit.from_id
INNER JOIN country s ON b.id = fruit.to_id
WHERE fruit.NAME = 'Apple';

此外，如果您想保持一致，可以为 fruit 表使用别名。

SELECT a.packaging_type, a.packaging_quantity, b.packaging_type, b.packaging_quantity 
FROM fruit f
INNER JOIN country a ON a.id = f.from_id
INNER JOIN country s ON b.id = f.to_id
WHERE f.NAME = 'Apple';