有没有一种方法可以找到包含分组数据的TOP X记录? [英] Is there a way to find TOP X records with grouped data?

问题描述

我正在使用Sybase 12.5服务器，并且有一个这样定义的表:

I'm working with a Sybase 12.5 server and I have a table defined as such:

CREATE TABLE SomeTable(
    [GroupID] [int] NOT NULL,
    [DateStamp] [datetime] NOT NULL,
    [SomeName] varchar(100),
    PRIMARY KEY CLUSTERED (GroupID,DateStamp)
)

我希望能够通过[DateStamp]仅按[GroupID]列出最新的X条记录.踢脚是X> 1，因此普通的旧MAX()不会删除它.我以为有一种使用游标执行此操作的奇妙方法，但我想知道是否有没有该方法的简单方法.

I want to be able to list, per [GroupID], only the latest X records by [DateStamp]. The kicker is X > 1, so plain old MAX() won't cut it. I'm assuming there's a wonderfully nasty way to do this with cursors and what-not, but I'm wondering if there is a simpler way without that stuff.

我知道我很想念一些明显的东西，我会踢自己没有得到的东西，但是....我没得到.请帮忙.

I know I'm missing something blatantly obvious and I'm gonna kick myself for not getting it, but .... I'm not getting it. Please help.

是否可以找到TOP X记录，但具有分组数据?

Is there a way to find TOP X records, but with grouped data?

推荐答案

根据联机手册，Sybase 12.5支持WINDOW函数和ROW_NUMBER()，尽管它们的语法与标准SQL略有不同.

According to the online manual, Sybase 12.5 supports WINDOW functions and ROW_NUMBER(), though their syntax differs from standard SQL slightly.

尝试这样的事情:

SELECT SP.*
FROM (
    SELECT *, ROW_NUMBER() OVER (windowA ORDER BY [DateStamp] DESC) AS RowNum
    FROM SomeTable
    WINDOW windowA AS (PARTITION BY [GroupID])
) AS SP
WHERE SP.RowNum <= 3
ORDER BY RowNum DESC;

我没有Sybase的实例，所以我没有对此进行测试.我只是从文档中合成了这个示例.

I don't have an instance of Sybase, so I haven't tested this. I'm just synthesizing this example from the doc.

我弄错了.我正在查看的文档是Sybase SQL Anywhere11.似乎Sybase ASA根本不支持WINDOW子句，即使在最新版本中也是如此.

I made a mistake. The doc I was looking at was Sybase SQL Anywhere 11. It seems that Sybase ASA does not support the WINDOW clause at all, even in the most recent version.

这是另一个可以完成相同任务的查询.您可以使用自联接将SomeTable的每一行与具有相同GroupID和更高的DateStamp的所有行进行匹配.如果以后有三行或更少的行，那么我们就是前三行之一.

Here's another query that could accomplish the same thing. You can use a self-join to match each row of SomeTable to all rows with the same GroupID and a later DateStamp. If there are three or fewer later rows, then we've got one of the top three.

SELECT s1.[GroupID], s1.[Foo], s1.[Bar], s1.[Baz]
FROM SomeTable s1
LEFT OUTER JOIN SomeTable s2
  ON s1.[GroupID] = s2.[GroupID] AND s1.[DateStamp] < s2.[DateStamp]
GROUP BY s1.[GroupID], s1.[Foo], s1.[Bar], s1.[Baz]
HAVING COUNT(*) < 3
ORDER BY s1.[DateStamp] DESC;

请注意，您必须在SELECT列表中列出与在GROUP BY子句中列出的列相同的列.基本上，您希望此查询返回的s1中的所有列.

Note that you must list the same columns in the SELECT list as you list in the GROUP BY clause. Basically, all columns from s1 that you want this query to return.

这篇关于有没有一种方法可以找到包含分组数据的TOP X记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！