访问前N个查询，其中N个按组更改

Question

我正在尝试在Microsoft Access中编写前n值查询，其中n可能会按组更改。例如，一个组可能是前3个记录，另一个组可能是前2个记录，而另一个组可能是前4个记录。我有一个带有字段"TopN“的表，其中存储了要为该组显示多少值。

前3个有效查询的示例，我正在尝试用前N替换前3，其中N是DLookup或使用TopN字段中的值的内容：

SELECT Payout.GDate, Payout.[Game Type], Payout.Denom, Payout.Segment, Payout.Manufacturer_Description, Payout.Stand, Payout.Payout
  FROM Payout, PayoutShowSegment
  WHERE (((Payout.Payout) IN (SELECT TOP 3 [Payout] 
                                FROM [Payout] 
                                WHERE [Payout].[Segment]=[PayoutShowSegment].[Segment])))
  ORDER BY Payout.[Game Type], Payout.Denom, Payout.Payout DESC;

Answer 1

几个月前我也遇到过类似的问题。

在处理您的查询时，应该可以使用下面这样的代码：

SELECT      T1.GDate, 
            T1.[Game Type], 
            T1.Denom, 
            T1.Segment, 
            T1.Manufacturer_Description, 
            T1.Stand, 
            T1.Payout
FROM        Payout T1 INNER JOIN Payout T2 ON
                T1.[Game Type] = T2.[Game Type] AND
                T1.Denom = T2.Denom AND
                T1.Segment = T2.Segment AND
                T1.Manufacturer_Description = T2.Manufacturer_Description AND
                T1.Stand = T2.Stand AND
                T1.Payout = T2.Payout AND
                T1.GDate >= T2.GDate
GROUP BY    T1.GDate, 
            T1.[Game Type], 
            T1.Denom, 
            T1.Segment, 
            T1.Manufacturer_Description, 
            T1.Stand, 
            T1.Payout
HAVING      COUNT(*) <= (
                         SELECT TopN
                         FROM   MyOtherTable
                         WHERE  MyOtherTable.IDField = T1.IDField
                        )
ORDER BY    T1.GDate

注意-- join可能会被简化为支付表中的PK。

欲了解更多信息，请访问以下网站：http://www.sql-ex.com/help/select16.php