每个字段的限制

Question

假设我们有这样一个sql查询，并从另一个表中加入了数据。

SELECT 
    pr.num, pr.resort_id, p.src_mask 
FROM 
    rutraveler.rt_photo_resort AS pr 
    JOIN rutraveler.rt_photo AS p ON pr.photo_id = p.id 
WHERE pr.resort_id = '612' AND p.src_mask is not null 
ORDER BY num 
LIMIT 30 

到目前为止，我们必须对几个resort_id做几个查询。
如何更改查询，以便我们只有一个查询（WHERE与结果resort_id在（612，333，111）不按每个resort_id 30余项？

Answer 1

使用ROW_NUMBER每resort_id计算行数。

SELECT resort_id, num, resort_id, src_mask 
FROM 
(
    SELECT 
    pr.resort_id, pr.num, pr.resort_id, p.src_mask, 
    ROW_NUMBER() OVER (PARTITION BY pr.resort_id ORDER BY num) AS rn 
    FROM 
    rutraveler.rt_photo_resort AS pr 
    JOIN rutraveler.rt_photo AS p ON pr.photo_id = p.id 
    WHERE resort_id in (612, 333, 111) AND p.src_mask is not null 
) data 
WHERE rn <= 30 
ORDER BY resort_id, num; 

Answer 2

可以使用CTE与ROW_NUMBER()和PARTITION BY

WITH Results_CTE AS 
(
    SELECT 
    pr.num, pr.resort_id, p.src_mask,ROW_NUMBER() over (PARTITION BY pr.resort_id ORDER BY num) As Row_number 
FROM 
    rutraveler.rt_photo_resort AS pr 
    JOIN rutraveler.rt_photo AS p ON pr.photo_id = p.id 
WHERE pr.resort_id IN (A,B,C) AND p.src_mask is not null 
) 

SELECT * 
FROM Results_CTE 
WHERE Row_number <= 30