sql server语法错误，在子查询附近

I am trying to get the last status a customer had. By browsing examples, I have put together this query, which works:

我在努力获得顾客的最后地位。通过浏览示例，我将这个查询放在一起，它可以工作:

SELECT table.Card, 
    table.Type, 
    table.Date, 
    ROW_NUMBER() OVER   
        (PARTITION by table.CARD ORDER BY table.date DESC, table.TYPE, table.DATE) rn 
 FROM table 
 WHERE table.Type = 'active' or table.Type = 'erased'

From there, I just need those records where rn=1, but as soon as I try to use itas a subquery as shown below, I get a syntaxis error:

从这里开始，我只需要rn=1的那些记录，但是当我尝试使用如下所示的子查询时，我就会得到一个合数错误:

SELECT * FROM (
    SELECT table.Card, 
        table.Type, 
        table.Date, 
        ROW_NUMBER() OVER   
            (PARTITION by table.CARD ORDER BY table.date DESC, table.TYPE, table.DATE) rn 
     FROM table 
     WHERE table.Type = 'active' or table.Type = 'erased'
) WHERE rn=1

It must something simple I am just missing, but I am totally confused. Any help will be appreciated.

一定是我错过了什么简单的东西，但我完全搞不懂。如有任何帮助，我们将不胜感激。

1 个解决方案

#1

You should assign an alias to the INNER select. Try this.

您应该为内部选择分配一个别名。试试这个。

SELECT * FROM (
    SELECT table.Card, 
        table.Type, 
        table.Date, 
        ROW_NUMBER() OVER   
            (PARTITION by table.CARD ORDER BY table.date DESC, table.TYPE, table.DATE) rn 
     FROM table 
     WHERE table.Type = 'active' or table.Type = 'erased'
) foo WHERE rn=1