Сравнить два набора результатов SQL-запросов
Итак, у меня есть приложение, которое проверяет запросы SQL SELECT на некоторые определенные ответы.
Сейчас он сравнивает результирующие наборы двух запросов, используя несколько циклов FOR из PHP. Он ищет каждый столбец из определенный запрос и пытается найти его в вход запрос;
Есть ли способ проверить это на уровне оракула?
Пример: допустим, у нас есть следующая таблица
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 13-JUL-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 13-JUL-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Определенный ответ:
SELECT ENAME, JOB FROM EMP WHERE DEPTNO=20;
Входной ответ:
SELECT * FROM EMP WHERE DEPTNO=20;
Эти два запроса более или менее эквивалентны с точки зрения выбранных строк.
Как я могу проверить этот факт?
- (Что 2 запроса эквивалентны в терминах строк)
- (Входной запрос может иметь дополнительные столбцы)
Что я пробовал:
- с использованием операторов UNION / MINUS.
(Проблема в том, что когда запросы имеют разное количество столбцов, МИНУС не работает).
Какие-либо предложения?
Заранее спасибо.
Решение
В какой-то степени это зависит от того, какую информацию вы хотите использовать в своем заявлении, и какую информацию вы надеетесь получить динамически. Это также зависит от того, как вы собираетесь задавать вопрос Oracle.
Если идея заключается в том, что вы создадите запрос, который включает в себя ссылочный запрос и входной запрос, и сообщит вам, соответствуют ли они, и если вы хотите положиться на приложение, предоставляющее явный список столбцов, и если вам нужна проверить, является ли заголовок столбца набора результатов, и если вам не важен порядок столбцов или строк, то вы можете сделать это следующим образом:
WITH
expected AS (
-- select all the expected columns, plus a count
SELECT ENAME, JOB, COUNT(*) AS count FROM (
-- the standard query to test against
SELECT ENAME, JOB FROM EMP WHERE DEPTNO=20
) reference
-- group by all the columns of the result
GROUP BY ENAME, JOB
),
observed AS (
-- choose only the expected columns (Oracle will barf if any are not present),
-- plus a count
SELECT ENAME, JOB, COUNT(*) AS count FROM (
-- the query whose results are to be tested
SELECT * FROM EMP WHERE DEPTNO=20
) user_specified
-- group by all the columns of the reference result
GROUP BY ENAME, JOB
)
SELECT COUNT(*) as differences
FROM (
(
SELECT * FROM expected
MINUS
SELECT * FROM observed
)
UNION ALL
(
SELECT * FROM observed
MINUS
SELECT * FROM expected
)
) differing_rows
Это во многом шаблонно. Вы вставляете весь ссылочный запрос в одном месте, весь входной запрос в другом и (тот же) список ожидаемых столбцов в четырех отдельных местах. Результатом запроса является одна строка с одним столбцом, содержащим значение 0, если результаты запроса точно совпадают для интересующих столбцов (игнорируя порядок столбцов и строк), или значение, большее нуля, в противном случае. В случае, если любой запрос компонента возвращает дубликаты строк, общий результат проверяет, возвращают ли два одинаковое количество копий каждой дублирующейся строки.
Другие решения
Других решений пока нет …