使用筛选在组内选择

假设

• name

为了测试连接断路器逻辑,我们将添加另一个“e”行:

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

• 不支持 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

接下来,我们将把这个结果集与原始表连接起来,根据第一个字符匹配行,分数=max(分数):

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个查询和一个中间临时表)更容易理解(可能更容易维护)。

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的某些版本确实支持 ,但不是全部。您还可以执行以下操作:

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 ?