尝试让SELECT TOP与ACCESS中的参数一起使用 [英] Trying to Get SELECT TOP to work with Parameter in ACCESS

问题描述

这是基于前几天我得到的一些代码(感谢peterm).我现在尝试在查询计算后选择结果的TOP X数. X取值范围是1到8，具体取决于每个玩家的结果数.

This is building on some code I got the other day (thanks to peterm). I am now trying to select the TOP X number of results after calculations on the query. The X can range from 1 to 8 depending on the number of results per player.

这是我的代码，但是尝试运行它时出现语法错误.

This is the code I have but I get a syntax error when I try to run it.

SELECT
  PlayerID
, RoundID
, PlayedTo
, (SELECT Count(PlayerID) FROM PlayedToCalcs) AS C
, iif(
    C <= 6
  , 1
  , iif(
      C <= 8
    , 2
    , (
        iif(
          C <= 10
        , 3
        , (
            iif(
              C <= 12
            , 4
            , (
                iif(
                  C <= 14
                , 5
                , (
                    iif(
                      C <= 16
                    , 6
                    , (
                        iif(
                          C <= 18
                        , 7
                        , (iif(C <= 20, 8, 999))
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  ) AS X
FROM PlayedToCalcs AS s
WHERE PlayedTo IN (
  SELECT TOP (X) PlayedTo
  FROM PlayedToCalcs
  WHERE PlayerID = s.PlayerID
  ORDER BY PlayedTo DESC, RoundID DESC
)
ORDER BY PlayerID, PlayedTo DESC, RoundID DESC;

这里是链接 http://sqlfiddle.com/#!3/a726c/4 以及一小部分我要在上面使用的数据.

Here is a link http://sqlfiddle.com/#!3/a726c/4 with a small sample of the data I'm trying to use it on.

推荐答案

Access db引擎不允许您为SELECT TOP使用参数.您必须在SQL语句中包含文字值.

The Access db engine does not allow you to use a parameter for SELECT TOP. You must include a literal value in the SQL statement.

例如，此查询正常工作.

For example this query works correctly.

SELECT TOP 2 *
FROM tblFoo
ORDER BY id DESC;

但是尝试替换参数 how_many 会触发错误3141，" SELECT语句包含一个保留字或一个拼写错误或丢失的参数名称，或者标点符号不正确. "

But attempting to substitute a parameter, how_many, triggers error 3141, "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

SELECT TOP how_many *
FROM tblFoo
ORDER BY id DESC;

这篇关于尝试让SELECT TOP与ACCESS中的参数一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！