Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Натуральная сортировка  [new]
Flar
Member

Откуда:
Сообщений: 8
Добрый день. Прошу консультации по следующей проблеме:
имеется массив, содержащий в себе след. значения:
1.3.1.1.1
1.3.1.1.10
1.3.1.1.2
1.3.1.1.3
1.3.1.1.4
1.3.1.1.5

Выборка осуществляется следующим путем:
select
,data.number
from #result data

Каким образом использовать order by, чтобы получить результат:
1.3.1.1.1
1.3.1.1.2
1.3.1.1.3
1.3.1.1.4
1.3.1.1.5
1.3.1.1.10

Пытался по-разному, но что-то не учитываю.
Заранее благодарю за ответ.
16 июн 17, 05:40    [20568874]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
aleks2
Guest
Ошибка в генетическом коде.

1. Это строки. А строки сравниваются как строки - посимвольно. Так что "2" > "10".
2. Варианты "исправления"
а) привести строки к фиксированной ширине
"1.3.1.1.2" -> "1.3.1.1.02"
б) Засунуть строки в задницу и перейти к целочисленной нумерации.
16 июн 17, 07:10    [20568904]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Flar
Member

Откуда:
Сообщений: 8
aleks2,
ну первый вариант понятен. тоже о нем думал.
по второму - он тоже реализуем, но вопрос - как он мне поможет? я могу убрать "."
соответственно выборка у меня будет выглядеть:
13111
131110
13112
13113
13114
13115

Оно понятно, что при стандартной сортировке у меня автоматом элемент "131110" ускачет в низ. но в итоге при полной выборке у меня результат будет выглядеть след. образом:
12111
12112
12113
12114
12115
13111
13112
13113
13114
13115
121110
131110

а это не то, что нужно. или я не прав?
16 июн 17, 07:24    [20568909]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Добрый Э - Эх
Guest
Flar,

тебе нужна посегментная сортировка. для этого сегменты должны быть одинаковой длины и выравнены слева нулями.
А как этого достичь - тут думать надо.
В оракле я бы написал так:
with
  t as 
    (
      select '1.1.1.2'   k from dual union all
      select '1.30.10.1' k from dual union all
      select '1.30.6'    k from dual union all
      select '1.111.1.2' k from dual union all
      select '1.2.10.1'  k from dual union all
      select '3.30.6'    k from dual union all
      select '2.1.6'     k from dual union all
      select '21.1'      k from dual union all
      select '1.10.1'    k from dual union all
      select '2'         k from dual
    )
select k
  from t
 order by regexp_replace(regexp_replace(k,'(\d+)','0000000000000000\1'),'0+(\d{5})','\1')


    K 
---------
1.1.1.2
1.2.10.1
1.10.1
1.30.6
1.30.10.1
1.111.1.2
2
2.1.6
3.30.6
21.1

Но сходу не нашел аналога функции REGEXP_REPLACE...
16 июн 17, 07:42    [20568922]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

чтобы суть происходящего была понятнее, привожу и результат работы функции regexp_replace:

with
  t as 
    (
      select '1.1.1.2'   k from dual union all
      select '1.30.10.1' k from dual union all
      select '1.30.6'    k from dual union all
      select '1.111.1.2' k from dual union all
      select '1.2.10.1'  k from dual union all
      select '3.30.6'    k from dual union all
      select '2.1.6'     k from dual union all
      select '21.1'      k from dual union all
      select '1.10.1'    k from dual union all
      select '2'         k from dual
    )
select k
     , regexp_replace(regexp_replace(k,'(\d+)','0000000000000000\1'),'0+(\d{5})','\1') as r2
  from t
 order by regexp_replace(regexp_replace(k,'(\d+)','0000000000000000\1'),'0+(\d{5})','\1')

    K                 R2
---------   -----------------------
1.1.1.2	    00001.00001.00001.00002
1.2.10.1    00001.00002.00010.00001
1.10.1      00001.00010.00001
1.30.6      00001.00030.00006
1.30.10.1   00001.00030.00010.00001
1.111.1.2   00001.00111.00001.00002
2           00002
2.1.6       00002.00001.00006
3.30.6      00003.00030.00006
21.1        00021.00001
16 июн 17, 07:48    [20568924]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Flar
Member

Откуда:
Сообщений: 8
Добрый Э - Эх,
Понял. Спасибо за ликбез.
16 июн 17, 08:36    [20568978]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Flar,

А в сиквеле можно так:
with t 
as 
    (
      select '1.1.1.2'   k union all
      select '1.30.10.1' k union all
      select '1.30.6'    k union all
      select '1.111.1.2' k union all
      select '1.2.10.1'  k union all
      select '3.30.6'    k union all
      select '2.1.6'     k union all
      select '21.1'      k union all
      select '1.10.1'    k union all
      select '2'         k 
    )
select k
from (
select k,
	convert(int, PARSENAME(k, 1)) as k1,
	convert(int, PARSENAME(k, 2)) as k2,
	convert(int, PARSENAME(k, 3)) as k3,
	convert(int, PARSENAME(k, 4)) as k4
  from t
) t
order by k1,k2,k3,k4
16 июн 17, 08:59    [20569028]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Flar
Member

Откуда:
Сообщений: 8
alexeyvg,

благодарю. пошел хранимку тиранить...
16 июн 17, 09:37    [20569078]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Flar
Member

Откуда:
Сообщений: 8
alexeyvg,

Мда. Видимо совсем туп.
Что сделать - я понял, но вот с реализацией беда возникла.
НЕ могу понять как свою реализацию выборки:
select 
 data.strid 
,data.level
,data.card
,data.number --искомое поле
,analytical.code as analytical_code
,card.name as card_name
,card.number as card_number
,obj.name as card_type
,data.prognoz
,period.code as period_code
,period.name as period_name
,source.code as source_code
,source.name as source_name
,source.sort as source_sort
,'' as executor
,'' as player
from #result data
inner join v_gmp_doc card on data.card=card.id
inner join dbo.get_element_cl('analytical') analytical on card.tgt=analytical.id
inner join entityobjects obj on card.documentid=obj.id 
inner join dbo.get_element_cl('period') period on data.period=period.id
inner join (select id,code,name,attr.value('/xml[1]/sort[1]','int') as sort from dbo.get_element_cl('source') s ) source on data.source=source.id
order by number --искомое поле


скрестить с выше Вами представленным алгоритмом...
16 июн 17, 09:54    [20569122]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
alexeyvg, но так же не работает.

Надо же "разярд" учитывать.
16 июн 17, 10:56    [20569353]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
*разряд
16 июн 17, 10:57    [20569355]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
with t
as
(
select '2' k union all
select '2.0.1' k union all
select '1.10' k

)
select *
from (
select k,
convert(int, PARSENAME(k, 1)) as k1,
convert(int, PARSENAME(k, 2)) as k2,
convert(int, PARSENAME(k, 3)) as k3,
convert(int, PARSENAME(k, 4)) as k4
from t
) t
ORDER BY k4,k3,k2,k1[src][/SRC]
Разве не должно быть
1.10
2
2.0.1

?
16 июн 17, 11:02    [20569371]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
aleks2
Guest
Flar
aleks2,
ну первый вариант понятен. тоже о нем думал.
по второму - он тоже реализуем, но вопрос - как он мне поможет? я могу убрать "."
соответственно выборка у меня будет выглядеть:
13111
131110
13112
13113
13114
13115

Оно понятно, что при стандартной сортировке у меня автоматом элемент "131110" ускачет в низ. но в итоге при полной выборке у меня результат будет выглядеть след. образом:
12111
12112
12113
12114
12115
13111
13112
13113
13114
13115
121110
131110

а это не то, что нужно. или я не прав?


Шо за бредятина?

1.3.1.1.10 разлагается на 5 (пять) номеров.
16 июн 17, 13:49    [20570060]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
вообщем пятничный мозг родил функцию, я в этом не очень так что by desing :)


CREATE FUNCTION dbo.fn_CodeModify
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
	RETURN 
	(  	SELECT 
			STUFF((
				SELECT @Delimiter +Item as [text()]
				FROM 	
				(
					SELECT [Item] = RIGHT('00000' + y.i.value('(./text())[1]', 'nvarchar(4000)'), 5) 
					FROM 
					( 
						SELECT x = CONVERT(XML, '<i>' 
						+ REPLACE(@List, @Delimiter, '</i><i>') 
						+ '</i>').query('.')
					) AS a 
					CROSS APPLY x.nodes('i') AS y(i)				
				) as x
				WHERE 
					Item IS NOT NULL
			 for xml path('') ), 1, 2,'')  Item
		
	);
GO

with t 
as 
(
select '2' k union all
select '2.0.1' k union all
select '1.10' k 
)
SELECT t.*, b.*
FROM t
CROSS APPLY 
	dbo.fn_CodeModify(t.k,'.')  b
ORDER BY b.Item
16 июн 17, 14:19    [20570140]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
Flar, - 14862331
16 июн 17, 14:19    [20570142]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
sql_user2
Member

Откуда:
Сообщений: 382
Flar
Пытался по-разному...


ALTER sql.ru SET friday_node ON;


Зависит ли натуральность сортировки от ориентации сервера?
16 июн 17, 14:37    [20570206]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
sql_user2
Flar
Пытался по-разному...


ALTER sql.ru SET friday_node ON;


Зависит ли натуральность сортировки от ориентации сервера?
из всей темы только одно знакомое слово попалось?
16 июн 17, 14:40    [20570223]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
invm
declare @t table(p varchar(100))

insert into @t values ('1.1'),('1.10'),('1.2'),('1.10.1'),('1.10.2'),('1.10.2.1'),('1.10.2.2')

select p
from @t
order by
 cast('/' + replace(p, '.', '/') + '/' as hierarchyid);



Крутой ход! Вот как натренировать голову чтоб не забывать про hierarchyid каждые два месяца? :D
16 июн 17, 16:42    [20570648]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Cammomile
Крутой ход! Вот как натренировать голову чтоб не забывать про hierarchyid каждые два месяца? :D

если коды с буквами то уже не то...
16 июн 17, 16:47    [20570658]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
Flar
Мда. Видимо совсем туп.
Не, это я туп :-(
PARSENAME тут не подходит, она с конца строки разбирает.
16 июн 17, 17:03    [20570707]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
автор
Не, это я туп :-(
Да ну что вы, коллега! Просто пятница.
16 июн 17, 17:06    [20570716]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Flar
Member

Откуда:
Сообщений: 8
Пытаюсь воспользоваться методом:
declare @t table(p varchar(100))

insert into @t values ('1.1'),('1.10'),('1.2'),('1.10.1'),('1.10.2'),('1.10.2.1'),('1.10.2.2')

select p
from @t
order by
 cast('/' + replace(p, '.', '/') + '/' as hierarchyid);

Результат хранимки выстраивается верно. Но есть большой нюанс, что это поле необходимо вывести в результат запроса. Когда пытаюсь это сделать следующим способом:

select 
 data.strid 
,data.level
,data.card
,data.number
,analytical.code as analytical_code
,card.name as card_name
,card.number as card_number
,obj.name as card_type
,data.prognoz
,period.code as period_code
,period.name as period_name
,source.code as source_code
,source.name as source_name
,source.sort as source_sort
,cast('/' + replace(data.number, '.', '/') + '/' as hierarchyid)
,'' as executor
,'' as player
from #result data
inner join v_gmp_doc card on data.card=card.id
inner join dbo.get_element_cl('analytical') analytical on card.tgt=analytical.id
inner join entityobjects obj on card.documentid=obj.id 
inner join dbo.get_element_cl('period') period on data.period=period.id
inner join (select id,code,name,attr.value('/xml[1]/sort[1]','int') as sort from dbo.get_element_cl('source') s ) source on data.source=source.id
order by cast('/' + replace(data.number, '.', '/') + '/' as hierarchyid)



То получаю ошибку:
Тип "Microsoft.SqlServer.Types.SqlHierarchyId" в сборке "Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" не помечен как сериализуемый.
19 июн 17, 03:04    [20573546]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5960
Flar
Пытаюсь воспользоваться методом:
Результат хранимки выстраивается верно. Но есть большой нюанс, что это поле необходимо вывести в результат запроса. Когда пытаюсь это сделать следующим способом:
То получаю ошибку:
Тип "Microsoft.SqlServer.Types.SqlHierarchyId" в сборке "Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" не помечен как сериализуемый.

Прикастовать поле запроса с hierarchyid к varchar (или к varbinary - что уж вам там на выходе нужно).
19 июн 17, 06:01    [20573566]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Flar
Member

Откуда:
Сообщений: 8
Сон Веры Павловны,

да мне как бы как раз и нужен в hierarchyid. В чаровском он у меня есть в виде поля number
19 июн 17, 07:13    [20573582]     Ответить | Цитировать Сообщить модератору
 Re: Натуральная сортировка  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Flar,
тип поддерживается начиная с SQL 2012.
19 июн 17, 11:06    [20573953]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить