#sql #oracle
Вопрос:
У меня есть два стола:
ТАБЛИЦА А :
CREATE TABLE z_ostan ( id NUMBER PRIMARY KEY,
name VARCHAR2(30) NOT NULL CHECK (upper(name)=name)
);
ТАБЛИЦА В:
CREATE TABLE z_shahr ( id NUMBER PRIMARY KEY,
name VARCHAR2(30) NOT NULL CHECK (upper(name)=name),
ref_ostan NUMBER,
CONSTRAINT fk_ref_ostan FOREIGN KEY (ref_ostan) REFERENCES z_ostan(id)
);
Как я могу найти «идентификатор» второго и третьего места из таблицы A-Наименее используемой таблицы B в таблице? Без использования предопределенных функций, таких как «count()»
Комментарии:
1. Почему «без использования
count()? Это самый естественный и быстрый способ решить проблему. Еще быстрее, если у вас есть индексref_ostan— если я правильно понимаю вашу проблему: найдите три идентификатора из таблицы A, которые отображаются наименьшее количество раз, какref_ostanв таблице B. Это правда? (И если идентификатор из A вообще не отображается в B, считается ли это «появляется 0 раз», так что это наименьшее количество раз?)2. Без использования предопределенных функций, таких как «count()» — означает ли это «без каких-либо агрегатных или аналитических функций»? Следует ли вам делать это на чистом SQL или на любом другом языке? В последнем случае напишите процедуру PL/SQL и посчитайте строки самостоятельно. Если это какое — то упражнение, возможно, стоит проверить, в каком контексте оно появляется-какие функции обсуждаются и т. Д.
3. 1) решите проблему с помощью COUNT (на самом деле не ясно, какова цель). 2) перепишите его без ПОДСЧЕТА , например
SUM, можно легко имитировать ПОДСЧЕТ. Если даже это не нравится , напишите свой собственный код…4. Какого поведения вы хотели бы в случае совпадения подсчетов? Вы хотите обработать это как DENSE_RANK, где мы можем увидеть более одного 2-го и/или 3-го наименее используемого числа, или вы хотите произвольно разорвать связи с первичным ключом? Лучше всего показывать фактические случаи с ожидаемыми результатами для каждого из различных случаев.
5. @mathguy Это практика, и Из-За Этого Я не Могу Использовать count. Но Вопрос: У нас есть FK В ТАБЛИЦЕ B Из Таблицы A. Мы Хотим Подсчитать, Сколько раз Идентификатор Из Таблицы A Использовался В Таблице B, и Напечатать 2-й и 3-й, Которые минимально повторяются в Таблице B
Ответ №1:
Это обрабатывает только существующие ссылки на таблицу A.
Обновлено для oracle (используется 12c)
Без использования каких-либо агрегатных или оконных функций:
Пример данных для таблицы: tblb
---- --------- ---------
| id | name | tbla_id |
---- --------- ---------
| 1 | TBLB_01 | 1 |
| 2 | TBLB_02 | 1 |
| 3 | TBLB_03 | 1 |
| 4 | TBLB_04 | 1 | 4 rows
| 5 | TBLB_05 | 2 |
| 6 | TBLB_06 | 2 |
| 7 | TBLB_07 | 2 | 3 rows
| 8 | TBLB_08 | 3 |
| 9 | TBLB_09 | 3 |
| 10 | TBLB_10 | 3 |
| 11 | TBLB_11 | 3 |
| 12 | TBLB_12 | 3 |
| 13 | TBLB_13 | 3 | 6 rows
| 14 | TBLB_14 | 4 |
| 15 | TBLB_15 | 4 |
| 16 | TBLB_16 | 4 | 3 rows
| 17 | TBLB_17 | 5 | 1 row
| 18 | TBLB_18 | 6 |
| 19 | TBLB_19 | 6 | 2 rows
| 20 | TBLB_20 | 7 | 1 row
---- --------- ---------
Есть много способов выразить эту логику.
Шаг за шагом с условиями CTE. Цель такова (для каждого набора строк tbla_id в tblb)
- создайте номер строки (n) для строк в каждом разделе. Обычно для этого мы используем оконные функции. Но я предполагаю, что это запрещено.
- Используйте этот номер строки (n) для определения количества строк в каждом разделе tbla_id. Чтобы найти это количество для каждого раздела, найдите последнюю строку в каждом разделе (начиная с шага 1).
- Упорядочите результаты шага 2 по n из этих последних строк. Выберите 2-ю и 3-ю строки этого результата.
WITH first AS ( -- Find the first row per tbla_id
SELECT t1.*
FROM tblb t1
LEFT JOIN tblb t2
ON t1.id > t2.id
AND t1.tbla_id = t2.tbla_id
WHERE t2.id IS NULL
)
, rnum (id, name, tbla_id, n) AS ( -- Generate a row_number (n) for each tbla_id partition
SELECT f.*, 1 FROM first f UNION ALL
SELECT n.id, n.name, n.tbla_id, c.n 1
FROM rnum c
JOIN tblb n
ON c.tbla_id = n.tbla_id
AND c.id < n.id
LEFT JOIN tblb n2
ON n.tbla_id = n2.tbla_id
AND c.id < n2.id
AND n.id > n2.id
WHERE n2.id IS NULL
)
, last AS ( -- Find the last row in each partition to obtain the count of tbla_id references
SELECT t1.*
FROM rnum t1
LEFT JOIN rnum t2
ON t1.id < t2.id
AND t1.tbla_id = t2.tbla_id
WHERE t2.id IS NULL
)
SELECT * FROM last
ORDER BY n, tbla_id OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
;
Конечный результат, где n — количество ссылок на tbla:
------ --------- --------- ------
| id | name | tbla_id | n |
------ --------- --------- ------
| 20 | TBLB_20 | 7 | 1 |
| 19 | TBLB_19 | 6 | 2 |
------ --------- --------- ------
Некоторые промежуточные результаты…
last Результат срока CTE. 2-я и 3-я строки этого становятся конечным результатом.
------ --------- --------- ------
| id | name | tbla_id | n |
------ --------- --------- ------
| 17 | TBLB_17 | 5 | 1 |
| 20 | TBLB_20 | 7 | 1 |
| 19 | TBLB_19 | 6 | 2 |
| 7 | TBLB_07 | 2 | 3 |
| 16 | TBLB_16 | 4 | 3 |
| 4 | TBLB_04 | 1 | 4 |
| 13 | TBLB_13 | 3 | 6 |
------ --------- --------- ------
rnum Результат срока CTE. Это обеспечивает номер строки над разделами tbla_id, упорядоченными по идентификатору
------ --------- --------- ------
| id | name | tbla_id | n |
------ --------- --------- ------
| 1 | TBLB_01 | 1 | 1 |
| 2 | TBLB_02 | 1 | 2 |
| 3 | TBLB_03 | 1 | 3 |
| 4 | TBLB_04 | 1 | 4 |
| 5 | TBLB_05 | 2 | 1 |
| 6 | TBLB_06 | 2 | 2 |
| 7 | TBLB_07 | 2 | 3 |
| 8 | TBLB_08 | 3 | 1 |
| 9 | TBLB_09 | 3 | 2 |
| 10 | TBLB_10 | 3 | 3 |
| 11 | TBLB_11 | 3 | 4 |
| 12 | TBLB_12 | 3 | 5 |
| 13 | TBLB_13 | 3 | 6 |
| 14 | TBLB_14 | 4 | 1 |
| 15 | TBLB_15 | 4 | 2 |
| 16 | TBLB_16 | 4 | 3 |
| 17 | TBLB_17 | 5 | 1 |
| 18 | TBLB_18 | 6 | 1 |
| 19 | TBLB_19 | 6 | 2 |
| 20 | TBLB_20 | 7 | 1 |
------ --------- --------- ------
Есть несколько других способов решения этой проблемы только в SQL.