sql - 如何在ms access sql中结合top和count，以便检索除第一条以外的每条记录？

Question

如何在ms access sql中结合top和count，以便检索除第一条以外的每条记录？即排除第一条记录，但不知道存在的记录数。

我想sql会是这样的：

SELECT TOP 

(SELECT COUNT(*) 
FROM Company LEFT JOIN CompanySecretary ON Company.[Company Number] = CompanySecretary.[Company Number]
WHERE Company.[Company Number]=[Forms]![Company]![Company Number]
- 1)

Company.[Company Number], CompanySecretary.[Name]
FROM Company LEFT JOIN CompanySecretary ON Company.[Company Number] = CompanySecretary.[Company Number]
WHERE Company.[Company Number]=[Forms]![Company]![Company Number]
ORDER BY Company.[Company Number] ASC, CompanySecretary.[Name] ASC;

例如

Company.[Company Number] | CompanySecretary.[Name] 
---------------------------------------------------
                     002 | Mark 
                     002 | Paul 
                     002 | William 

预期的结果是：

Company.[Company Number] | CompanySecretary.[Name] 
---------------------------------------------------
                     002 | Paul 
                     002 | William 

太感谢了。感激的。

标签： sqlms-access

Answer 1

假设同一公司中没有 2 名同名秘书

SELECT TOP 1 Company.[Company Number], CompanySecretary.[Name]
 FROM Company LEFT JOIN CompanySecretary 
              ON Company.[Company Number] = CompanySecretary.[Company Number]
 WHERE Company.[Company Number]=[Forms]![Company]![Company Number]
 ORDER BY Company.[Company Number] ASC, CompanySecretary.[Name] ASC

这些是要从结果集中排除的值对

让我们像qTop1在 Access 查询中一样创建它

现在所需的查询将是

SELECT Company.[Company Number], CompanySecretary.[Name]
 FROM Company LEFT JOIN CompanySecretary 
               ON Company.[Company Number] = CompanySecretary.[Company Number]
 WHERE Company.[Company Number]=[Forms]![Company]![Company Number]
       AND NOT EXISTS (SELECT 1 FROM qTop1 
                        WHERE qTop1.[Company Number] = Company.[Company Number]
                               AND qTop1.Name = CompanySecretary.[Name])    
 ORDER BY Company.[Company Number] ASC, CompanySecretary.[Name] ASC