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

Откуда:
Сообщений: 27
Всем доброго дня.
Есть вот такой запрос:
DECLARE 
@str VARCHAR (400)='MTA: All Agency Utility Portfolio|MTA: All Agency Utility Portfolio|MTA: All Agency Utility Portfolio|MNCRR|Electric|Constellation|1-ELVHNH|'

;WITH cte
AS(
SELECT value AS val, ROW_NUMBER() OVER (ORDER BY(SELECT 0)) AS rn
FROM  STRING_SPLIT(@str,'|'))
SELECT cte.val,rn
INTO #t
FROM cte 


результатом которого выводится вот такой список:
 
MTA: All Agency Utility Portfolio 1
MTA: All Agency Utility Portfolio 2
MTA: All Agency Utility Portfolio 3
MNCRR 4
Electric 5
Constellation 6
1-ELVHNH 7
8


Суть дальнейших действий состоит в том, чтобы убрать повторяющиеся строки, но при использование,, предположим, Distinct или Group By, сервер по умолчанию применяет сортировку по алфавиту, а это, в данном случае, совершенно не нужно.

Нужно получить что-то типа:

MTA: All Agency Utility Portfolio 1
MNCRR 2
Electric 3
Constellation 4
1-ELVHNH 5
6


Подскажите, пожалуйста, как это можно реализовать?
Заранее спасибо.
1 ноя 18, 11:47    [21721263]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20207
Какая разница, сортировать по алфавиту или по рандому?
1 ноя 18, 11:50    [21721273]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
lopster
Member

Откуда:
Сообщений: 27
Akina, Разница заключается в том, что это уровни иерархии, и чтобы их дальше вывести в столбцы, их порядок по отношению к уровню не должен быть нарушен.
1 ноя 18, 12:00    [21721290]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
lopster
Akina, Разница заключается в том, что это уровни иерархии, и чтобы их дальше вывести в столбцы, их порядок по отношению к уровню не должен быть нарушен.
Чтобы был определённый порядок, должно быть поле, задающее этот порядок. Оно у вас есть?

Дополните OVER() предложением PARTITION BY [value] и оставьте в результате строки с rn=1
1 ноя 18, 12:18    [21721330]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
lopster
Member

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

так порядок и задается rn.
Я пробовал это сделать точно так, как вы написали. Не работает.

Запрос из поста можно запустить в любой студии с версией сервера от 2016 и проверить.
1 ноя 18, 12:56    [21721402]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
Lissaro
Member

Откуда: Москва
Сообщений: 8
Может так:
DECLARE 
@str VARCHAR (400)='MTA: All Agency Utility Portfolio|MTA: All Agency Utility Portfolio|MTA: All Agency Utility Portfolio|MNCRR|Electric|Constellation|1-ELVHNH|'

;WITH cte
AS(
SELECT value AS val, ROW_NUMBER() OVER (ORDER BY(SELECT 0)) AS rn
FROM  STRING_SPLIT(@str,'|'))
SELECT cte.val,rn,MIN(rn) OVER (PARTITION BY val) AS ff
INTO #t
FROM cte

 SELECT DISTINCT  val, ff
 FROM   #t
 ORDER BY ff
1 ноя 18, 13:04    [21721414]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
lopster
iap,

так порядок и задается rn.
Я пробовал это сделать точно так, как вы написали. Не работает.

Запрос из поста можно запустить в любой студии с версией сервера от 2016 и проверить.
Порядок у вас никак не задаётся! И rn здесь совершенно не при чём.
DECLARE 
@str VARCHAR (400)='MTA: All Agency Utility Portfolio|MTA: All Agency Utility Portfolio|MTA: All Agency Utility Portfolio|MNCRR|Electric|Constellation|1-ELVHNH|';

WITH cte AS
(
 SELECT
  val=value
, rn=ROW_NUMBER() OVER (ORDER BY 1/0)
, n=ROW_NUMBER() OVER (PARTITION BY [value] ORDER BY 1/0)
FROM  STRING_SPLIT(@str,'|')
)
SELECT val,rn
FROM cte
WHERE n=1
ORDER BY rn;
1 ноя 18, 14:30    [21721562]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
uaggster
Member

Откуда:
Сообщений: 767
Не пользуйтесь для этой задачи STRING_SPLIT.
Напишите функцию разделения, где будет явно указана позиция item в строке.
Через xml например, или clr-ку напишите.
1 ноя 18, 14:39    [21721585]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
lopster
Member

Откуда:
Сообщений: 27
iap,
Это решение получается только для частного случая.
Если я, допустим, меняю строку:
DECLARE 
@str VARCHAR (400)='bdhg|bdhg|bdhg|aert|kiop|bnmk|1-ELVHNH|';

WITH cte AS
(
 SELECT
  val=value
, rn=ROW_NUMBER() OVER (ORDER BY 1/0)
, n=ROW_NUMBER() OVER (PARTITION BY [value] ORDER BY 1/0)
FROM  STRING_SPLIT(@str,'|')
)
SELECT val,rn
FROM cte
WHERE n=1
ORDER BY rn;

, то результат будет один фиг по алфавиту.

val rn
1
1-ELVHNH 2
aert 3
bdhg 4
bnmk 7
kiop 8
1 ноя 18, 14:45    [21721591]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
Владислав Колосов
Member

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

значит требуется промежуточное сохранения нумерованного списка.
1 ноя 18, 15:33    [21721660]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7401
Или табличную CLR допилите для устранения дублей, которая делает split. В инете можно код найти.
1 ноя 18, 15:35    [21721665]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
lopster,

DECLARE 
@str VARCHAR (400)='MTA: All Agency Utility Portfolio|MTA: All Agency Utility Portfolio|MTA: All Agency Utility Portfolio|MNCRR|Electric|Constellation|1-ELVHNH|';

with a as
(
 select top (cast(0x7fffffff as int))
  value,
  row_number() over (order by (select 1)) as rn
 from
  STRING_SPLIT(@str,'|')
 order by
  row_number() over (order by (select 1))
)
select value, row_number() over (order by min(rn)) from a group by value;
1 ноя 18, 15:35    [21721667]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
Еще вариант
select
 value,
 row_number() over (order by charindex('|' + value + '|', '|' + @str + '|'))
from
 STRING_SPLIT(@str,'|')
group by
 value;
1 ноя 18, 16:22    [21721741]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
uaggster
Не пользуйтесь для этой задачи STRING_SPLIT.
Напишите функцию разделения, где будет явно указана позиция item в строке.
Через xml например, или clr-ку напишите.
Всё давно написано:
Функция, которая делит строку на слова
1 ноя 18, 16:46    [21721762]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
Владислав Колосов
значит требуется промежуточное сохранения нумерованного списка.


Правильно ли я понял, что это особенность запросов с подзапросами, что нумерация ROW_NUMBER может примениться не внутри подзапроса/сте, а позже, при окончательной выборке, как происходит у ТС?
1 ноя 18, 21:29    [21721965]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
Владислав Колосов
Member

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

Сервер не рассматривает CTE как отдельно выполняющиеся запросы, а строит общий план выполнения. Поэтому и возникают проблемы с сортировками казалось бы верного выражения.
2 ноя 18, 11:09    [21722281]     Ответить | Цитировать Сообщить модератору
 Re: Удаление строк с повторяющимися значениями.  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
Владислав Колосов
PizzaPizza,

Сервер не рассматривает CTE как отдельно выполняющиеся запросы, а строит общий план выполнения. Поэтому и возникают проблемы с сортировками казалось бы верного выражения.


Ага. То есть в данном случае сервер решил, что ему удобнее и проще так выполнить запрос. А есть ли хинты, помимо выноса в табличную переменную или временную таблицу, что бы форсить выполнение row_number внутри подзапроса в случае ТС?
2 ноя 18, 20:02    [21722897]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить