table1 & table2:
表1和表2 http://aftabfarda.parsfile.com/1.png
SELECT *
FROM (SELECT DISTINCT dbo.tb1.ID, dbo.tb1.name, ROW_NUMBER() OVER (ORDER BY tb1.id DESC) AS row
FROM dbo.tb1 INNER JOIN
dbo.tb2 ON dbo.tb1.ID = dbo.tb2.id_tb1) AS a
WHERE row BETWEEN 1 AND 7
ORDER BY id DESC
结果:
结果。。。http://aftabfarda.parsfile.com/3.png
(id 11 重复 3 次)
我怎么能有这个输出:
ID name row
-- ------ ---
11 user11 1
10 user10 2
9 user9 3
8 user8 4
7 user7 5
6 user6 6
5 user5 7
您可以在使用子查询row_number
之前应用 distinct
:
select *
from (
select row_number() over (order by tbl.id desc) as row
, *
from (
select distinct t1.ID
, tb1.name
from dbo.tb1 as t1
join dbo.tb2 as t2
on t1.ID = t2.id_tb1
) as sub_dist
) as sub_with_rn
where row between 1 and 7
@Andomar的建议,您可以使用DENSE_RANK
而不是ROW_NUMBER
并首先对行进行排名(在子查询中),然后应用DISTINCT
(在外部查询中):
SELECT DISTINCT
ID,
name,
row
FROM (
SELECT
t1.ID,
t1.name,
DENSE_RANK() OVER (ORDER BY t1.ID DESC) AS row
FROM dbo.tb1 t1
INNER JOIN dbo.tb2 t2 ON t1.ID = t2.id_tb1
) AS a
WHERE row BETWEEN 1 AND 7
ORDER BY ID DESC
相似,但不完全相同,尽管两者都可能归结为相同的查询计划,但我只是不确定。我认为值得测试。
当然,您也可以尝试半连接而不是正确的连接,以 IN
或 EXISTS
的形式,首先防止重复:
SELECT
ID,
name,
row
FROM (
SELECT
ID,
name,
ROW_NUMBER() OVER (ORDER BY ID DESC) AS row
FROM dbo.tb1
WHERE ID IN (SELECT id_tb1 FROM dbo.tb2)
/* Or:
WHERE EXISTS (
SELECT *
FROM dbo.tb2
WHERE id_tb1 = dbo.tb1.ID
)
*/
) AS a
WHERE row BETWEEN 1 AND 7
ORDER BY ID DESC