Oracle SQL: ограничение количества строк в запросе с таблицей ассоциации

Question

Этот запрос извлечен из сложного.

В этом примере у меня есть две таблицы данных: user_table, ref и одна таблица ассоциации: user_ref_asso.

Схема и тестовый запрос находятся здесь: http://sqlfiddle.com/

Я пытаюсь ограничить количество результатов USER_TABLE, используя "где rownum

Мой текущий запрос:

select * from
(SELECT u.user_id, r.ref_id, u.name, r.ref
 FROM user_table u
 INNER JOIN user_ref_asso ur
 ON ur.user_id = u.user_id
 INNER JOIN REF r
 ON r.ref_id = ur.ref_id
 order by u.user_id, r.ref_id)
WHERE rownum <= 2;

Например, если результат без ограничений строки:

USER REF
1    1
1    2
2    1
2    2
3    1
3    2

Если я установлю ограничение числа строк на 2, ожидаемый результат будет (2 разных пользователя):

USER REF
1    1
1    2
2    1
2    2

Но в моем случае результат (2 результата):

USER REF
1    1
1    2

Как ограничить количество строк в отдельном столбце user_id?

Answer 1

Используйте Аналитическую функцию для достижения этой цели:

select user_id, ref_id, name, ref from
(SELECT u.user_id, r.ref_id, u.name, r.ref, dense_rank() over (order by u.user_id) rn
   FROM user_table u
  INNER JOIN user_ref_asso ur
        ON ur.user_id = u.user_id
  INNER JOIN REF r
     ON r.ref_id = ur.ref_id
  order by u.user_id, r.ref_id)
WHERE rn <= 2;

Выход:

| USER_ID | REF_ID |  NAME |  REF | RN |
|---------|--------|-------|------|----|
|       1 |      1 | Name1 | Ref1 |  1 |
|       1 |      2 | Name1 | Ref2 |  1 |
|       2 |      1 | Name2 | Ref1 |  2 |
|       2 |      2 | Name2 | Ref2 |  2 |

sql Fiddle

Answer 2

Самое простое решение, но не очень приятное:)

select * from
(SELECT u.user_id, r.ref_id, u.name, r.ref
 FROM (select * from user_table WHERE rownum <= 2) u
 INNER JOIN user_ref_asso ur
 ON ur.user_id = u.user_id
 INNER JOIN REF r
 ON r.ref_id = ur.ref_id
 order by u.user_id, r.ref_id);

или в случае оракула вы можете использовать некоторый аналитический запрос, чтобы вычислить количество возвращаемых пользователей и ограничить его, используя это...

/edit/ Вот запрос с использованием аналитических функций. Пришлось попробовать это, так как я не был уверен, что это RANK или DENSE_RANK:)

select * from
(SELECT u.user_id, r.ref_id, u.name, r.ref, dense_rank() over (order by u.user_id) cnt
 FROM user_table u
 INNER JOIN user_ref_asso ur
 ON ur.user_id = u.user_id
 INNER JOIN REF r
 ON r.ref_id = ur.ref_id
 order by u.user_id, r.ref_id)
WHERE cnt <= 2;

Answer 3

with cte as
(
SELECT u.user_id, r.ref_id, u.name, r.ref,rank()over(partition by r.ref_id order by u.user_id ) as rn
 FROM user_table u
 INNER JOIN user_ref_asso ur
 ON ur.user_id = u.user_id
 INNER JOIN REF r
 ON r.ref_id = ur.ref_id
 order by u.user_id, r.ref_id
)

select * from cte where rn<=2

Посмотреть демо