查看同一表Access 2010中的另一条记录时，将SQL子查询转换为联接 [英] Convert a SQL subquery into a join when looking at another record in the same table Access 2010

问题描述

我已阅读到Joins比子查询更有效，我的查询非常慢，并且使用很多子查询，因此我想对其进行改进，但不知道如何做.

I have read that Joins are more efficient than subqueries, I have a query that is extremely slow and uses lots of subqueries, therefore I would like to improve it but do not know how.

我有下表:

 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]等形式描述房屋的文本字符串.因此，三人房屋的输出可能类似于

HsHld - the goal of this query is to produce for each house (i.e. each aacode) a text sting describing the house in the form [Marital][AgeCat][Relationship][AgeCat][Relationship][AgeCat] etc. So an output for a three person house might look like Married(30-44)Spouse(30-44)Child(1-4)

我知道我当前的HsHld代码很糟糕，但包含在下面:

I know my current code for HsHld is terrible, but it is included below:

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;

有几项关键的事情需要改变.

There are several key things that need to change.

1. 目前我无法从Rel字段加入亲戚 表工作，所以我正在使用一个名为RAL的Switch功能， 是更好的方法.
2. 为简单起见，我仅包含Rel2& Rel3等，但是在实际代码中，它上升到Rel13！因此，性能问题更加严重.
3. 我想用联接替换这些子查询，但是作为子查询 查看同一张表中的另一条记录，我不确定如何去 关于这个.
4. 我对此非常了解，我知道一些SQL，但是 对于我有限的知识来说，这个问题的复杂性太高了

1. At the moment I can't get a join from the Rel field to the Relatives table to work, so I am using a Switch function called RAL there must be a better way.
2. For simplicity in the post I have only included Rel2 & Rel3 etc but in the actual code it goes up to Rel13! So the problem of performance is even worse.
3. I want to replace these subqueries with joins, but as the subquery looks into another record in the same table I am unsure how to go about this.
4. I'm very out of my depth with this, I know a little SQL but the complexity of this problem is too much for my limited knowledge

推荐答案

第一件事是您有一个关系情况，但是您拥有的表结构使用列来表示关系.这将为您提供表上的R01，R02，R03 ... R13列.不幸的是，由于表结构是重复的非规范化而不是关系式的，因此您将无法显着改变性能.这意味着您的查询将需要所有这些重复的代码，正如您提到的要重复13次.这也意味着您的切换功能可以被联接代替，但再次会被重复13次.

The very first thing is that you have a relational situation but the table structure you have is using columns to represent relationships. This gives you the R01, R02, R03 ... R13 columns on your table. Unfortunately you will not be able to change performance dramatically because your table structure is repetitive denormalized instead of relational. This means that your query will need all this repetitive code, as you mentioned repeating 13 times. That also means that your switch function can be replaced by a join but again will be repeated 13 times.

现在，回到查询中，您在查询上有多个子选择，您需要在FROM子句的左联接上联接相关表，并在选择上使用新的相关别名.现在，您将在下面的示例中看到，对于每个R01，R02字段，您将拥有一个Fam2，Fam3关系，并且需要根据您的情况执行13次此操作，并且对于每个关系，您都需要链接到亲属表(如我确实叫Relat2，Relat3等).现在，如果您可以将数据库结构更改为规范化的结构，则可以真正简化此查询并使用更简单的联接.

Right, now back to your query, you have multiple sub-selects on your query and you need to join the related tables on a left join on the FROM clause and use the new related alias on your select. now you will see on the example below that for each R01, R02 field you will have a Fam2, Fam3 relation and you will need to do this 13 times on your case, and for each one you need to link to the relatives table (as i did called Relat2, Relat3, etc). Now if you can change your database structure for a normalized structure, you could really simplify this query and use much simpler joins.

看看这是否可以帮助您了解这一过程:

See if this one helps you understand the process:

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; 

这篇关于查看同一表Access 2010中的另一条记录时，将SQL子查询转换为联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！