首先是您有一个关系情况,但是您拥有的表结构使用列来表示关系。这将为您提供表上的R01,R02,R03 … R13列。不幸的是,由于表结构是重复的非规范化而不是关系式的,因此您将无法显着改变性能。这意味着您的查询将需要所有这些重复的代码,正如您提到的要重复13次。这也意味着您的切换功能可以由联接代替,但将再次重复13次。
右,现在回到您的查询,您在查询上有多个子选择,您需要在FROM子句的左联接上联接相关表,并在选择上使用新的相关别名。现在,您将在下面的示例中看到,对于每个R01,R02字段,您将拥有一个Fam2,Fam3关系,并且需要根据您的情况执行13次此操作,并且对于每个关系,您都需要链接到亲属表(如我确实叫Relat2,Relat3等)。现在,如果您可以将数据库结构更改为规范化的结构,则可以真正简化此查询并使用更简单的联接。
看看这是否有助于您理解该过程:
SELECT People.ID, People.aacode, People.PERSNO,
People.HRP, People.DVHsize, xMarSta.Marital,
[Marital] & " (" & [People.AgeCat] & ")" & [RAL2] & [RAge2] &
[RAL3] & [RAge3] AS HsTyp,
Fam2.R01 AS Rel2,
Fam3.R01 AS Rel3,
Relat2.Relationship as RAL2,
Relat3.Relationship as RAL3,
Fam2.AgeCat AS RAge2,
Fam3.AgeCat AS RAge3
FROM (((((People
LEFT JOIN (People AS Fam2) ON (Fam2.aacode = People.aacode and Fam2.PERSNO = 2))
LEFT JOIN (Relatives as Relat2) on Relat2.Id = Fam2.R01)
LEFT JOIN (People as Fam3) ON (Fam3.aacode = People.aacode AND Fam3.PERSNO = 3))
LEFT JOIN (Relatives as Relat3) on Relat3.Id = Fam3.R01)
LEFT JOIN xMarSta ON xMarSta.ID=People.xMarSta)
WHERE (People.HRP=[People.PERSNO])
ORDER BY People.aacode;