SQL查询选择每组前5名，从而导致Access冻结简单代码和小数据 [英] SQL query select top 5 per group leading Access to freeze on simple code and small data

问题描述

我试图重现SQL组中的select top n，但是即使只有少量数据，访问也会冻结(需要几分钟的时间).

I was trying to reproduce the select top n from group SQL but access freezes (takes minutes to complete) even on small set of data.

date         ret1         anndate

26-Jul-13   0.999214    25-Jul-13
29-Jul-13   0.982684    25-Jul-13
30-Jul-13   0.947938    25-Jul-13
31-Jul-13   1.024081    25-Jul-13
01-Aug-13   1.017739    25-Jul-13
02-Aug-13   1.001621    25-Jul-13
10-Dec-13   0.965405    09-Dec-13
11-Dec-13   1.009705    09-Dec-13
12-Dec-13   1.025508    09-Dec-13
13-Dec-13   0.994232    09-Dec-13
16-Dec-13   1.009065    09-Dec-13
17-Dec-13   0.984549    09-Dec-13
18-Dec-13   1.007299    09-Dec-13

我想要实现的是每个日期"有以下五个行.例如，对于2013年7月25日的日期，我想要前5行.对于2013年12月9日的日期，我希望接下来的5行在2013年12月16日结束.

What I wanted to achieve is to have the five following rows per each 'anndate'. for example, for anndate 25 july 2013, I want the first 5 rows. for anndate 09-dec-2013, I want 5 following rows ended on 16-dec-2013.

我的代码是:

SELECT *
FROM com
WHERE date in 
(select top 5 date from com where com.date>com.anndate);

但它会导致崩溃(更正:应该是需要几分钟才能完成" ).任何人都可以帮助指出哪里出了问题?

but it causes access to crash(correction: should be 'take minutes to complete'). Anyone can help point out where is wrong?

更新:

我将列名[date]更改为[cdate]，但仍然无法正常工作.

I changed the column name [date] to [cdate], it still doesnt work.

我运行了如下所示的简单操作，但仍然需要几分钟才能完成....

SELECT *
FROM com
WHERE cdate in (select cdate from com )

推荐答案

我将您的示例数据存储在一个表中，同时以date和anndate作为日期/时间数据类型.然后，我使用从相关子查询生成的anndate_rank构建查询. anndate_rank的目的是每个anndate组中的排名编号.

I stored your sample data in a table with both date and anndate as Date/Time data type. Then I built a query with anndate_rank generated from a correlated subquery. The purpose of anndate_rank is a rank number within each anndate group.

然后，整个查询成为一个新查询的子查询，该查询选择anndate_rank< =最高限制---我选择了前2名而不是前5名.

Then that entire query became a subquery in a new one which selects anndate_rank <= the top limit --- I chose top 2 instead of top 5.

这是查询的结果集，查询如下:

This is the result set from the query, and the query is below:

date       ret1     anndate   anndate_rank
---------- -------- --------- ------------
7/26/2013  0.999214 7/25/2013            1
7/29/2013  0.982684 7/25/2013            2
12/10/2013 0.965405 12/9/2013            1
12/11/2013 1.009705 12/9/2013            2

SELECT
    sub.date,
    sub.ret1,
    sub.anndate,
    sub.anndate_rank
FROM
    (
        SELECT
            c.date,
            c.ret1,
            c.anndate,
            (
                SELECT Count(*)
                FROM com AS c2
                WHERE
                        c2.anndate=c.anndate
                    AND c2.date<=c.date
            ) AS anndate_rank
        FROM com AS c
    ) AS sub
WHERE sub.anndate_rank<=2;

请注意，此方法假定在任何anndate组中都没有重复的date值，如样本数据中一样.如果您的真实数据确实包含重复的date/anndate对，则此查询将不会为您提供所需的结果.

Note this approach assumes no repeated date values within any anndate group, as in your sample data. If your real data does include duplicate date / anndate pairs, this query will not give you the results you want.

这篇关于SQL查询选择每组前5名，从而导致Access冻结简单代码和小数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！