我读过联接比子查询更有效,我有一个查询非常慢并且使用大量子查询,因此我想改进它但不知道如何改进。
我有以下表格:
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 字段连接到亲戚
表工作,所以我使用名为 RAL 的 Switch 函数,必须
是一个更好的方式。
- 为了简单起见,我在帖子中只包含了 Rel2 和 Rel3 等,但在实际代码中它上升到 Rel13!所以性能问题就更严重了。
- 我想用联接替换这些子查询,但作为子查询
查看同一张表中的另一条记录,我不确定如何去
对这个。
- 我对此非常不了解,我懂一点 SQL,但是
这个问题的复杂性对于我有限的知识来说太复杂了