mysql - 选择一个数字范围

Question

查询:

SELECT nummer
   FROM tabell_med_nummer
  WHERE SUBSTR(nummer, -1) = 0

此查询在表 tabell_med_nummer 中选择一个以“0”结尾的数字。例如，它会选择 1000、1010、1020，但不会选择 1001、1002 等等。

我需要基于此获得一个范围。例如 1000-1100 或 1010-1023。范围的大小是可变的。以什么结尾并不重要，但必须选择一个以“0”结尾的数字，如1010。

棘手的部分是并非每个数字都存在于表中。因此，如果表中不存在 1004，那么我需要一个 100 的范围，它不能从 1000 开始。

有谁知道我该如何解决这个问题？

根据要求:

    +----+-------------+-------+-------+-------------------------------+---------------+---------+------+------+---------------------------------+
| id | select_type | table | type  | possible_keys                 | key           | key_len | ref  | rows | Extra                           |
+----+-------------+-------+-------+-------------------------------+---------------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | f     | ALL   | NULL                          | NULL          | NULL    | NULL |   19 | Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | index | telefonnummer,telefonnummer_2 | telefonnummer | 5       | NULL | 1001 | Using index; Using join buffer  |
|  1 | SIMPLE      | p     | ALL   | NULL                          | NULL          | NULL    | NULL | 4568 | Using where; Using join buffer  |
|  1 | SIMPLE      | t3    | index | telefonnummer,telefonnummer_2 | telefonnummer | 5       | NULL | 1001 | Using index; Using join buffer  |
|  1 | SIMPLE      | t1    | ALL   | telefonnummer,telefonnummer_2 | NULL          | NULL    | NULL | 1001 | Using where; Using join buffer  |
+----+-------------+-------+-------+-------------------------------+---------------+---------+------+------+---------------------------------+
5 rows in set (0.00 sec)

我更改了一些表名和字段名并添加了一些连接和 where 语句:

SELECT t1.telefonnummer start, t2.telefonnummer end, t1.kundenummer kundenummer, t1.fornavn fornavn, t1.etternavn etternavn, t1.bedriftsnavn bedriftsnavn, t1.organisasjonsnummer organisasjonsnummer, t1.partnerID partnerID

FROM TELEFONNUMMERTILDELING t1

JOIN TELEFONNUMMERTILDELING t2
ON t1.telefonnummer % 10 = 0
AND t1.telefonnummer <= t2.telefonnummer

JOIN TELEFONNUMMERTILDELING t3
ON t3.telefonnummer BETWEEN t1.telefonnummer AND t2.telefonnummer

JOIN TELEFONNUMMERTILDELING_POSTNUMMER p
ON p.postnummer = 4085

JOIN TELEFONNUMMERTILDELING_FYLKE f
ON f.ID = p.fylkeID GROUP BY start, end

HAVING end - start + 1 = COUNT(*)
AND end - start + 1 = 50
AND (kundenummer IS NULL OR kundenummer = '')
AND (fornavn IS NULL OR fornavn = '')
AND (etternavn IS NULL OR etternavn = '')
AND (bedriftsnavn IS NULL OR bedriftsnavn = '')
AND (organisasjonsnummer IS NULL OR organisasjonsnummer = '')
AND partnerID = 1001 

Answer 1

-- get the start and end points
SELECT   t1.nummer AS start, t2.nummer AS end

-- pair every possible start number with every potential end number
FROM     tabell_med_nummer t1
    JOIN tabell_med_nummer t2
      ON t1.nummer % 10 = 0 AND t1.nummer <= t2.nummer

-- obtain every number in between
    JOIN tabell_med_nummer t3
      ON t3.nummer BETWEEN t1.nummer AND t2.nummer

-- group into potential ranges
GROUP BY start, end

-- now limit only to contiguous ranges
  HAVING end - start + 1 = COUNT(*)

-- and those that contain the desired number of records
     AND end - start + 1 = ?

如果 nummer 不能保证是唯一的，例如使用 UNIQUE 约束，那么您需要将 COUNT(*) 替换为性能较差的 COUNT(DISTINCT t3.nummer) 或者替换t3 与 (SELECT DISTINCT nummer FROM tabell_med_nummer)。