问题描述
我试图重现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冻结简单代码和小数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!