php - 比较两个 SQL 查询结果集

Question

所以我有一个应用程序可以根据某些定义的答案检查 SQL SELECT 查询。

现在它使用 PHP 中的一些 FOR 循环比较两个查询的结果集。它从定义的查询中搜索每一列，并尝试在输入查询中找到它；

有什么方法可以在 oracle 级别上检查这个吗？

示例:假设我们有下表

 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;

这两个查询在所选行方面或多或少是等价的。

我如何检查这个事实？

1. (就行而言，这 2 个查询是等价的)
2. (输入查询可能有额外的列)

我尝试过的:

1. 使用 UNION/MINUS 运算符。(问题在于，当查询具有不同数量的列时，MINUS 不起作用)。

有什么建议吗？

提前致谢。

Answer 1

这在某种程度上取决于您愿意依赖您的应用程序提供哪些信息，以及您希望动态提取哪些信息。这也取决于您打算如何向 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 的单行和单列，否则为大于零的值。如果任一组件查询返回重复行，则整体结果将测试两者是否返回相同数量的每个重复行副本。