女士访问等级并匹配两个查询 [英] Ms Access Rank and match two querys

问题描述

我已经被这个问题困扰了一段时间了，但是还没解决好.在这里:

我在Acces数据库中有一些表和一些查询.我有一个查询来选择某些字段并按其频率排序.这是针对两个表完成的，给了我两个等级.看起来像这样(并按DESC排序，因此频率"位于最前面):

Table 1                    Table 2
Value1   Frequency1         Value2 Frequency2

Table2.Value2是Table1.Value1的子集，所以我想匹配Value2和Value1并添加一列，显示Value2在Table2上的位置(排名)，因此我可以比较它在Table1和Table2排名上的位置.会像这样:

Table3
Value1 Frequency1 Frequency2 PositionTable2

我已经搜索了如何返回字段的行号(无法使其工作)，我可以将两个表放在一起并按Value进行匹配，但无法获得所需的结果. /p>

我对SQL的了解有限，而且我根本不了解VB，因此请保持友善:) 预先感谢

修改 示例:

Table 1                    Table 2
Name    Frequency    Name    Frequency
Mary       5         Paul     2
John       4         John     1
Paul       3

我想要的输出:

Table 3               
Name    Frequency    Frequency2    RankIn2
Mary       5                                  //doesn't appear in table2, freq=null rankin2=null
John       4            1            2            //second line of table2
Paul       3            2            1            //first line in table2

解决方案

您可以分两个步骤进行操作:

第1步-创建一个查询(例如，名为具有排名的表2")来计算表2的排名.SQL可能看起来像这样:

SELECT
    [Table 2].[Name],
    [Table 2].[Frequency],
    Count(*) AS [Rank]
FROM
    [Table 2],
    [Table 2] AS [Self]
WHERE
    [Self].[Frequency]>=[Table 2].[Frequency]
GROUP BY
    [Table 2].[Name],
    [Table 2].[Frequency];

如果表2中有关系"(即，具有相同频率的不同名称)，则此查询将为两者分配相同的等级.如果您不希望这样做，请更改WHERE子句以指定要打破平局的方式.例如，如果出现平局，则WHERE子句...

WHERE
    [Self].[Frequency]>[Table 2].[Frequency]
    OR
    ([Self].[Frequency]=[Table 2].[Frequency] AND [Self].[Name]<=[Table 2].[Name])

...将把编号较低的行分配给字母中第一个出现的名称.

第2步-创建另一个将第一个查询联接到表1的查询.SQL可能看起来像这样:

SELECT
    [Table 1].[Name], 
    [Table 1].[Frequency], 
    [Table 2 with Rank].[Frequency] AS [Frequency2], 
    [Table 2 with Rank].Rank AS [RankIn2]
FROM 
    [Table 1] LEFT JOIN [Table 2 with Rank] 
        ON [Table 1].[Name] = [Table 2 with Rank].[Name]
ORDER BY
    [Table 1].[Frequency] DESC;

I've been stuck with this problems for a while, and couldn't get it right yet. Here it is:

I have some tables in my Acces database and some querys. I have a query to select some fields and order by, say, their frequency. This is done for two tables, giving me two ranks. Looks like this (and are sorted DESC, so the higher Frequency is on top):

Table 1                    Table 2
Value1   Frequency1         Value2 Frequency2

Table2.Value2 is a subset of Table1.Value1, so I want to match Value2 and Value1 plus Add a Column showing Value2 position (rank) on Table2, so I can compare it's position on Table1 and Table2 ranks.So I'll have something like:

Table3
Value1 Frequency1 Frequency2 PositionTable2

I've searched how to return the line number of a field (couldn't get it to work), and I can put the two tables together and match by Value, but can't get the result I need.

My knowledge of SQL is limited, and I don't understand VB at all, so please be nice :) Thanks in advance

Edit Example:

Table 1                    Table 2
Name    Frequency    Name    Frequency
Mary       5         Paul     2
John       4         John     1
Paul       3

Output I want:

Table 3               
Name    Frequency    Frequency2    RankIn2
Mary       5                                  //doesn't appear in table2, freq=null rankin2=null
John       4            1            2            //second line of table2
Paul       3            2            1            //first line in table2

解决方案

You might do this in two steps:

Step 1 -- Create a query (say, named "Table 2 with Rank") that calculates the rank for Table 2. The SQL might look something like this:

SELECT
    [Table 2].[Name],
    [Table 2].[Frequency],
    Count(*) AS [Rank]
FROM
    [Table 2],
    [Table 2] AS [Self]
WHERE
    [Self].[Frequency]>=[Table 2].[Frequency]
GROUP BY
    [Table 2].[Name],
    [Table 2].[Frequency];

If there are "ties" in Table 2 (that is, different names with the same frequency), this query will assign the same rank to both. If you don't want this, change the WHERE clause to specify how you want to break ties. For example, in the event of a tie, the WHERE clause...

WHERE
    [Self].[Frequency]>[Table 2].[Frequency]
    OR
    ([Self].[Frequency]=[Table 2].[Frequency] AND [Self].[Name]<=[Table 2].[Name])

...will assign the lower numbered rank to the name that comes first the in the alphabet.

Step 2 -- Create another query that joins the first query to Table 1. The SQL might look something like this:

SELECT
    [Table 1].[Name], 
    [Table 1].[Frequency], 
    [Table 2 with Rank].[Frequency] AS [Frequency2], 
    [Table 2 with Rank].Rank AS [RankIn2]
FROM 
    [Table 1] LEFT JOIN [Table 2 with Rank] 
        ON [Table 1].[Name] = [Table 2 with Rank].[Name]
ORDER BY
    [Table 1].[Frequency] DESC;

这篇关于女士访问等级并匹配两个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！