Access别名中的嵌套子查询导致“输入参数值" [英] Nested subquery in Access alias causing "enter parameter value"

问题描述

我正在使用 Access(我通常使用 SQL Server)做一些小工作，并且我在下面的语句中获得了 Night.NightId 的输入参数值"，其中有一个子查询一个子查询.如果我没有将它嵌套两层深，我希望它会起作用，但我想不出解决方法(欢迎查询想法).

I'm using Access (I normally use SQL Server) for a little job, and I'm getting "enter parameter value" for Night.NightId in the statement below that has a subquery within a subquery. I expect it would work if I wasn't nesting it two levels deep, but I can't think of a way around it (query ideas welcome).

场景非常简单，有一个 Night 表，与 Score 表具有一对多的关系 - 每个晚上通常有 10 个分数.每个分数都有一个位域 IsDouble，通常对于两个分数来说 true.

The scenario is pretty simple, there's a Night table with a one-to-many relationship to a Score table - each night normally has 10 scores. Each score has a bit field IsDouble which is normally true for two of the scores.

我想列出所有的夜晚，每个夜晚旁边都有一个数字，代表前 2 个分数中有多少被标记为 IsDouble(可能是 0、1 或 2).

I want to list all of the nights, with a number next to each representing how many of the top 2 scores were marked IsDouble (would be 0, 1 or 2).

这是 SQL，我尝试了很多向列和表添加别名的组合，但为了简单起见，我在下面将它们取出:

Here's the SQL, I've tried lots of combinations of adding aliases to the column and the tables, but I've taken them out for simplicity below:

select Night.*
    , 
    ( select sum(IIF(IsDouble,1,0)) from 
            (SELECT top 2 * from Score where NightId=Night.NightId order by Score desc, IsDouble asc, ID)
    ) as TopTwoMarkedAsDoubles
    from Night

推荐答案

这有点推测.但是，某些数据库在多重嵌套子查询中存在相关条件问题.MS Access 可能有这个问题.

This is a bit of speculation. However, some databases have issues with correlation conditions in multiply nested subqueries. MS Access might have this problem.

如果是这样，您可以通过使用聚合与选择前两个值的 where 子句来解决此问题:

If so, you can solve this by using aggregation with a where clause that chooses the top two values:

select s.nightid,
       sum(IIF(IsDouble, 1, 0)) as TopTwoMarkedAsDoubles
from Score as s
where s.id in (select top 2 s2.id
               from score as s2
               where s2.nightid = s.nightid
               order by s2.score desc, s2.IsDouble asc, s2.id
              )
group by s.nightid;

如果这可行，只需重新加入 Night 以获取其他列.

If this works, it is a simply matter to join Night back in to get the additional columns.

这篇关于Access别名中的嵌套子查询导致“输入参数值"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！