代码之家 › 专栏 › 技术社区 › levant pied

使用筛选在组内选择

sap-ase sql

levant pied · 技术社区 · 7 年前

假设我有以下表格/数据:

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

标准:

按名称的第一个字母分组
每组得分最高

这就是我想到的:

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 s) 一般来说效率如何?

2 回复 | 直到 7 年前

markp-fuso 7 年前

假设

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

Gordon Linoff 7 年前

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 ?