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

Откуда:
Сообщений: 734
Вопрос в общем то озвучен тут:
https://www.sql.ru/forum/330253/sortirovka-punktov-1-1-1-1-2-1-10-i-t-d
у меня аналогичная проблема, но для MS SQL:
как правильно отсортировать пункты, которые, естественно в текстовом виде?
т.е. проблема когда после 1.1 идет 1.10 а не 1.2
20 сен 13, 01:03    [14862033]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
nerv,
declare @t table(p varchar(100))

insert into @t values ('1.1'),('1.10'),('1.2')

select p
from @t
order by CAST(LEFT(p,CHARINDEX('.',p)-1) as int), CAST(RIGHT(p,DATALENGTH(p)-CHARINDEX('.',p)) as int)
20 сен 13, 04:25    [14862117]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
nerv,

Если губина вложенности пунктов не больше четырех и использутся только числа, то можно использовать PARSENAME
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(PARSENAME([p], 4) AS INT), CAST(PARSENAME([p], 3) AS INT), CAST(PARSENAME([p], 2) AS INT), CAST(PARSENAME([p], 1) AS INT)
20 сен 13, 07:48    [14862187]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
nerv
Member

Откуда:
Сообщений: 734
Ruuu, Да, ваш приме работает только с уровнем вложения 2...
20 сен 13, 08:38    [14862255]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
nerv
Member

Откуда:
Сообщений: 734
Guf, гениально конечно. Несколько раз прочитал о PARSENAME, так и не понял, как ваш приме работает.
Уровень вложения пока 5 (из приказа), с 5 ваш пример так же работает.
Будет ли уровень вложения со временем больше - возможно, предугадать невозможно, это же Министерство, как они приказ переделают в следующий раз - не известно.
А если скажем уровень вложения (на перспективу так сказать) сделать до 6, это просто надо будет CAST(PARSENAME([p], 6) AS INT), CAST(PARSENAME([p], 5) AS INT) добавить?
И сильно ли эти два выражения затормозят выборку, если 6 уровня пока нет?
20 сен 13, 08:42    [14862260]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Guf
nerv,

Если губина вложенности пунктов не больше четырех и использутся только числа, то можно использовать PARSENAME
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(PARSENAME([p], 4) AS INT), CAST(PARSENAME([p], 3) AS INT), CAST(PARSENAME([p], 2) AS INT), CAST(PARSENAME([p], 1) AS INT)
Идея интересная, только нужно допилить:
declare @t table(p varchar(100))

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

select p
from @t
ORDER BY CAST(REVERSE(PARSENAME(REVERSE([p]), 1)) AS INT),
	CAST(REVERSE(PARSENAME(REVERSE([p]), 2)) AS INT),
	CAST(REVERSE(PARSENAME(REVERSE([p]), 3)) AS INT),
	CAST(REVERSE(PARSENAME(REVERSE([p]), 4)) AS INT)
20 сен 13, 08:44    [14862267]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
nerv
Member

Откуда:
Сообщений: 734
Ruuu, Странный "допил"
У вас пункт 1.1.1.4. оказывается раньше пункта 1.
Пример Guf работает корректно.
20 сен 13, 09:16    [14862330]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
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);
20 сен 13, 09:17    [14862331]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
nerv
Member

Откуда:
Сообщений: 734
не, получается оба примера не работают.
Уже на уровне вложения 4, например "1.1.1.4" все выражения
CAST(REVERSE(PARSENAME(REVERSE([p]), 1)) AS INT),
CAST(REVERSE(PARSENAME(REVERSE([p]), 2)) AS INT),
CAST(REVERSE(PARSENAME(REVERSE([p]), 3)) AS INT),
CAST(REVERSE(PARSENAME(REVERSE([p]), 4)) AS INT)
выдают Null. У меня вложенность 5.
20 сен 13, 09:26    [14862359]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
nerv
Ruuu, Странный "допил"
У вас пункт 1.1.1.4. оказывается раньше пункта 1.
Пример Guf работает корректно.
Да ну?
declare @t table(p varchar(100))

insert into @t values ('1'),('1.1'),('1.10'),('1.2'),('1.1.4'),('1.1.1.4'),('1.10.1'),('1.10.2'),('1.8.1'),('1.10.2.1'),('1.10.2.2'),('2.3'),('2.1.1')

select p Guf
from @t
ORDER BY CAST(PARSENAME([p], 4) AS INT), CAST(PARSENAME([p], 3) AS INT), CAST(PARSENAME([p], 2) AS INT), CAST(PARSENAME([p], 1) AS INT)

select p GufEditedByRuuu
from @t
ORDER BY CAST(REVERSE(PARSENAME(REVERSE([p]), 1)) AS INT),
	CAST(REVERSE(PARSENAME(REVERSE([p]), 2)) AS INT),
	CAST(REVERSE(PARSENAME(REVERSE([p]), 3)) AS INT),
	CAST(REVERSE(PARSENAME(REVERSE([p]), 4)) AS INT)
Хотя, если вам позволяет версия сервера, то invm уже привел решение лучше.
20 сен 13, 09:27    [14862361]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
Glory
Member

Откуда:
Сообщений: 104751
nerv
не, получается оба примера не работают.
Уже на уровне вложения 4, например "1.1.1.4" все выражения
CAST(REVERSE(PARSENAME(REVERSE([p]), 1)) AS INT),
CAST(REVERSE(PARSENAME(REVERSE([p]), 2)) AS INT),
CAST(REVERSE(PARSENAME(REVERSE([p]), 3)) AS INT),
CAST(REVERSE(PARSENAME(REVERSE([p]), 4)) AS INT)
выдают Null.

Работает
declare @p varchar(20)
set @p = '1.1.1.4'
select    CAST(REVERSE(PARSENAME(REVERSE(@p), 1)) AS INT),
 	CAST(REVERSE(PARSENAME(REVERSE(@p), 2)) AS INT),
	CAST(REVERSE(PARSENAME(REVERSE(@p), 3)) AS INT),
	CAST(REVERSE(PARSENAME(REVERSE(@p), 4)) AS INT)
20 сен 13, 09:29    [14862365]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
nerv
Member

Откуда:
Сообщений: 734
invm, требуется совместимость с MS SQL 2005 (
20 сен 13, 09:30    [14862367]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
nerv
Member

Откуда:
Сообщений: 734
Glory,
да, забыл уточнить, на конце точка обязательна для всех пунктов, то есть в приказе "1.", "1.1.1.4."
поэтому из-за последней точки не работает.
20 сен 13, 09:32    [14862374]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
nerv,

Неее, в таком случае у Вас ничего не получится. Значение второго параметра у парснейм сторого от 1 до 4.
Нужно делать либо отдельное поле для сортировки (как частный случай пункты так ('001.001'),('001.010'),('001.002')), либо делать нормальное дерево, либо исползовать вариант invm.
20 сен 13, 09:36    [14862392]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
Glory
Member

Откуда:
Сообщений: 104751
nerv
да, забыл уточнить, на конце точка обязательна для всех пунктов, то есть в приказе "1.", "1.1.1.4."
поэтому из-за последней точки не работает.

Вам про это и сказали. Уровень вложенности для PARSENAME - это количество точек, а не чисел
20 сен 13, 09:37    [14862397]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
nerv
Member

Откуда:
Сообщений: 734
Ruuu, мой случай:
insert into @t values ('1.'),('1.1.'),('1.10.'),('1.2.'),('1.1.4.'),('1.1.1.4.'),('1.10.1.'),('1.10.2.'),('1.8.1.'),('1.10.2.1.'),('1.10.2.2.'),('2.3.'),('2.1.1.')
поэтому и не работает. А еще встречается '1.1.1.4.1.' и '1.1.1.4.2.'
20 сен 13, 09:37    [14862398]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
nerv
Member

Откуда:
Сообщений: 734
Хорошо. будем думать.
В одном месте оно отображается в виде дерева, но в другом редакторе нужно линейно представить в гриде, вариант invm не приемлем, так как MS SQL 2005 требуется, вводить новое поле с поддержкой на триггерах наверное единственный вариант. Всем спасибо.
20 сен 13, 09:42    [14862411]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
use tempdb;
go

create function dbo.fnNormalize
(
 @s varchar(max)
)
returns varchar(max)
with schemabinding
as
begin
 declare @result varchar(max);

 with t as
 (select @s + '.' as source),
 x as
 (
  select
   cast(1 as bigint) as s, charindex('.', source, 1) - 1 as l, source
  from
   t
 
  union all
 
  select
   a.v as s, charindex('.', x.source, a.v) - a.v as l, x.source
  from
   x cross apply
   (select x.s + x.l + 1) a(v)
  where
   charindex('.', x.source, a.v) > 0
 )
 select
  @result = (select right(space(10) + substring(source, s, l), 10) from x order by s for xml path(''));

 return @result;
end;
go

create table dbo.t (p varchar(100), s as dbo.fnNormalize(p) persisted);

insert into dbo.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 dbo.t order by s;
go

drop table dbo.t;
drop function dbo.fnNormalize;
go
20 сен 13, 10:19    [14862547]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по пунктам  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
вот вам прототип, если понравиться допиливайте под свои нужды.
declare @t table(id int,p varchar(100))

insert into @t values (1,'4.1.9.6.7.3.4.6.4.5.8.6.4.4.45.4.45.56.5.4.45.34'),(2,'4.1.3.'),(3,'1.2.4.')
select CHARINDEX('.',REVERSE('1.2.4.'),2)-1 ,(substring('1.2.4.',LEN('1.2.4.')-CHARINDEX('.',REVERSE('1.2.4.'),1)+1,LEN('1.2.4.'))) 

;with parseNumber as(
select	t.id
		,p
		,rp.val rev
		,startloc.val startloc
		,part.val part
		,1 lev
		,replicate('0',3-LEN(part2.val))+    part2.val  numb
from	@t t
		cross apply(select REVERSE(p)) rp(val)
		cross apply (select CHARINDEX('.',REVERSE(p),0)  )startloc(val)
		cross apply ( select SUBSTRING(rp.val,0,startloc.val)) part(val)
		cross apply (select cast(isnull(nullif(part.val,''),'0')  as varchar (32))) part2(val)
union all
select 
		t.id
		,p
		,rp.val rev
		,startloc.val startloc
		,part.val
		,lev+1 
		,replicate('0',3-LEN(part2.val))+    part2.val+numb  numb
from	parseNumber t
		cross apply(select REVERSE(p)) rp(val)
		cross apply (select CHARINDEX('.',REVERSE(p),t.startloc+1)  )startloc(val)
		cross apply ( select  case when startloc.val>0 then SUBSTRING(rp.val,startloc+1,startloc.val-1-startloc)
								else SUBSTRING(rp.val,startloc+1,LEN(rp.val)) end
								
		)
						 part(val)
		cross apply (select cast(isnull(nullif(part.val,''),'0') as varchar (32))) part2(val)				 
where startloc>0

)



, parseNumber2 as(
select MAX((cast(numb as float ) )) pnumber
		,id
		
from parseNumber
group by id)

select *
from parseNumber2
order by pnumber
20 сен 13, 13:09    [14863749]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить