查看同一表Access 2010中的另一条记录时,将SQL子查询转换为联接
我已经读到Joins比子查询更有效,我的查询非常慢,并且使用很多子查询,因此我想改进它,但不知道如何做。
我有以下表格:
People \\this table stores lists of individual people with the following fields
(
ID, \\Primary Key
aacode Text, \\represents a individual house
PERSNO number, \\represent the number of the person in the house e.g. person number 1
HRP number, \\the PERSNO of the Housing Reference Person (HRP) the "main" person in the house
DVHsize number, \\the number of people in the house
R01 number, \\the persons relationship to the person who is PERSNO=1
R02 number, \\the persons relationship to the person who is PERSNO=2
R03 number, \\the persons relationship to the person who is PERSNO=3
AgeCat text, \\the age range of the person e.g. 30-44
xMarSta number, \\representing the marital satus of the person
)
Relatives \\this table stores the possible R01 numbers and their text equivalents
(
ID Primary Key, \\all possible R01 values
Relationship text, \\meaning of the corisponding R01 values
)
xMarSta \\this table store the possible xMarSta values and their text equivalents
(
ID Primary Key \\all possible xMarSta values
Marital text, \\meaning of corresponding R01 values
)
查询是:
HsHld
-该查询的目标是为每个房屋(即每个aacode)生成一个文本形式的字符串,以表格[Marital][AgeCat][Relationship][AgeCat][Relationship][AgeCat]
等形式描述房屋。因此,三人房屋的输出可能看起来像Married(30-44)Spouse(30-44)Child(1-4)
我知道我当前的HsHld代码很糟糕,但是包含在下面:
SELECT People.ID, People.aacode, People.PERSNO,
People.HRP, People.DVHsize, xMarSta.Marital,
[Marital] & " (" & [AgeCat] & ")" & [RAL2] & [RAge2] &
[RAL3] & [RAge3] & [RAL4] & [RAge4] & [RAL5] & [RAge5] &
[RAL6] & [RAge6] & [RAL7] & [RAge7] & [RAL8] & [RAge8] AS HsTyp,
(SELECT Fam2.R01 FROM People AS Fam2 WHERE Fam2.aacode = People.aacode
AND Fam2.PERSNO = 2) AS Rel2,
(SELECT Fam3.R01 FROM People AS Fam3 WHERE Fam3.aacode = People.aacode
AND Fam3.PERSNO = 3) AS Rel3,
Switch([Rel2] Is Null,Null,[Rel2]=-9,'DNA',[Rel2]=-8,'NoAns',
[Rel2]=1,'Spouse',[Rel2]=2,'Cohabitee',[Rel2]<7,'Child',
[Rel2]<10,'Parent',[Rel2]<15,'Sibling',[Rel2]=15,'Grandchild',
[Rel2]=16,'Grandparent',[Rel2]=17,'OtherRelative',
[Rel2]=20,'CivilPartner',True,'Other') AS RAL2,
Switch([Rel3] Is Null,Null,[Rel3]=-9,'DNA',[Rel3]=-8,'NoAns',
[Rel3]=1,'Spouse',[Rel3]=2,'Cohabitee',[Rel3]<7,'Child',
[Rel3]<10,'Parent',[Rel3]<15,'Sibling',[Rel3]=15,'Grandchild',
[Rel3]=16,'Grandparent',[Rel3]=17,'OtherRelative',
[Rel3]=20,'CivilPartner',True,'Other') AS RAL3,
(Select FAge2.AgeCat FROM People AS FAge2
WHERE FAge2.aacode = People.aacode
AND FAge2.PERSNO = 2
) AS RAge2,
(Select FAge3.AgeCat FROM People AS FAge3
WHERE FAge3.aacode = People.aacode AND FAge3.PERSNO = 3
) AS RAge3
FROM Relatives
RIGHT JOIN (xMarSta RIGHT JOIN People ON xMarSta.ID=People.xMarSta)
ON Relatives.ID=People.R01
WHERE (((People.HRP)=[People.PERSNO]))
ORDER BY People.aacode;
有几项关键的事情需要改变。
- 目前,我无法从Rel字段连接到Relatives表,因此我正在使用一种名为RAL的Switch函数,必须有一种更好的方法。
- 为了简单起见,我只包含了Rel2和Rel3等,但是在实际代码中,它取决于Rel13!因此,性能问题更加严重。
- 我想用联接替换这些子查询,但是当子查询查找同一表中的另一条记录时,我不确定如何执行此操作。
- 我对此非常不了解,我知道一些SQL,但是对于我有限的知识来说,此问题的复杂性太大
-
首先是您有一个关系情况,但是您拥有的表结构使用列来表示关系。这将为您提供表上的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;