Выбор внутри группы с фильтрацией

Question

Скажем, у меня есть следующая таблица / данные:

create table #a(id int, name varchar(2), score int)
go
insert #a values(0, 'a1', 1)
insert #a values(1, 'b1', 0)
insert #a values(2, 'c1', 1)
insert #a values(3, 'd1', 0)
insert #a values(4, 'd2', 1)
insert #a values(5, 'e1', 0)
insert #a values(6, 'e2', 2)
insert #a values(7, 'e3', 1)
insert #a values(8, 'e4', 0)

Я хотел бы выбрать эти строки:

id name score
1  b1   0
2  c1   1
4  d2   1
6  e2   2

Критерии:

• Только id > 0
• Группы по первой букве имени
• В каждой группе самый высокий балл
• Tie-breaker, самое высокое имя (сортировка ASCII)

Вот что я придумал:

select id, name, score
into #b
from #a
where id > 0
group by left(name, 1)
having score = max(score)
go
select f.* 
from #b f
left join #b g on left(g.name, 1) = left(f.name, 1) and g.name > f.name
where g.name is null
order by f.name

Можно ли сделать это лучше, если не использовать временную таблицу / два запроса / повторения (все это leftс) а эффективность в целом?

Answer 1

Предположения

• name уникален

Чтобы проверить логику тай-брейка, мы добавим еще одну строку 'e':

insert #a values (9,'e5',2) -- same score as the 6/e2/2 record

Поскольку SAP(Sybase) ASE имеет довольно много ограничений...

• нет поддержки для rank() функция
• нет поддержки для row_number() функция
• нет поддержки для offset/limit статьи
• ограниченная поддержка top предложение в подзапросах
• нет поддержки для order by предложение в подзапросах

... нам нужно проявить немного "креативности" (читай: это станет немного запутанным)

Первое, что мы сделаем, это найдем максимальный балл для каждого из отдельных символов, где id > 0:

select  left(name,1) as name1,
        max(score)   as mscore

from    #a
where   id > 0

group by left(name,1)
order by 1
go

 name1 mscore
 ----- -----------
 b               0
 c               1
 d               1
 e               2

Далее мы объединяем этот набор результатов с исходной таблицей, сопоставляя строки на основе первого символа и счет = макс (оценка):

select a2.name1,
       a1.name,
       a2.mscore

from   #a a1
join   (select  left(name,1) as name1,
                max(score)   as mscore
        from    #a
        where   id > 0
        group by left(name,1)) a2

on      left(a1.name,1) = a2.name1
and     a1.score        = a2.mscore
and     a1.id           > 0

order by 1,2
go

 name1 name mscore
 ----- ---- -----------
 b     b1             0
 c     c1             1
 d     d2             1
 e     e2             2
 e     e5             2

Далее мы рассмотрим правило тай-брейка; мы можем справиться с этим, применяя max() функция к нашему a1.name столбец (не забудьте добавить соответствующий group by пункт):

select a2.name1,
       max(a1.name) as mname,
       a2.mscore

from   #a a1
join   (select  left(name,1) as name1,
                max(score)   as mscore
        from    #a
        where   id > 0
        group by left(name,1)) a2

on      left(a1.name,1) = a2.name1
and     a1.score        = a2.mscore
and     a1.id           > 0

group by a2.name1, 
         a2.mscore
order by 1,2
go

 name1 mname mscore
 ----- ----- -----------
 b     b1              0
 c     c1              1
 d     d2              1
 e     e5              2

Последняя часть головоломки состоит в том, чтобы связать этот конечный результат с исходной таблицей, чтобы получить идентификатор:

select a3.id,
       a4.mname  as 'name',
       a4.mscore as 'score'
from   #a a3
join   (select a2.name1,
               max(a1.name) as mname,
               a2.mscore

        from   #a a1
        join   (select  left(name,1) as name1,
                        max(score)   as mscore
                from    #a
                where   id > 0
                group by left(name,1)) a2

        on      left(a1.name,1) = a2.name1
        and     a1.score        = a2.mscore
        and     a1.id           > 0

        group by a2.name1,
              a2.mscore) a4

on     a3.name = a4.mname

order by 1,2
go

 id          name score
 ----------- ---- -----------
           1 b1             0
           2 c1             1
           4 d2             1
           9 e5             2

ПРИМЕЧАНИЕ. Вышеуказанные запросы / результаты проверены на SAP(Sybase) ASE 16.0 SP03 PL01.

---

Чистый результат...

Хотя с помощью одного запроса можно делать то, что нужно, кодирование немного запутано (и, вероятно, его сложнее поддерживать).

Исходный код (2 запроса и промежуточная временная таблица) немного легче понять (и, вероятно, немного легче поддерживать).

Answer 2

Использование row_number():

select a.*
from (select a.*,
             row_number() over (partition by left(name, 1)
                                order by score desc, name asc
                               ) as seqnum
      from #a a
     ) a
where seqnum = 1;

РЕДАКТИРОВАТЬ:

Некоторые версии Sybase поддерживают row_number(), но не все. Вы также можете сделать:

select a.*
from #a a
where a.id = (select top 1 a2.id
              from #a a2
              where left(a2.name, 1) = left(a.name, 1)
              order by a2.score desc, name asc
             );

РЕДАКТИРОВАТЬ II:

Это работает?

select a.*
from (select a.*,
             (select top 1 a2.id
              from #a a2
              where left(a2.name, 1) = left(a.name, 1)
              order by a2.score desc, name asc
             ) as comp_id
      from #a a
     ) a
where id = comp_id;

То есть TOP разрешено в подзапросах в SELECT?