在SELECT中，将Select中的子查询相关联，以将ROWID转换为Rowname

Question

如何结合这两组代码？表格：Geregistreerd g，仪器i和Indeling id。

我想显示g.voornaammuzikant，g.achternaammuzikant，i.naaminstrument，id.familie。

表g与表i与instrumentid链接表i与indelingid

的表ID链接

- ofening 1：

SELECT TRIM(' ' FROM g.voornaammuzikant), TRIM(' ' FROM g.achternaammuzikant),
    (SELECT i.naaminstrument 
    from instrument i
    where i.instrumentid = g.instrumentid) as "naam instrument"
from geregistreerd g
order by g.voornaammuzikant;

- ofening 2：

SELECT i.naaminstrument, 
(SELECT id.familie
from indeling id 
where i.indelingid = id.indelingid) as "familie",
(SELECT id.onderfamilie
from indeling id 
where i.indelingid = id.indelingid) as "onderfamilie"

from instrument i
order by i.naaminstrument;

组合

SELECT TRIM(' ' FROM g.voornaammuzikant), 
TRIM(' ' FROM g.achternaammuzikant),
(SELECT i.naaminstrument
        (SELECT id.familie
        from indeling id 
        where i.indelingid = id.indelingid) as "familie"
from instrument i
where i.instrumentid = g.instrumentid) as "naam instrument"  
from geregistreerd g
order by g.voornaammuzikant;

Answer 1

如果可以简化它们，为什么要以复杂的方式做事？简单的加入怎么了？这样的东西：

select g.voornaammuzikant, 
       g.achternaammuzikant, 
       i.naaminstrument, 
       id.familie
from geregistreerd g join instrument i on i.instrumentid = g.instrumentid
join indeling id on id.indelingid = i.indelingid
order by g.voornaammuzikant;

---

顺便说一句，您发布的那些TRIM S看起来不好。SELECT TRIM(' ' FROM g.voornaammuzikant)当然是错误的；我不知道你该说什么。也许从这些值中删除多余的空间？如果它们存在，那么 - 有可能 - 该列的数据类型CHAR？如果是这样，请考虑切换到VARCHAR2，因为前者的垫子值具有该列的整个长度。