Несколько лет назад я работал над системой, в которой числовой первичный ключ хранился в столбце [SQL Server] varchar, поэтому я быстро оторвался при запросе с оператором BETWEEN:
SELECT ID FROM MyTable WHERE ID BETWEEN 100 AND 110;Результаты:
100
102
103
109
110
11Это был просто плохой дизайн. Тем не менее, я работаю над сторонней ERP-системой, которая, как вы можете себе представить, должна быть универсальной и гибкой; таким образом, у нас есть различные таблицы, в которых предусмотрены буквенно-цифровые поля, где бизнес использует только цифры - так что могут возникнуть аналогичные проблемы.
Я предполагаю, что это достаточно распространенная проблема; У меня есть достаточно простое решение, но мне любопытно, как другие подходят к таким проблемам.
Мое простое решение:
SELECT ID FROM MyTable
WHERE ID BETWEEN iStartValue AND iEndValue
AND (LENGTH(ID) = LENGTH(iStartValue)
OR LENGTH(ID) = LENGTH(iEndValue));Как вы, вероятно, можете сказать, это система Oracle, но я обычно работаю в SQL Server - так что, возможно, решения, независимые от базы данных, предпочтительнее.
Изменить 1: поцарапать это - я не понимаю, почему проприетарные решения также не приветствуются.
Редактировать 2: Спасибо за все ответы. Я не уверен, разочарован ли я, что нет очевидного, сложного решения, но я соответственно рад, что не кажется, что я что-то упустил очевидное!
Я думаю, что все еще предпочитаю свое собственное решение; это просто и работает - есть ли причина, почему я не должен использовать это? Я не могу поверить, что это намного, если таковые имеются, менее эффективно, чем другие решения, предлагаемые.
Я понимаю, что в идеальном мире этой проблемы не было бы; но, к сожалению, я не работаю в идеальном мире, и часто это случай, когда можно извлечь выгоду из плохой ситуации.
Если вы уверены, что значения в ID являются только числовыми, то почему бы просто не CAST их
WHERE CAST(ID as int) BETWEEN iStartValue AND iEndValueРЕДАКТИРОВАТЬ 1: расширение метода приведения, который должен работать, состоит в том, чтобы использовать подзапрос для извлечения всех числовых записей. Пожалуйста, обратите внимание - я не думаю, что этот метод лучше, чем предложенный выше, я включаю его, поскольку он решает проблему !!!
SELECT ID
FROM (
SELECT ID
FROM MyTable
WHERE ISNUMERIC(ID) = 1
AND CHARINDEX ('.', ID) = 0
AND CHARINDEX ('-', ID) = 0
) a
WHERE CONVERT(bigint, ID) BETWEEN 0 AND 12000
ORDER BY LENGTH(ID) ASC, IDПроверка на «-» и «.» персонажи на самом деле не требуются. Я предполагаю, что ваши идентификаторы не могут быть отрицательными или десятичными.
Как насчет броска вместо этого.
SELECT ID FROM MyTable
WHERE cast(ID as signed) BETWEEN cast(iStartValue as signed) AND cast(iEndValue as signed)Данный синтаксис - MySQL, но для T-SQl существуют аналогичные операторы CAST.
Я не знаю, может ли это сработать в вашей ситуации, но ...
Как насчет добавления фактического числового столбца в таблицу, заполненную значением (в SQL Server можно использовать вычисляемый столбец с установленным постоянным индексом)
В БД других поставщиков используется какой-то другой механизм для заполнения (триггер, материализованное представление и т. Д.)
а затем использовать этот столбец вместо одного varchar ...
Возможно, LPAD (id, 12, '') подойдет вам. Все значения столбцов должны быть шириной 12 с пробелами слева.
Также я был бы немного обеспокоен числовыми значениями в столбцах varchar2.
если вы делаете что-либо числовое, например аналитику, вы можете получить исключение для нечисловых данных.
Другим вариантом было бы дополнить ваши цифры нулями слева и использовать для этого оператор before. По соображениям саргидности, вероятно, лучше включить это в качестве второго условия условия (чтобы все еще можно было использовать возможные индексы). Что-то вроде этого...
SELECT ID FROM MyTable
WHERE ID BETWEEN iStartValue AND iEndValue
And Right('0000000000' + ID, 10) Between iStartValue and iEndValue Я проверил это в SQL Server, и он возвращает правильные значения. Возможно, вам придется изменить это для работы с Oracle.