Подтвердить что ты не робот

SQL sort by version "number", строка переменной длины

Я пытаюсь создать SQL-запрос, который будет заказывать результаты по номеру версии (например, 1.1, 4.5.10 и т.д.)

Вот что я пробовал:

SELECT * FROM Requirements 
    WHERE Requirements.Release NOT LIKE '%Obsolete%' 
    ORDER BY Requirements.ReqNum

Теперь поле ReqNum является строковым полем и, к сожалению, я не могу изменить его на float или что-то подобное, потому что у меня есть номера требований, например 162.1.11.

Когда я верну результаты, я буду получать следующие настройки:

1.1
1.10
1.11
1.3

Как написать запрос, который будет сортироваться по лексикографическому порядку?

... или,

Как правильно сортировать данные?

Спасибо за ввод заранее!

4b9b3361

ОТВЕТЫ

Ответ 1

Для получения наилучших результатов храните номер хранилища рефакторинга, чтобы каждый раздел имел свой собственный столбец: MajorVersion, MinorVersion, Revision, Build. Тогда задача упорядочения становится тривиальной. Вы также можете построить вычисляемый столбец для легкого извлечения полной строки.

Ответ 2

В PostgreSQL вы можете:

SELECT * FROM Requirements
ORDER BY string_to_array(version, '.')::int[];

Этот последний ::int[] позволяет преобразовать значения строк в целые числа, а затем сравнить их как таковые.

Ответ 3

SELECT * FROM Requirements 
WHERE Requirements.Release NOT LIKE '%Obsolete%' 
ORDER BY cast('/' + replace(Requirements.ReqNum , '.', '/') + '/' as hierarchyid);

Ответ 4

Если вы не перепроектируете таблицу, как разумно предлагает Joel Coehoorn, вам нужно переформатировать номера версий в строку, которая вам нужна, например,

  • 1.1 → 0001.0001.0000
  • 162.1.11 → 0162.0001.0011

Это может быть сделано с помощью функции или с использованием вычисленного/виртуального столбца, если у вашей СУБД есть эти данные. Затем вы можете использовать эту функцию или столбец в предложении ORDER BY.

Ответ 5

Следующая функция примет номер версии и отформатирует каждый уровень до трех цифр:

Использование:

select * from TableX order by dbo.fn_VersionPad(VersionCol1)

Функции:

CREATE FUNCTION [dbo].[fn_VersionPad]
(
    @version varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
    /*
        Purpose:  Pads multi-level Version Number sections to 3 digits
        Example:  1.2.3.4
        Returns:  001.002.003.004
    */

    declare @verPad varchar(20)
    declare @i int
    declare @digits int

    set @verPad = ''

    set @i = len(@version)
    set @digits = 0

    while @i > 0
    begin
        if (substring(@version, @i, 1) = '.')
        begin
            while (@digits < 3)
            begin
                -- Pad version level to 3 digits
                set @verPad = '0' + @verPad
                set @digits = @digits + 1
            end

            set @digits = -1
        end

        set @verPad = substring(@version, @i, 1) + @verPad

        set @i = @i - 1
        set @digits = @digits + 1
    end

    while (@digits < 3)
    begin
        -- Pad version level to 3 digits
        set @verPad = '0' + @verPad
        set @digits = @digits + 1
    end

    return @verPad
END

Ответ 6

Если вы находитесь на земле SQL Server...

DECLARE @string varchar(40)
SET @string = '1.2.3.4'
SELECT PARSENAME(@string, 1), PARSENAME(@string, 2), PARSENAME(@string, 3), PARSENAME(@string, 4)

Результаты: 4, 3, 2, 1

Полезно для разбора IP-адресов и других пунктирных объектов, например номера версии. (Вы можете использовать REPLACE() для преобразования элементов в точечную нотацию тоже... например 1-2-3-4 → 1.2.3.4)

Ответ 7

Небольшое отклонение от ответа @vuttipong-l (T-SQL)

SELECT V
FROM (
SELECT '6.1.3' V UNION
SELECT '6.11.3' UNION
SELECT '6.2.3' UNION
SELECT '6.1.12' 
) AS q
ORDER BY cast('/' + V + '/' as hierarchyid)

Работает в SQL Server начиная с 2008 года, точки в порядке в строчном представлении столбца hierarchyid, поэтому нам не нужно заменять их косой чертой. Цитата из документа:

Сравнение выполняется путем сравнения целочисленных последовательностей, разделенных по точкам в порядке словаря.

Там одно предостережение: сегменты версии не должны иметь префикс с нулями.

Ответ 8

Вы можете разделить строку (вы уже знаете разделители: "." ) с CHARINDEX/SUBSTR и ORDER разными частями. Сделайте это в функции или сделайте это по частям.

Это будет некрасиво, и это будет не так быстро, поэтому, если вам нужны быстрые запросы, следуйте за Тони или Джоэлом.

Ответ 9

Это будет работать, если вы используете Microsoft SQL Server:

create function fnGetVersion (@v AS varchar(50)) returns bigint as
begin
declare @n as bigint;
declare @i as int;
select @n = 0;
select @i = charindex('.',@v);
while(@i > 0)
begin
    select @n = @n * 1000;
    select @n = @n + cast(substring(@v,1,@i-1) as bigint); 
    select @v = substring(@v,@i+1,len(@v)[email protected]);
    select @i = charindex('.',@v);
end
return @n * 1000 + cast(@v as bigint);
end

Проверьте, выполнив эту команду:

select dbo.fnGetVersion('1.2.3.4')

Это вернет номер 1002003004, который отлично сортируется. Вам нужно, чтобы 9.0.1 было больше 2.1.2.3, тогда вам нужно немного изменить логику. В моем примере 9.0.1 будет сортироваться до 2.1.2.3.

Ответ 10

НЕ КАКИЕ-ЛИБО КОДЫ

Insert into @table
Select 'A1' union all
Select 'A3' union all
Select 'A5' union all
Select 'A15' union all
Select 'A11' union all
Select 'A10' union all
Select 'A2' union all
Select 'B2' union all
Select 'C2' union all
Select 'C22' union all
Select 'C221' union all
Select 'A7' 

Select cod from @table
Order by LEN(cod),cod

Результат:

A1
A2
A3
A5
A7
B2
C2
A10
A11
A15
C22
C221

Это просто:

Declare @table table(id_ int identity(1,1), cod varchar(10))

Insert into @table
Select 'A1' union all
Select 'A3' union all
Select 'A5' union all
Select 'A15' union all
Select 'A11' union all
Select 'A10' union all
Select 'A2' union all
Select 'A7' 

Select cod from @table
Order by LEN(cod),cod

Ответ 11

Функция для PostgreSQL

Просто используйте

select *
  from sample_table
 order by _sort_version(column_version);




CREATE FUNCTION _sort_version (
  p_version text
)
RETURNS text AS
$body$
declare 
  v_tab text[];
begin
  v_tab := string_to_array(p_version, '.');  

  for i in 1 .. array_length(v_tab, 1) loop
    v_tab[i] := lpad(v_tab[i], 4, '0');
  end loop;

  return array_to_string(v_tab, '.');
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
COST 1;

Ответ 12

ФИКСЕД ТИШ ПУТЬ.

<pre>
00000001    1
00000001.00000001   1.1
00000001.00000001.00000001  1.1.1
00000001.00000002   1.2
00000001.00000009   1.9
00000001.00000010   1.10
00000001.00000011   1.11
00000001.00000012   1.12
00000002    2
00000002.00000001   2.1
00000002.00000001.00000001  2.1.1
00000002.00000002   2.2
00000002.00000009   2.9
00000002.00000010   2.10
00000002.00000011   2.11
00000002.00000012   2.12

select * from (select '000000001' as tCode,'1' as Code union
select '000000001.000000001' as tCode,'1.1'as Code union
select '000000001.000000001.000000001' as tCode,'1.1.1'as Code union
select '000000001.000000002' as tCode,'1.2'  union
select '000000001.000000010' as tCode,'1.10'as Code union
select '000000001.000000011' as tCode,'1.11'as Code union
select '000000001.000000012' as tCode,'1.12'as Code union
select '000000001.000000009' as tCode,'1.9' as Code
union
select '00000002' as tCode,'2'as Code union
select '00000002.00000001' as tCode,'2.1'as Code union
select '00000002.00000001.00000001' as tCode,'2.1.1'as Code union
select '00000002.00000002' as tCode,'2.2'as Code union
select '00000002.00000010' as tCode,'2.10'as Code union
select '00000002.00000011' as tCode,'2.11'as Code union
select '00000002.00000012' as tCode,'2.12'as Code union
select '00000002.00000009' as tCode,'2.9'as Code ) as t
order by t.tCode

</pre>

<pre>


public static string GenerateToCodeOrder(this string code)
    {
        var splits = code.Split('.');
        var codes = new List<string>();
        foreach (var str in splits)
        {
            var newStr = "";
            var zeroLength = 10 - str.Length;
            for (int i = 1; i < zeroLength; i++)
            {
                newStr += "0";
            }
            newStr += str;
            codes.Add(newStr);
        }
        return string.Join(".", codes);
    }

</pre>

Ответ 13

В PostgreSQL это не может быть проще:

SELECT ver_no FROM version ORDER BY string_to_array(ver_no, '.', '')::int[]

Ответ 14

У меня была такая же проблема, хотя мои были с номерами квартир, такими как A1, A2, A3, A10, A11 и т.д., что они хотели сортировать "правильно". Если разделение номера версии на отдельные столбцы не работает, попробуйте этот PL/SQL. Он берет строку, такую ​​как A1 или A10, и расширяет ее до A0000001, A0000010 и т.д., Поэтому она сортируется красиво. Просто назовите это в предложении ORDER BY, например

выберите apt_num из квартиры порядок от PAD (apt_num)

function pad(inString IN VARCHAR2)
   return VARCHAR2

--This function pads the numbers in a alphanumeric string.
--It is particularly useful in sorting, things like "A1, A2, A10"
--which would sort like "A1, A10, A2" in a standard "ORDER BY name" clause
--but by calling "ORDER BY pkg_sort.pad(name)" it will sort as "A1, A2, A10" because this
--function will convert it to "A00000000000000000001, A00000000000000000002, A00000000000000000010" 
--(but since this is in the order by clause, it will
--not be displayed.

--currently, the charTemplate variable pads the number to 20 digits, so anything up to 99999999999999999999 
--will work correctly.
--to increase the size, just change the charTemplate variable.  If the number is larger than 20 digits, it will just
--appear without padding.


   is
      outString VARCHAR2(255);
      numBeginIndex NUMBER;
      numLength NUMBER;
      stringLength NUMBER;
      i NUMBER;
      thisChar VARCHAR2(6);
      charTemplate VARCHAR2(20) := '00000000000000000000';
      charTemplateLength NUMBER := 20;


   BEGIN
      outString := null;
      numBeginIndex := -1;
      numLength := 0;
      stringLength := length(inString);

      --loop through each character, get that character
      FOR i IN 1..(stringLength) LOOP
         thisChar := substr(inString, i, 1);

         --if this character is a number
         IF (FcnIsNumber(thisChar)) THEN

            --if we haven't started a number yet
            IF (numBeginIndex = -1) THEN
               numBeginIndex := i;
               numLength := 1;

            --else if we're in a number, increase the length
            ELSE 
               numLength := numLength + 1;
            END IF;

            --if this is the last character, we have to append the number
            IF (i = stringLength) THEN
               outString:= FcnConcatNumber(inString, outString, numBeginIndex, numLength, charTemplate, charTemplateLength);
            END IF;

         --else this is a character
         ELSE

            --if we were previously in a number, concat that and reset the numBeginIndex
            IF (numBeginIndex != -1) THEN
               outString:= FcnConcatNumber(inString, outString, numBeginIndex, numLength, charTemplate, charTemplateLength);
               numBeginIndex := -1;
               numLength := 0;
            END IF;

            --concat the character
            outString := outString || thisChar;
         END IF;
      END LOOP;

      RETURN outString;

   --any exception, just return the original string
   EXCEPTION WHEN OTHERS THEN
      RETURN inString;

   END;

Ответ 15

Вот пример запроса, который извлекает строку. Вы должны иметь возможность использовать это либо в рефакторинге UPDATE базы данных, либо просто в своем запросе как есть. Однако я не уверен, как это вовремя; просто что-то посмотреть и проверить.

SELECT SUBSTRING_INDEX("1.5.32",'.',1) AS MajorVersion,
  SUBSTRING_INDEX(SUBSTRING_INDEX("1.5.32",'.',-2),'.',1) AS MinorVersion,
  SUBSTRING_INDEX("1.5.32",'.',-1) AS Revision;

это вернет:

MajorVersion | MinorVersion | Revision
1            | 5            | 32

Ответ 16

Хорошо, если высокая производительность - это проблема, тогда ваш единственный вариант - изменить ваши значения на что-то числовое.

Однако, если это запрос с низким потреблением, вы можете просто разделить свои номера и порядок на них.

Этот запрос предполагает только основные и младшие номера версий и что они содержат только числа.

SELECT
    *
FROM
    Requirements
WHERE
    Requirements.Release NOT LIKE '%Obsolete%'
ORDER BY
    CONVERT(int, RIGHT(REPLICATE('0', 10) + LEFT(Requirements.ReqNum, CHARINDEX('.', Requirements.ReqNum)-1), 10)),
    CONVERT(int, SUBSTRING(Requirements.ReqNum, CHARINDEX('.', Requirements.ReqNum )+1, LEN(Requirements.ReqNum) - CHARINDEX('.', Requirements.ReqNum )))

Ответ 17

Для пуристов с принципом "все-в-одном", предполагая, что Oracle может решить какой-то instr/substr/decode/to_number voodoo:

SELECT *
FROM Requirements
WHERE Release NOT LIKE '%Obsolete%'
ORDER BY
    to_number(
      substr( reqnum, 1, instr( reqnum, '.' ) - 1 )
    )
  , to_number(
      substr( 
          reqnum
        , instr( reqnum, '.' ) + 1 -- start: after first occurance
        , decode( 
              instr( reqnum, '.', 1, 2 )
            , 0, length( reqnum )
            , instr( reqnum, '.', 1, 2 ) - 1 
          ) -- second occurance (or end)
          - instr( reqnum, '.', 1, 1) -- length: second occurance (or end) less first
      )
    )
  , to_number(
      decode( 
          instr( reqnum, '.', 1, 2 )
        , 0, null
        , substr( 
              reqnum
            , instr( reqnum, '.', 1, 2 ) + 1 -- start: after second occurance
            , decode( 
                  instr( reqnum, '.', 1, 3 )
                , 0, length( reqnum )
                , instr( reqnum, '.', 1, 3 ) - 1 
              ) -- third occurance (or end)
              - instr( reqnum, '.', 1, 2) -- length: third occurance (or end) less second
          ) 
      )
    )
  , to_number(
      decode( 
          instr( reqnum, '.', 1, 3 )
        , 0, null
        , substr( 
              reqnum
            , instr( reqnum, '.', 1, 3 ) + 1 -- start: after second occurance
            , decode( 
                  instr( reqnum, '.', 1, 4 )
                , 0, length( reqnum )
                , instr( reqnum, '.', 1, 4 ) - 1 
              ) -- fourth occurance (or end)
              - instr( reqnum, '.', 1, 3) -- length: fourth occurance (or end) less third
          ) 
      )
    )
;

Я подозреваю, что есть много предостережений, включая:

  • предположение о наличии младшей версии (второй)
  • ограничено четырьмя версиями, указанными в комментариях

Ответ 18

Здесь используется функция сравнения для PostgreSQL, которая будет сравнивать произвольные строки так, чтобы число цифр сравнивалось численно. Другими словами, "ABC123" > "ABC2", но "AB123" < "ABC2". Он возвращает -1, 0 или +1, как обычно выполняют такие функции сравнения.

CREATE FUNCTION vercmp(a text, b text) RETURNS integer AS $$
DECLARE
   ar text[];
   br text[];
   n integer := 1;
BEGIN
   SELECT array_agg(y) INTO ar FROM (SELECT array_to_string(regexp_matches(a, E'\\d+|\\D+|^$', 'g'),'') y) x;
   SELECT array_agg(y) INTO br FROM (SELECT array_to_string(regexp_matches(b, E'\\d+|\\D+|^$', 'g'),'') y) x;
   WHILE n <= array_length(ar, 1) AND n <= array_length(br, 1) LOOP
      IF ar[n] ~ E'^\\d+$' AND br[n] ~ E'^\\d+$' THEN
         IF ar[n]::integer < br[n]::integer THEN
            RETURN -1;
         ELSIF ar[n]::integer > br[n]::integer THEN
            RETURN 1;
         END IF;
      ELSE
         IF ar[n] < br[n] THEN
            RETURN -1;
         ELSIF ar[n] > br[n] THEN
            RETURN 1;
         END IF;
      END IF;
      n := n + 1;
   END LOOP;

   IF n > array_length(ar, 1) AND n > array_length(br, 1) THEN
      RETURN 0;
   ELSIF n > array_length(ar, 1) THEN
      RETURN 1;
   ELSE
      RETURN -1;
   END IF;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

Затем вы можете создать операторский класс, чтобы сортировка выполнялась с помощью функции сравнения с ORDER BY field USING <#:

CREATE OR REPLACE FUNCTION vernum_lt(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) < 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vernum_lte(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) <= 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vernum_eq(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) = 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vernum_gt(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) > 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vernum_gte(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN vercmp(a, b) >= 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;

CREATE OPERATOR <# ( PROCEDURE = vernum_lt, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR ># ( PROCEDURE = vernum_gt, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR =# ( PROCEDURE = vernum_lte, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR <=# ( PROCEDURE = vernum_lte, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR >=# ( PROCEDURE = vernum_gte, LEFTARG = text, RIGHTARG = text);

CREATE OPERATOR CLASS vernum_ops FOR TYPE varchar USING btree AS
  OPERATOR 1 <# (text, text),
  OPERATOR 2 <=# (text, text),
  OPERATOR 3 =#(text, text),
  OPERATOR 4 >=# (text, text),
  OPERATOR 5 ># (text, text),
  FUNCTION 1 vercmp(text, text)
;

Ответ 19

Я бы сделал, как сказал Джоэл Коэхорн. Затем, чтобы перестроить структуру данных, вам не нужно вручную ее выполнять. Вы можете написать простой script, который выполнит задание для всех 600 записей.

Ответ 20

Просто удалите точки (Inline, замените пустой строкой) приведите результат как int и order by к результату. Отлично работает:

a.Version = 1.4.18.14

select...
Order by cast( replace (a.Version,'.','') as int)