ROW_NUMBER执行内部联接时出现意外结果

Question

我构建了这个SQL请求，

SELECT 
distinct c_users.id,
c_photos.user_id,
c_photos.pic_filename,
c_users.user_first_name,
c_users.user_last_name,
c_users.description,
c_users.curriculum_diploma_1,
ROW_NUMBER() OVER(ORDER BY c_users.id ASC) AS rank
FROM
c_users
INNER JOIN
c_photos
ON
c_users.id=c_photos.user_id
WHERE 
lower(location_town) LIKE '%lille%' 
AND
(c_photos.pic_type='profile' OR c_photos.pic_type='photo')    
LIMIT
9 
;

这是输出，

输出

排名列有一些意想不到的结果，我希望它是 1、2、3 等，而不是 94、84、25 等。我不知道该怎么做。

谢谢

伦纳德

Answer 1

如果您希望按特定顺序显示结果，则需要一个order by。 我建议：

order by c_users.id 

或

order by rank

编辑：

我不确定row_number()如何与distinct一起工作. 我对group by更舒服. 但是，在您的情况下，我怀疑是否需要重复消除(除非用户有两张同名的照片(。

SELECT u.id, p.user_id, p.pic_filename, u.user_first_name, u.user_last_name,
u.description, u.curriculum_diploma_1,
ROW_NUMBER() OVER (ORDER BY u.id ASC) AS rank
FROM c_users u INNER JOIN
c_photos p
ON u.id = p.user_id
WHERE lower(location_town) LIKE '%lille%' AND
p.pic_type in ('profile', 'photo')
LIMIT 9 ;

您可以添加：

GROUP BY u.id, p.user_id, p.pic_filename, u.user_first_name, u.user_last_name,
u.description, u.curriculum_diploma_1 

如有必要。

Answer 2

with CTE as
(
SELECT 
distinct c_users.id,
c_photos.user_id,
c_photos.pic_filename,
c_users.user_first_name,
c_users.user_last_name,
c_users.description,
c_users.curriculum_diploma_1,
ROW_NUMBER() OVER(ORDER BY c_users.id ASC) AS rank
FROM
c_users
INNER JOIN
c_photos
ON
c_users.id=c_photos.user_id
WHERE 
lower(location_town) LIKE '%lille%' 
AND
(c_photos.pic_type='profile' OR c_photos.pic_type='photo')    
)
select *
from CTE
where rank <=9

要么将其打包在 CTE/查询中并使用row_number，要么按新列排序(根据 Gordon 的答案(，以使限制按预期工作。

Answer 3

不同的语句导致了这种情况。你可以试试这个：

SELECT *, ROW_NUMBER() OVER(ORDER BY c_users.id ASC) AS rank from (
SELECT 
distinct c_users.id,
c_photos.user_id,
c_photos.pic_filename,
c_users.user_first_name,
c_users.user_last_name,
c_users.description,
c_users.curriculum_diploma_1
FROM
c_users
INNER JOIN
c_photos
ON
c_users.id=c_photos.user_id
WHERE 
lower(location_town) LIKE '%lille%' 
AND
(c_photos.pic_type='profile' OR c_photos.pic_type='photo')    
) as data
LIMIT
9