我有一个包含最小值列和最大值列的表,这些列可能有重叠的数据。
示例
+--------+--------+
| Minval | Maxval |
+--------+--------+
| 0000 | 1000 |
| 1500 | 8999 |
| 0100 | 0200 |
| 5000 | 6999 |
+--------+--------+
最小值和最大值的可能范围为0000-9999。
我正在寻找一种方法来找出数据中的漏洞。根据上述数据,差距为1001-1499和9000-9999。
我正在使用php和mysql。
您不需要php代码。您可以在SQL中执行此操作。
间隙将从比最大值多一个开始,到比最小值少一个结束。然后你只需要看看一个特定的记录是否参与了一个缺口。
select t.*,
t.maxval+1 as gapStart,
(select min(t2.minval) - 1
from t t2
where t2.minval > t.maxval
) as gapEnd
from t
where not exists (select 1
from t t2
where t.maxval + 1 between t2.minval and t2.maxval
)
MySQL查询可以返回结果,无需脚本。
SELECT CONCAT(LPAD(r.lo,4,'0'),'-',LPAD(r.hi,4,'0')) AS gap
, r.lo
, r.hi
-- , d.minval IS NULL AS gap
-- , d.*
FROM ( SELECT rl.lo, rh.hi
FROM (SELECT 0000 AS lo UNION
SELECT rlo.maxval+1
FROM example1 rlo
WHERE rlo.maxval < 9999
) rl
JOIN (SELECT 9999 AS hi UNION
SELECT rhi.minval-1
FROM example1 rhi
WHERE rhi.minval > 0000
) rh
ON rh.hi >= rl.lo
GROUP BY rl.lo, rh.hi
) r
LEFT
JOIN example1 d
ON r.lo BETWEEN d.minval+0 AND d.maxval+0
OR r.hi BETWEEN d.minval+0 AND d.maxval+0
OR d.minval+0 BETWEEN r.lo AND r.hi
OR d.maxval+0 BETWEEN r.lo AND r.hi
WHERE d.minval IS NULL
ORDER
BY r.lo, r.hi
-- , d.minval, d.maxval
我使用的方法是从一组所有可能的差距开始。我们知道,每一个潜在的缺口都会:
- 从
0000
或任何maxval+1
开始 - 结束于
9999
或任何minval-1
因此,我们可以生成一个所有可能的"间隙开始"列表和一个所有可能性的"间隙结束"列表。(当我编写查询时,我认为这些是"范围"。我为内联视图使用别名,r
表示"范围",rh
表示范围的"高端",而rl
表示范围的低端。
分配了别名"r
"的内联视图返回所有可能是间隙的行。(这几乎是一个交叉连接,但我们消除了r.hi小于r.lo的行。我认为用于此的实际术语是"不等式连接"。)
返回到原始范围表的反联接模式消除了不是真正间隙的行,因为与表中的某个范围有一些重叠。(反联接模式是一个LEFT join,然后是WHERE子句中的一个谓词,如果LEFT JON操作找到匹配的行,则消除行,因此我们只剩下不匹配的行。作为替代方案,可以使用NOT EXISTS (correlated subquery)
方法来完成相同的消除。)
(当范围表中没有行时,此查询还将返回间隙0000-9999
。)
为了设置测试用例(我将OP 0000
值调整为0055
,以证明这将识别启动0000
的间隙。)
CREATE TABLE `example1` (minval INT(4), maxval INT(4));
INSERT INTO `example1` VALUES (0055,1000),(1500,8999),(0100,0200),(5000,6999);
gap lo hi
--------- ----- -----
0000-0054 0 54
1001-1499 1001 1499
9000-9999 9000 9999
编辑
我刚刚注意到OP注释的数据类型是CHAR(4),我假设它是整数类型,可能是INT(4) ZEROFILL
。上面的查询也适用于CHAR类型,但我们需要确保CHAR被转换为整数,最简单的方法是向列引用添加"+0",进行调整并测试。
CREATE TABLE `example1` (minval CHAR(4), maxval CHAR(4));
INSERT INTO `example1` VALUES ('0055','1000')
,('1500','8999'),('0100','0200'),('5000','6999');
您当然需要一个PHP代码
CREATE TABLE TEMP_TABLE (NUMBER_VALUE INT);
INSERT INTO TEMP_TABLE VALUES (1,2,3,....9999);
您可以使用php循环来完成此操作,或者创建一次常量查询并重用它
然后对主表中的每一行进行
DELETE FROM TEMP_TABLE WHERE NUMBER_VALUE BETWEEN MINVAL AND MAXVAL;
一旦删除,你将留下空白,但所有的数字。然后,您可以循环这些以在PHP 中存储最小值和最大值