按分区在visual foxpro中的排名

Question

我有下表，看上去

+ --- + --- +
| AID | Tag |
+ --- + --- +
| 1   | 1   |
| 2   | 2   |
| 2   | 3   |
| 2   | 820 |
| 2   | 821 |
| 3   | 2   |
| 4   | 5   |
| 4   | 18  |
| 4   | 2744|
| 4   | 2745|
+ --- + --- +

编写以下Server 2008代码时

select AID,
       Tag,
       RANK() over (partition by AID order by Tag asc) as rank
from My_Table

它产生以下结果

+ --- + --- + ---- +
| AID | Tag | Rank |
+ --- + --- + ---- +
| 1   | 1   | 1    |
| 2   | 2   | 1    |
| 2   | 3   | 2    |
| 2   | 820 | 3    |
| 2   | 821 | 4    |
| 3   | 2   | 1    |
| 4   | 5   | 1    | 
| 4   | 18  | 2    |
| 4   | 2744| 3    |
| 4   | 2745| 4    |
+ --- + --- + ---- +

这正是我想要的。

现在，我想用VisualSql9sql编写同样的内容。我尝试使用recno()作为演示的这里；这对我的记录进行了编号，但似乎不支持分区能力，相关的子查询在VFP 9 SQL中似乎不受支持。我知道我可以用光标和扫描来做这件事，但我不想那样做。有什么建议吗？

Answer 1

对于任何想知道的人来说，我找到了答案。VisualSQL9.0支持以下FoxPro代码，并将执行我们希望的操作。

select  t1.aid, ;
        t1.tag, ;
        count(*) as rank ;
    from my_table t1 ;
    inner join my_table t2 ;
        on t2.aid = t1.aid ;
        and t2.tag <= t1.tag ;
    group by t1.aid, t1.tag

为了了解原因，让我们通过省略聚合并包含来自t2的标记来更仔细地查看内部连接。

select  t1.aid, ;
        t1.tag, ;
        t2.tag ;
    from my_table t1 ;
    inner join my_table t2 ;
        on t2.aid = t1.aid ;
        and t2.tag <= t1.tag ;
    order by t1.aid, t1.tag

此代码生成如下所示的表

+ --- + ---- + ---- +
| AID | Tag1 | Tag2 |
+ --- + ---- + ---- +
| 1   | 1    | 1    |
| 2   | 2    | 2    |
| 2   | 3    | 2    |
| 2   | 3    | 3    |
| 2   | 820  | 2    |
| 2   | 820  | 3    |
| 2   | 820  | 820  |
| 2   | 821  | 2    |
| 2   | 821  | 3    |
| 2   | 821  | 820  |
| 2   | 821  | 821  |
| 3   | 2    | 2    |
| 4   | 5    | 5    | 
| 4   | 18   | 5    |
| 4   | 18   | 18   |
| 4   | 2744 | 5    |
| 4   | 2744 | 18   |
| 4   | 2744 | 2744 |
| 4   | 2745 | 5    |
| 4   | 2745 | 18   |
| 4   | 2745 | 2744 |
| 4   | 2745 | 2745 |
+ --- + ---- + ---- +

我们实际上并不关心Tag2中的数据，但现在我们可以清楚地看到，排名是Tag1和Tag1分组的计数。

Answer 2

在VFP中没有秩()函数。但是，您可以通过多种方式实现相同的效果。一种方法是简单的scan...endscan传递更新排名值，如下例所示：

*** Sample Data
    Create Cursor mytable ( AID Int, Tag Int)
    Insert Into mytable Values (1,1   )
    Insert Into mytable Values (2,2   )
    Insert Into mytable Values (2,3   )
    Insert Into mytable Values (2,820 )
    Insert Into mytable Values (2,821 )
    Insert Into mytable Values (3,2   )
    Insert Into mytable Values (4,5   )
    Insert Into mytable Values (4,18  )
    Insert Into mytable Values (4,2744)
    Insert Into mytable Values (4,2745)
*** Sample Data

Select AID, Tag, Cast(0 As Int) As rank ;
    from mytable ;
    order By AID, Tag ;
    into Cursor crsRanked ;
    readwrite

Scan
    AID = AID
    rcno = Recno()
    Replace rank With Recno()-m.rcno+1 While AID = m.AID
    Skip -1
Endscan

Locate
Browse

编辑:昨天我忽略了MS的秩()函数是如何工作的，对不起。这里有一个类似于MS的Rank()、Dense_Rank()、Row_number()的程序：

Create Cursor mytable ( AID Int, Tag Int)
Insert Into mytable Values (1,1   )
Insert Into mytable Values (2,2   )
Insert Into mytable Values (2,3   )
Insert Into mytable Values (2,820 )
Insert Into mytable Values (2,821 )
Insert Into mytable Values (3,2   )
Insert Into mytable Values (4,5   )
Insert Into mytable Values (4,18  )
Insert Into mytable Values (4,18  )
Insert Into mytable Values (4,18  )
Insert Into mytable Values (4,2744)
Insert Into mytable Values (4,2745)

Select AID, Tag, ;
    Cast(0 As Int) As rownum, ;
    Cast(0 As Int) As rank, ;
    Cast(0 As Int) As denserank ;
    from mytable ;
    order By AID, Tag ;
    into Cursor crsRanked ;
    readwrite

Local AID,rank,denserank,nextrank,rcno
Scan
    AID = AID
    rank = 0
    nextrank = 0
    denserank = 0
    rcno = Recno()
    Scan While m.AID = AID
        Tag = Tag
        rank = nextrank + 1
        denserank = m.denserank + 1
        Replace ;
            rank With m.rank, ;
            denserank With m.denserank, ;
            rownum With Recno()-m.rcno+1 ;
            While AID = m.AID And Tag = m.Tag
        nextrank = m.nextrank + _Tally
        Skip -1
    Endscan
    Skip -1
Endscan

Locate
Browse