每组前3名，包括0 [英] Top 3 per group including 0

问题描述

我有一张表，我希望从Access 2010开始每周从ErrorMargin返回前3条记录.

I have a table for which I want to return the top 3 records by ErrorMargin per week commencing in Access 2010.

我遇到的问题是0个值被忽略，并且在平局的情况下我只希望看到1条记录，其中平局将使记录总数超过3条.

The problem I'm having is that 0 values are being ignored and I'd like to see just 1 record in the case of ties where the tie would take the total number of records over 3.

我的桌子是:
NB:VBA位于帖子底部，用于创建表格.

The table I have is:
NB: VBA at bottom of post to create the table.

 TMID   WeekCommencing  ErrorMargin
    1   05-Oct-15   0
    1   12-Oct-15   2
    3   05-Oct-15   1
    3   12-Oct-15   1
    8   12-Oct-15   2
    9   05-Oct-15   0.333333333
    9   12-Oct-15   4
    12  05-Oct-15   0
    12  12-Oct-15   1.5

我目前使用的SQL是:

The SQL I have at the moment is:

SELECT      T1.TMID,
            T1.WeekCommencing,
            T1.ErrorMargin,
            COUNT(*)
FROM        qry_REP_ErrorMargin T1 INNER JOIN qry_REP_ErrorMargin T2 ON
                T1.ErrorMargin <= T2.ErrorMargin AND
                T1.WeekCommencing = T2.WeekCommencing
GROUP BY    T1.TMID,
            T1.WeekCommencing,
            T1.ErrorMargin
HAVING      COUNT(*) <= 3
ORDER BY    T1.WeekCommencing,
            T1.ErrorMargin

这将返回下表，该表仅显示2015年5月10日的两条记录-还有另外两条记录的ErrorMargin为0，我希望它也返回其中之一.没关系. TMID和WeekCommencing字段将构成表的关键字段.

This returns the following table, which is only showing two records for the 5/10/2015 - there are two further records with a 0 ErrorMargin and I'd like it to return one of those as well. It doesn't matter which. The TMID and WeekCommencing fields would make up the key field for the table.

TMID    WeekCommencing  ErrorMargin Expr1003
9       05/10/2015      0.33        2
3       05/10/2015      1           1
1       12/10/2015      2           3
8       12/10/2015      2           3
9       12/10/2015      4           1

我在尝试其他解决方案，但是还没有设法使任何事情起作用-

I've had a play around with other solutions, but haven't managed to get anything to work yet - MS Access Select top n query grouped by multiple fields

创建表的VBA代码:

Sub Create()

    Dim db As DAO.Database
    Set db = CurrentDb

    db.Execute "CREATE TABLE qry_REP_ErrorMargin" & _
        "(TMID LONG, WeekCommencing DATE, ErrorMargin Double)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (1,42282,0)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (1,42289,2)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (3,42282,1)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (3,42289,1)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (8,42289,2)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (9,42282,0.333333333333333)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (9,42289,4)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (12,42282,0)"
    db.Execute "INSERT INTO qry_REP_ErrorMargin" & _
        "(TMID, WeekCommencing, ErrorMargin) VALUES (12,42289,1.5)"

End Sub

推荐答案

以下内容可能会满足您的要求:

The following may do what you want:

SELECT em.*
FROM qry_REP_ErrorMargin as em
WHERE em.TMID IN (SELECT TOP 3 TMID
                  FROM qry_REP_ErrorMargin as em2
                  WHERE em2.WeekCommencing = em.WeekCommencing
                  ORDER BY em2.ErrorMargin
                 );

请注意，在平局的情况下，MS Access可能返回三行以上.如果您不想重复，则在ORDER BY中包含一个id列以防止联系:

Note that in the case of ties, MS Access might return more than three rows. It you do not want duplicates, then include an id column in the ORDER BY to prevent ties:

ORDER BY em2.ErrorMargin, em2.TMID

这篇关于每组前3名，包括0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！