代码之家 › 专栏 › 技术社区 › szpic

从每个组中选择值最高的记录

greatest-n-per-group sql-server-2012 sql

szpic · 技术社区 · 8 年前

假设我有这样的桌子:

客户床垫表:

Shop        Name        Surname     Adress
----------------------------------------------
SHOP1       name1       surname1    adress1_1 
SHOP2       name1       surname1    adress1_2 
SHOP1       name2       surname2    adress2_1 
SHOP3       name2       surname2    adress2_3 
SHOP3       name1       surname1    adress1_3

和

ShopPriority表:

Shop   Priority
---------------
SHOP1       1
SHOP2       2
SHOP3       3

我正在尝试选择优先级最高的客户地址(重新获得它是商店)

我写过SQL:

select ca.shop, ca.name, ca.surname, ca.adress, sp.priority
from CustomerAdresses ca
join ShopPriority sp on sp.shop = ca.shop
group by ca.name, ca.surname, ca.adress, ca.shop, sp.priority

此查询返回如下结果:

Shop        name        surname     adress    priority
-------------------------------------------------------
SHOP1       name1       surname1    adress1_1   1
SHOP2       name1       surname1    adress1_2   2
SHOP3       name1       surname1    adress1_3   3
SHOP1       name2       surname2    adress2_1   1
SHOP3       name2       surname2    adress2_3   3

现在我已经接近我需要的想法了。记录按名称和姓氏分组。现在我只需要从具有最高优先级的每个组中获得一个记录。我试着一组一组地订购记录,但后来整个组都崩溃了。

2 回复 | 直到 8 年前

Yogesh Sharma 8 年前

row_number()

select t.*
from (select ca.shop, ca.name, ca.surname, ca.adress, sp.priority,
             row_number() over (partition by ca.shop order by sp.priority desc) as seq
      from CustomerAdresses ca join 
           ShopPriority sp 
           on sp.shop = ca.shop
      ) t
where seq = 1;

top (1) with ties

select top (1) with ties ca.shop, ca.name, ca.surname, ca.adress, sp.priority
from CustomerAdresses ca join 
     ShopPriority sp 
     on sp.shop = ca.shop
order by row_number() over (partition by ca.shop order by sp.priority desc);

Gordon Linoff 8 年前

row_number()

select spc.*
from (select ca.shop, ca.name, ca.surname, ca.adress, sp.priority,
             row_number() over (partition by ca.shop order by sp.priority desc) as seqnum
      from CustomerAdresses ca join
           ShopPriority sp
           on sp.shop = ca.shop
     ) spc
where seqnum = 1;

推荐文章

M. Rictusgrin · 逐字符比较两个字符串并突出显示不同的字符串

4 年前

selva raj · 如何在sql server中创建具有动态列的表

4 年前

Sai · 如何恢复SQL Server 2012数据库?

8 年前

Masoud · SQL Server中多列的透视

8 年前

Mitesh Jain · 如何消除重复记录

8 年前

ravsun · 按多列值自定义联合查询顺序

8 年前

NoBullMan · SQL查询-删除列值重复的行

8 年前

Sai · 如何从SQL Server 2012中选择datetime where isnull值?

8 年前

JohnMark Sill · 通过一个条件将子集合从一行求和到另一行

8 年前

Malik Mahsam Awan · 从webapi获取角度4中的日期

8 年前