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

Откуда: Самара
Сообщений: 70
День добрый! Не подскажете, как решить простейшую, на первый взгляд, задачку:
В таблице есть поле код и поле год

код год
а1 2012
а2 2012
а3 2012
б14 2014
б15 2014
б16 2014
а4 2011
а5 2011
а6 2011
ак100 2011
ак101 2011
ак102 2011
ак103 2000
ак104 2011
ак105 2011
вм1000 2017
вм1001 2017
вм1002 2017
абв55 2010

Код - сочетание букв и цифр. Букв от 1 до 4. Цифр от 1 до 5, для каждого буквенного обозначения идет по возрастающей. Год может быть разный для всех кодов или одинаковый.
Нужно получить из этой таблицы такую, по которой можно посмотреть года для каждого типа кодов (например, для "а" отдельно с а1 по а10000 /последней) в порядке возрастания, и с группировкой через -, и с отдельным выделением кодов новой строкой, у которых по порядку возрастания год другой, например:

код год
а1-а3 2012
а4-а6 2011
абв55 2010
ак100-ак102 2011
ак103 2000
ак104-ак105 2011
б14-б16 2014
вм1001-вм1002 2017

Или хотябы перечислением через запятую, Это возможно?

код год
а1, а2, а3 2012
а4, а5, а6 2011...

Версия SQL: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0
14 окт 13, 21:21    [14969574]     Ответить | Цитировать Сообщить модератору
 Re: Сложная или простая группировка с сортировкой  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
declare @t table ( code varchar(10), year int )
insert into @t
values 
  ( 'а1', 2012 )
, ( 'а2', 2012 )
, ( 'а3', 2012 )
, ( 'б14', 2014 )
, ( 'б15', 2014 )
, ( 'б16', 2014 )
, ( 'а4', 2011 )
, ( 'а5', 2011 )
, ( 'а6', 2011 )
, ( 'ак100', 2011 )
, ( 'ак101', 2011 )
, ( 'ак102', 2011 )
, ( 'ак103', 2000 )
, ( 'ак104', 2011 )
, ( 'ак105', 2011 )
, ( 'вм1000', 2017 )
, ( 'вм1001', 2017 )
, ( 'вм1002', 2017 )
, ( 'абв55', 2010 )

select min( code ) 
     + case when min( code ) = max( code ) 
            then '' 
            else '-' + max( code )
            end as [codes]
     , year 
  from @t
  group by year, left( code, patindex( '%[0-9]%', code ) - 1 )
  order by min( code )
14 окт 13, 21:46    [14969658]     Ответить | Цитировать Сообщить модератору
 Re: Сложная или простая группировка с сортировкой  [new]
Allllllllllex
Member

Откуда: Самара
Сообщений: 70
Таблицы в более читабельном виде
Таблица 1

код год
а1 2012
а2 2012
а3 2012
б14 2014
б15 2014
б16 2014
а4 2011
а5 2011
а6 2011
ак100 2011
ак101 2011
ак102 2011
ак103 2000
ак104 2011
ак105 2011
вм1000 2017
вм1001 2017
вм1002 2017
абв55 2010


Таблица 2

код год
а1-а3 2012
а4-а6 2011
абв55 2010
ак100-ак102 2011
ак103 2000
ак104-ак105 2011
б14-б16 2014
вм1001-вм1002 2017


Простая группировка, как Вы понимаете дает:

код год
ак100-ак105 2011
ак103 2000



вместо

код год
ак100-ак102 2011
ак103 2000
ак104-ак105 2011


Т.е. нарушается последовательность кодов по возрастанию (ак103 после ак105)
14 окт 13, 21:56    [14969697]     Ответить | Цитировать Сообщить модератору
 Re: Сложная или простая группировка с сортировкой  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
declare @t table ( code varchar(10), year int )
insert into @t
values 
  ( 'а1', 2012 )
, ( 'а2', 2012 )
, ( 'а3', 2012 )
, ( 'б14', 2014 )
, ( 'б15', 2014 )
, ( 'б16', 2014 )
, ( 'а4', 2011 )
, ( 'а5', 2011 )
, ( 'а6', 2011 )
, ( 'ак100', 2011 )
, ( 'ак101', 2011 )
, ( 'ак102', 2011 )
, ( 'ак103', 2000 )
, ( 'ак104', 2011 )
, ( 'ак105', 2011 )
, ( 'вм1000', 2017 )
, ( 'вм1001', 2017 )
, ( 'вм1002', 2017 )
, ( 'абв55', 2010 );

with cte
as
(
select * 
    , c2 - row_number() over (partition by year, c1 order by c2) diff
from ( select *, left( code, patindex( '%[0-9]%', code ) - 1 ) c1
            , substring( code, patindex( '%[0-9]%', code ), 100 ) c2
        from @t
     ) t
)
select c1 + min( c2 ) 
     + case when min( c2 ) = max( c2 ) 
            then '' 
            else '-' + c1 + max( c2 )
            end as [codes]
     , year 
from cte
group by year, c1, diff
order by c1 + min( c2 )


в 2012-ом можно ещё проще

результат:

codes                                     year
----------------------------------------- -----------
а1-а3 2012
а4-а6 2011
абв55 2010
ак100-ак102 2011
ак103 2000
ак104-ак105 2011
б14-б16 2014
вм1000-вм1002 2017
14 окт 13, 22:06    [14969751]     Ответить | Цитировать Сообщить модератору
 Re: Сложная или простая группировка с сортировкой  [new]
Allllllllllex
Member

Откуда: Самара
Сообщений: 70
Knyazev Alexey
[src]

в 2012-ом можно ещё проще

результат:

codes                                     year
----------------------------------------- -----------
а1-а3 2012
а4-а6 2011
абв55 2010
ак100-ак102 2011
ак103 2000
ак104-ак105 2011
б14-б16 2014
вм1000-вм1002 2017


Большое спасибо! Просто огромное! Я уж думал не получится. Откуда Вы все знаете?
14 окт 13, 22:23    [14969825]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить