如何找出已删除的行?

编程入门行业动态更新时间:2024-10-26 10:37:48

本文介绍了如何找出已删除的行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

带有SE引擎的Informix-SQL 7.32:

Informix-SQL 7.32 with SE engine:

我有一个客户从SE表中删除了几行. (我不使用事务日志记录或审计).该表具有一个串行列.我想创建一个Ace报告来打印缺少的序列列.我尝试了以下快速而肮脏的报告，但没有成功！..您能建议一种更好的方法吗?

I have a customer who deleted several rows from an SE table. (I'm not using transaction logging or audit). The table has a serial column. I would like to create an Ace report to print the missing serial columns. I tried the following quick and dirty report, but it didn't work!.. can you suggest a better way?

define variable next_id integer end select tbl_id from tbl order by tbl_id {I'm ordering tbl_id because all the rows are periodically re-clustered} end {by an fk_id in order to group all rows belonging to the same customer} format on every row let next_id = tbl_id + 1 after group of tbl_id if tbl_id + 1 <> next_id then print column 1, tbl_id + 1 using "######" end

或创建一个包含INT列的临时表，该列包含从1到5000的序号，并执行以下select语句:

or maybe create a temporary table with an INT column containing sequential numbers from 1 to 5000 and do a select statement like:

SELECT tbl_id FROM tbl WHERE tbl_id NOT IN (SELECT tmp_int FROM tmp);

或带有HAVING，OUTER等的select语句.

or a select statement with HAVING, OUTER, etc.

推荐答案

由于这是SE，因此我们必须使用老式的表示法，而不是SQL-92 JOIN表示法.

Since this is SE, we have to use the old-fashioned notation, not the SQL-92 JOIN notations.

以下四个查询是两个可能答案的共同基础:

The four queries that follow are a common foundation for the two possible answers:

SELECT t1.tbl_id AS tbl_id, t2.tbl_id AS ind FROM tbl AS t1, OUTER tbl AS t2 WHERE t1.tbl_id + 1 = t2.tbl_id INTO TEMP x1; SELECT t1.tbl_id AS tbl_id, t2.tbl_id AS ind FROM tbl AS t1, OUTER tbl AS t2 WHERE t1.tbl_id - 1 = t2.tbl_id INTO TEMP x2; SELECT tbl_id AS hi_range FROM x1 WHERE ind IS NULL INTO TEMP x3; SELECT tbl_id AS lo_range FROM x2 WHERE ind IS NULL INTO TEMP x4;

表x3和x4现在分别包含tbl_id的值，这些值没有立即后继者，也没有立即前任者.每个值都是tbl_id值的连续范围的开始或结束.在IDS而不是SE中，可以使用标准的SQL OUTER JOIN表示法，并在两个查询而不是四个查询中过滤连接的结果.您在SE中没有那么奢侈.

The tables x3 and x4 now contain (respectively) the values for tbl_id that have no immediate successor and no immediate predecessor. Each value is the start or end of a contiguous ranges of tbl_id values. In IDS instead of SE, you could use the standard SQL OUTER JOIN notation and filter the results of the join in two queries instead of four; you do not have that luxury in SE.

现在，您只需要弄清楚如何合并两个表:

Now you just have to work out how to combine the two tables:

SELECT t1.lo_range, t2.hi_range FROM x4 AS t1, x3 AS t2 WHERE t1.lo_range <= t2.hi_range AND NOT EXISTS (SELECT t3.lo_range, t4.hi_range FROM x4 AS t3, x3 AS t4 WHERE t3.lo_range <= t4.hi_range AND t1.lo_range = t3.lo_range AND t2.hi_range > t4.hi_range );

此查询的主要部分出现两次，并生成范围的开始小于或等于范围的末尾的所有成对的行(等于允许范围"本身由一个值组成，并带有两侧的已删除行). NOT EXISTS子句可确保没有其他对具有相同的起始值和较小的终止值.

The main part of this query occurs twice and generates all pairs of rows where the start of the range is less than or equal to the end of the range (equal allows for 'ranges' consisting of one value on its own, with deleted rows on either side). The NOT EXISTS clause ensures that there is no other pair with the same start value and a smaller end value.

如果数据中有很多空白，则对临时表的查询可能不会很快.如果差距很小，那就应该可以了.

The queries on the temp tables may not be very fast if there are many gaps in the data; if there are very few gaps, then they should be OK.

最后一个查询在范围数方面表现出二次行为.当我只有十几个范围时，一切都很好(响应时间不到一秒)；当我有1200个射程时，那还不行-没有在合理的时间内完成.

The last query exhibits quadratic behaviour in terms of the number of ranges. When I had just a dozen ranges, it was fine (sub-second response time); when I had 1,200 ranges, it was not OK - did not complete in a reasonable time.

由于二次行为不好，我们该如何重新表述查询...

Since quadratic behaviour is not good, how can we rephrase the query...

对于范围的每个下限，请找到大于或等于下限的范围的最小上限，或者在SQL中:

For each low end of the range, find the minimum high end of a range that is greater than or equal to the low end, or in SQL:

SELECT t1.lo_range, MIN(t2.hi_range) AS hi_range FROM x4 AS t1, x3 AS t2 WHERE t2.hi_range >= t1.lo_range GROUP BY t1.lo_range;

请注意，可以轻松将其合并到ACE报告中.它为您提供了存在的数字范围-而不是缺少的范围.您可以弄清楚如何生成另一个.

Note that this can easily be incorporated into an ACE report. It gives you the ranges of number present - not those which are absent. You can work out how to generate the other.

在具有22100行，包含1200个间隔的数据的表上，该表的性能很好.在基准模式(-B)下使用(my)SQLCMD程序，并将SELECT输出发送到/dev/null，并使用IDS 11.70.FC1在MacOS X 10.6.7(MacBook Pro，3 GHz的Intel Core 2 Duo和4 GB RAM)，结果为:

That performed pretty well on a table with 22100 rows containing 1200 gaps in the data. Using (my) SQLCMD program in its benchmark mode (-B), and sending SELECT output to /dev/null, and using IDS 11.70.FC1 run on MacOS X 10.6.7 (MacBook Pro, Intel Core 2 Duo at 3 GHz and 4 GB RAM), the results were:

$ sqlcmd -d stores -B -f gaps.sql + CLOCK START; 2011-03-31 18:44:39 + BEGIN; Time: 0.000588 2011-03-31 18:44:39 + SELECT t1.tbl_id AS tbl_id, t2.tbl_id AS ind FROM tbl AS t1, OUTER tbl AS t2 WHERE t1.tbl_id + 1 = t2.tbl_id INTO TEMP x1; Time: 0.437521 2011-03-31 18:44:39 + SELECT t1.tbl_id AS tbl_id, t2.tbl_id AS ind FROM tbl AS t1, OUTER tbl AS t2 WHERE t1.tbl_id - 1 = t2.tbl_id INTO TEMP x2; Time: 0.315050 2011-03-31 18:44:39 + SELECT tbl_id AS hi_range FROM x1 WHERE ind IS NULL INTO TEMP x3; Time: 0.012510 2011-03-31 18:44:39 + SELECT tbl_id AS lo_range FROM x2 WHERE ind IS NULL INTO TEMP x4; Time: 0.008754 + output "/dev/null"; 2011-03-31 18:44:39 + SELECT t1.lo_range, MIN(t2.hi_range) AS hi_range FROM x4 AS t1, x3 AS t2 WHERE t2.hi_range >= t1.lo_range GROUP BY t1.lo_range; Time: 0.561935 + output "/dev/stdout"; 2011-03-31 18:44:40 + SELECT COUNT(*) FROM x1; 22100 Time: 0.001171 2011-03-31 18:44:40 + SELECT COUNT(*) FROM x2; 22100 Time: 0.000685 2011-03-31 18:44:40 + SELECT COUNT(*) FROM x3; 1200 Time: 0.000590 2011-03-31 18:44:40 + SELECT COUNT(*) FROM x4; 1200 Time: 0.000768 2011-03-31 18:44:40 + SELECT t1.lo_range, MIN(t2.hi_range) AS hi_range FROM x4 AS t1, x3 AS t2 WHERE t2.hi_range >= t1.lo_range GROUP BY t1.lo_range INTO TEMP x5; Time: 0.529420 2011-03-31 18:44:40 + SELECT COUNT(*) FROM x5; 1200 Time: 0.001155 2011-03-31 18:44:40 + ROLLBACK; Time: 0.329379 + CLOCK STOP; Time: 2.202523 $