sql - 使用 SQL 获取分页的不同记录(非重复)

Question

按照@mdb 的回答申请 pagination using SQL SERVER ，当主表连接到其他表以形成一对多关系时，我发现很难检索不同的记录，即一个人有很多地址。

用例，假设我想检索所有在纽约有地址的人给定的表 #temp_person和 #temp_addresses ，我会加入他们 PersonID和 OwnerID .

当一个人有多个地址时，就会出现问题，结果集包含重复的记录。

为了更清楚，这是一个带有数据的示例查询:

样本数据:

create table #temp_person (
    PersonID int not null,
    FullName varchar(max) not null
)

create table #temp_addresses(
    AddressID int identity not null,
    OwnerID int not null,
    Address1 varchar(max),
    City varchar(max)
)

insert into #temp_person
values 
    (1, 'Sample One'),
    (2, 'Sample Two'),
    (3, 'Sample Three')

insert into #temp_addresses (OwnerID,  Address1, City)
values
(1, 'Somewhere East Of', 'New York'),
(1, 'Somewhere West Of', 'New York'),
(2, 'blah blah blah', 'Atlantis'),
(2, 'Address2 Of Sample Two', 'New York'),
(2, 'Address3 Of Sample Two', 'Nowhere City'),
(3, 'Address1 Of Sample Three', 'New York'),
(3, 'Address2 Of Sample Three', 'Seattle')

--drop table #temp_addresses, #temp_person

分页查询:

 SELECT
    (
        CAST( RowNum as varchar(MAX) ) 
        + '/' 
        + CAST(TotalCount as varchar(MAX))
     ) as ResultPosition
    , PersonID
    , FullName
FROM (
SELECT DISTINCT
        ROW_NUMBER() OVER(ORDER BY p.FullName ASC) as RowNum 
        , p.PersonID
        , p.FullName
        , Count(1) OVER() as TotalCount
    FROM #temp_person p
    LEFT JOIN #temp_addresses a
        ON p.PersonID = a.OwnerID
    WHERE City = 'New York'
) as RowConstrainedResult
WHERE RowNum > 0 AND RowNum <= 3
ORDER BY RowNum

预期结果:

ResultPosition  PersonID    FullName
   1/3           1         Sample One
   2/3           2         Sample Two
   3/3           3        Sample Three

实际结果:

ResultPosition  PersonID    FullName
   1/4            1        Sample One
   2/4            1        Sample One
   3/4            3       Sample Three

如您所见，由于与 #temp_addresses 的连接，内部查询返回了多条记录。 .

有没有办法我们只能通过 PersonID 返回唯一记录?

更新:

实际用例是“高级搜索”功能，用户可以使用不同的过滤器进行搜索，即姓名、名字、姓氏、出生日期、地址等。 <WHERE_CLAUSE>和 <JOIN_STATEMENTS>在查询中动态添加所以 GROUP BY在这里不适用。

另外，请解决此问题的“分页”方案。也就是说，我只想检索 N来自 Start 的结果数量同时还检索结果的总数，就好像它们没有分页一样。即，我只检索了 500 个结果中的 25 行。

Answer 1

只需按 PersonID 分组即可并且不需要使用 subquery

SELECT 
         cast(row_number() over (order by (select 1)) as varchar(max)) +'/'+
         cast(Count(1) OVER() as varchar(max)) ResultPosition,  
         p.PersonID,
         max(p.FullName) FullName
FROM #temp_person p
LEFT JOIN #temp_addresses a ON p.PersonID = a.OwnerID
WHERE City = 'New York'
group by p.PersonID

编辑 : 我会用 CTE用于分页

;with cte as
(
    SELECT 
         row_number() over(order by (select 1)) rn,
         cast(row_number() over (order by (select 1)) as varchar(max)) +'/'+
         cast(Count(1) OVER() as varchar(max)) ResultPosition,  
         p.PersonID,
         max(p.FullName) FullName
    FROM #temp_person p
    LEFT JOIN #temp_addresses a ON p.PersonID = a.OwnerID
    WHERE City = 'New York'
    group by p.PersonID
) 
select * from cte 
where rn > 0  and rn <= 2

结果:

ResultPosition  PersonID    FullName
1/3             1           Sample One
2/3             2           Sample Two
3/3             3           Sample Three