复杂的MS Access Greatest-N-Per-Group问题

Question

我想将以下查询合并为一个，其中

• scouting.jumpGate是整数，
• scouting.astroLoc是一个字符串，
• scouting.ownerguild是一个字符串，并且
• scouting.galaxy是一个整数

交叉链接到另一个表（也是我的GROUP）：

    Select TOP 3 
        scouting.jumpGate, 
        scouting.astroLoc, 
        scouting.ownerGuild, 
        scouting.Galaxy 
FROM    scouting 
WHERE   scouting.Galaxy = 1 
AND     scouting.ownerGuild = 'TEST' 
ORDER BY    scouting.jumpGate DESC, 
            scouting.astroloc DESC;

和

    SELECT TOP 3 
        scouting.jumpGate, 
        scouting.astroLoc, 
        scouting.ownerGuild, 
        scouting.Galaxy 
FROM    scouting 
WHERE   scouting.Galaxy = 2 
AND     scouting.ownerGuild = 'TEST' 
ORDER BY    scouting.jumpGate DESC, 
            scouting.astroloc DESC;

和

    SELECT TOP 3 
        scouting.jumpGate, 
        scouting.astroLoc, 
        scouting.ownerGuild, 
        scouting.Galaxy 
FROM    scouting 
WHERE   scouting.Galaxy = 3 
AND     scouting.ownerGuild = 'TEST' 
ORDER BY    scouting.jumpGate DESC, 
            scouting.astroloc DESC;

持续到

    SELECT TOP 3 
        scouting.jumpGate, 
        scouting.astroLoc, 
        scouting.ownerGuild, 
        scouting.Galaxy 
FROM    scouting 
WHERE   scouting.Galaxy = 79 
AND     scouting.ownerGuild = 'TEST' 
ORDER BY    scouting.jumpGate DESC, 
            scouting.astroloc DESC;

在Microsoft网站上阅读此“每组最大N”问题后生成的代码如下：

Select  scouting.astroLoc, 
        scouting.galaxy, 
        scouting.jumpGate, 
        scouting.ownerGuild 
From    galaxy Inner Join 
        scouting On galaxy.[galaxy_ID] = scouting.galaxy 
Where   scouting.ownerGuild = 'SWARM' 
AND     (scouting.jumpGate) In (Select Top 3 scouting.jumpGate From scouting Where scouting.galaxy = galaxy.[galaxy_ID] Order By scouting.jumpGate Desc) 
Order By    scouting.astroLoc Desc, 
            scouting.jumpGate Desc

基本上，这与我想要的非常接近。一切似乎都正常。但是，即使在盯着数据看之后，某些GROUPS仍未在输出中表示，每个组都有一条满足查询约束的记录。顺便说一句，如果我删除了scouting.ownerGuild=’SWARM’约束，它会很好地工作（但是我需要这个约束）。

Answer 1

怎么样：

Select  scouting.astroLoc, 
        scouting.galaxy, 
        scouting.jumpGate, 
        scouting.ownerGuild 
From    galaxy Inner Join 
        scouting On galaxy.[galaxy_ID] = scouting.galaxy 
WHERE   (scouting.ID) In (
             Select Top 3 scouting.ID
             From scouting 
             Where scouting.galaxy = galaxy.[galaxy_ID] 
             And scouting.ownerGuild = 'SWARM' 
             Order By scouting.jumpGate Desc) 
Order By    scouting.astroLoc Desc, 
                scouting.jumpGate Desc

否则，似乎前三名可能会包含一些

  where scouting.ownerGuild <> 'SWARM'