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

Откуда: Норильск
Сообщений: 940
Всем доброго дня. Есть 2 запроса (в первом выбираются корневые пункты, а во втором их подпункты, таблицы разные)

1.
SELECT short_name FROM table1 where short_name 
in ('А', 'Б', 'Б.1', 'Б.7', 'Б.8', 'Б.10', 'Г', 'Г.1', 'Г.2', 'Г.3', 'Д')


Возвращает такой результат:

А
Б
Б.1
Б.7
Б.8
Б.10
Г
Г.1
Г.2
Г.3
Д


2.
SELECT short_name1 FROM table2 WHERE short_name1
in ('Б.1.1', 'Б.1.3', 'Б.1.10', 'Г.1.1')

Возвращает такой результат:

Б.1.1
Б.1.3
Б.1.10
Г.1.1


Далее эти 2 запроса необходимо объединить и получить конечный правильный результат, где сортировка будет сначала по корневым пунктам из 1 таблицы, далее по их подпунктам из 2 таблицы. Результат должен выглядеть так:

А
Б
Б.1
Б.1.1
Б.1.3
Б.1.10
Б.7
Б.8
Б.10
Г
Г.1
Г.1.1
Г.2
Г.3
Д


Ниже прилагаю скрипт двух таблиц

DECLARE @t TABLE (short_name VARCHAR(10))
INSERT INTO @t 
SELECT 'А'
UNION all
SELECT 'Б'
UNION all
SELECT 'Б.1'
UNION all
SELECT 'Б.2'
UNION all
SELECT 'Б.3'
UNION all
SELECT 'Б.7'
UNION all
SELECT 'Б.8'
UNION all
SELECT 'Б.10'
UNION all
SELECT 'Г'
UNION all
SELECT 'Г.1'
UNION all
SELECT 'Г.2'
UNION all
SELECT 'Г.3'
UNION all
SELECT 'Д'

DECLARE @t1 TABLE (short_name1 VARCHAR(10))
INSERT INTO @t1
SELECT 'Б.1.1'
UNION all
SELECT 'Б.1.3'
UNION all
SELECT 'Б.1.10'
UNION all
SELECT 'Г.1.1'


Помогите пожалуйста. Заранее спасибо!!!
26 ноя 14, 07:49    [16905772]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21241
Напишите функцию нормализации данных для такого ранжирования. Если ориентироваться на приведённый шаблон данных - то в формат "#.00.00". Используйте эту функцию в кляузе ORDER BY.
Либо нормализуйте данные по месту хранения.
26 ноя 14, 09:04    [16905928]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
Настенька
Member

Откуда: Норильск
Сообщений: 940
Была идея написать курсор, но может, есть способы обойтись без него.
26 ноя 14, 09:10    [16905946]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Настенька
1.
SELECT short_name FROM table1 where short_name 
in ('А', 'Б', 'Б.1', 'Б.7', 'Б.8', 'Б.10', 'Г', 'Г.1', 'Г.2', 'Г.3', 'Д')



Возвращает такой результат:

А
Б
Б.1
Б.7
Б.8
Б.10
Г
Г.1
Г.2
Г.3
Д

1. Нет никакой сортировки, если отсутствует ORDER BY
2. Для сортировки чисел, которые хранятся почему то как строки, придется либо выравнивать эти строки слева, либо преобразовать строки в числа
3. А иерархию лучше всего и хранить как иерархию
26 ноя 14, 09:39    [16906109]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Настенька,

попробуйте изучить тип данных hierarchyid. Как раз для данных такого формата.
26 ноя 14, 13:08    [16907473]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
сортировка АБВГД
Guest
Настенька,
CREATE FUNCTION [dbo].[Str_Split] (@string varchar(max), @delimeter varchar(255))  
RETURNS bigint
 AS  
BEGIN 

declare @retArray TABLE (id smallint Primary key IDENTITY(6, -2), value varchar(max))

declare @i int = 1, @previ int = 1, @res bigint

while 1=1 
begin

select @i= CHARINDEX(@delimeter,@string,@i)
if (@i>0)
begin
	if @i-@previ>0 and len(ltrim(rtrim(substring(@string,@previ, @i-@previ))))>0
		insert @retArray(value) select ltrim(rtrim(substring(@string,@previ, @i-@previ)))
	select @previ=@i+len(replace(@delimeter,' ','/')), @i=@i+len(replace(@delimeter,' ','/'))

end
else 
begin
	if len(@string)+1-@previ>0 and len(ltrim(rtrim(substring(@string,@previ, len(@string)+1-@previ))))>0
		insert @retArray(value) select  ltrim(rtrim(substring(@string,@previ, len(@string)+1-@previ)))
	break
end
end

update r
set r.value = ASCII(LEFT(r.value, 1)) - ASCII('А') + 1
from @retArray r 
where r.id = (select MAX(r2.id) from @retArray r2)

set @res = (select sum(r.value * POWER(10, r.id)) from @retArray r)

return @res
end


select s.*, dbo.[Str_Split](s.short_name, '.') as sort
from 
(	select * 
	from @t t
	union all
	select t1.* 
	from @t1 t1
) s
order by dbo.[Str_Split](s.short_name, '.')
26 ноя 14, 14:22    [16908065]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
RIBor
Member

Откуда:
Сообщений: 43
Может так?

select
	short_name
from (
	select short_name from #t
	union 
	select short_name1 from #t1
) a
order by 
	left(short_name, 1), 
	convert(decimal(5,2), iif(substring(short_name, 3, 50) = '', '0', substring(short_name, 3, 50)))
26 ноя 14, 16:24    [16909217]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
Настенька
Member

Откуда: Норильск
Сообщений: 940
Спасибо за функцию.

RIBor, этот код не работает, ругается на:

Msg 170, Level 15, State 1, Line 48
Line 48: Incorrect syntax near '='.
27 ноя 14, 06:35    [16911632]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
1. С помощью hierarchyid
select
 t.short_name
from
 (
  select short_name from @t
  union all
  select short_name1 from @t1
 ) t cross apply
 (select nullif(charindex('.', t.short_name), 0)) a(p)
order by
 left(t.short_name, isnull(a.p, 1000000)), cast('/' + substring(t.short_name, a.p + 1, 1000000) + '/' as hierarchyid);

2.Если гарантированно не более 4-х уровней
select
 t.short_name
from
 (
  select short_name from @t
  union all
  select short_name1 from @t1
 ) t cross apply
 (select reverse(t.short_name)) a(s) cross apply
 (select reverse(parsename(a.s, 1)), reverse(parsename(a.s, 2)), reverse(parsename(a.s, 3)), reverse(parsename(a.s, 4))) b(s1, s2, s3, s4)
order by
 b.s1, cast(b.s2 as int), cast(b.s3 as int), cast(b.s4 as int);
27 ноя 14, 07:58    [16911688]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
Настенька
Member

Откуда: Норильск
Сообщений: 940
Извините пожалуйста, забыла указать, у меня MS SQL Server 2000, там этой функции cross apply нет (и остальных тоже). :(
27 ноя 14, 08:02    [16911693]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
OmgFail
Member

Откуда:
Сообщений: 4
Настенька,

with cte (short_name, x)
as
(
select short_name, convert(xml, '<r>'+replace(short_name, '.', '</r><r>') + '</r>') from @t
union all
select short_name1, convert(xml, '<r>' + replace(short_name1, '.', '</r><r>') + '</r>') from @t1
)

select short_name 
from cte
order by
  x.value('(r)[1]', 'varchar(10)') ,
  x.value('(r)[2]', 'int'),
  x.value('(r)[3]', 'int')
27 ноя 14, 08:04    [16911695]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
DECLARE @t TABLE (short_name VARCHAR(10))
INSERT INTO @t 
SELECT 'А'
UNION all
SELECT 'Б'
UNION all
SELECT 'Б.1'
UNION all
SELECT 'Б.2'
UNION all
SELECT 'Б.3'
UNION all
SELECT 'Б.7'
UNION all
SELECT 'Б.8'
UNION all
SELECT 'Б.10'
UNION all
SELECT 'Г'
UNION all
SELECT 'Г.1'
UNION all
SELECT 'Г.2'
UNION all
SELECT 'Г.3'
UNION all
SELECT 'Д'

DECLARE @t1 TABLE (short_name1 VARCHAR(10))
INSERT INTO @t1
SELECT 'Б.1.1'
UNION all
SELECT 'Б.1.3'
UNION all
SELECT 'Б.1.10'
UNION all
SELECT 'Г.1.1'


SELECT short_name
FROM
(SELECT short_name,n1,n2,n3
,CASE WHEN n1 = 0 THEN short_name ELSE SUBSTRING(short_name,1,n1-1) END AS c1
,CASE WHEN n1 = 0 AND n2 = 0 THEN '' 
	  WHEN n1 > 0 AND n2 = 0 THEN SUBSTRING(short_name,n1+1,LEN(short_name)-n1)
	  ELSE SUBSTRING(short_name,n1+1,n2-n1-1)	END AS c2
,CASE WHEN n2 = 0 AND n3 =0 THEN '' 
	  WHEN n2 > 0 AND n3 =0 THEN SUBSTRING(short_name,n2+1,LEN(short_name)-n2)
	  ELSE SUBSTRING(short_name,n2+1,n3-n2-1)	END AS c3
FROM
(select short_name
,CHARINDEX('.',short_name,0) as n1
,CHARINDEX('.',short_name,ISNULL(NULLIF(CHARINDEX('.',short_name,0),0),LEN(short_name))+1) as n2
,CHARINDEX('.',short_name,ISNULL(NULLIF(CHARINDEX('.',short_name,ISNULL(NULLIF(CHARINDEX('.',short_name,0),0),LEN(short_name))+1),0),LEN(short_name))+1) as n3
from
(select * 
from @t 
union all
select * 
from @t1)n)c)t
ORDER BY c1,CAST(c2 as int),CAST(c3 as int)
27 ноя 14, 10:14    [16912115]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
Настенька
Member

Откуда: Норильск
Сообщений: 940
LexusR, это просто гениально, завидую вашим познаниям в SQL. Все работает четко и быстро. ОГРОМНОЕ ВАМ СПАСИБО И БОЛЬШОЙ ПРИВЕТ СИБИРЯКАМ!!!
28 ноя 14, 08:31    [16917047]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
alexeyvg
Member

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

Можно покороче написать, вспомнив о PARSENAME
select short_name
from (
	select	short_name
			,PARSENAME(short_name, 4) as n0
			,PARSENAME(short_name, 3) as n1
			,PARSENAME(short_name, 2) as n2
			,PARSENAME(short_name, 1) as n3
	from (
		select * 
		from @t 
		union all
		select * 
		from @t1
	) n
) c
order by right('0000000' + n0, 8), 
		right('0000000' + n1, 8), 
		right('0000000' + n2, 8), 
		right('0000000' + n3, 8)
28 ноя 14, 09:31    [16917212]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
alexeyvg
Можно покороче написать, вспомнив о PARSENAME


согласен но если уровней не больше 4
28 ноя 14, 09:47    [16917278]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
alexeyvg
Можно покороче написать, вспомнив о PARSENAME

да и запрос нужно немного подкрутить так как результат у вас несколько не тот
28 ноя 14, 10:00    [16917357]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка строковых и цифровых значений из двух таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
LexusR
alexeyvg
Можно покороче написать, вспомнив о PARSENAME


согласен но если уровней не больше 4

да и запрос нужно немного подкрутить так как результат у вас несколько не тот
Ага, согласен.
select short_name
from (
	select	short_name
			,isnull('0000000' + PARSENAME(short_name, 4), '') as n0
			,isnull('0000000' + PARSENAME(short_name, 3), '') as n1
			,isnull('0000000' + PARSENAME(short_name, 2), '') as n2
			,isnull('0000000' + PARSENAME(short_name, 1), '') as n3
	from (
		select * 
		from @t 
		union all
		select * 
		from @t1
	) n
) c
order by right(n0, 8) + right(n1, 8) + right(n2, 8) + right(n3, 8)
28 ноя 14, 10:29    [16917548]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить