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

从每组中选择最上面的1行

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

Easty · 技术社区 · 13 年前

我有一个表格,列出了已安装软件的版本:

id  | userid | version | datetime
----+--------+---------+------------------------
111 | 75     | 10075   | 2013-03-12 13:40:58.770
112 | 75     | 10079   | 2013-03-12 13:41:01.583
113 | 78     | 10065   | 2013-03-12 14:18:24.463
114 | 78     | 10079   | 2013-03-12 14:22:20.437
115 | 78     | 10079   | 2013-03-12 14:24:01.830
116 | 78     | 10080   | 2013-03-12 14:24:06.893
117 | 74     | 10080   | 2013-03-12 15:31:42.797
118 | 75     | 10079   | 2013-03-13 07:03:56.157
119 | 75     | 10080   | 2013-03-13 07:05:23.137
120 | 65     | 10080   | 2013-03-13 07:24:33.323
121 | 68     | 10080   | 2013-03-13 08:03:24.247
122 | 71     | 10080   | 2013-03-13 08:20:16.173
123 | 78     | 10080   | 2013-03-13 08:28:25.487
124 | 56     | 10080   | 2013-03-13 08:49:44.503

我想显示每个记录中一条记录的所有字段 userid 但只有最高版本(也是 varchar ).

7 回复 | 直到 10 年前

Tim Schmelter 13 年前

如果使用SQL Server(最低2005年),则可以使用 CTE 与 ROW_NUMBER 作用您可以使用 CAST 对于获得正确顺序的版本:

WITH cte 
     AS (SELECT id, 
                userid, 
                version, 
                datetime, 
                Row_number() 
                  OVER ( 
                    partition BY userid 
                    ORDER BY Cast(version AS INT) DESC) rn 
         FROM   [dbo].[table]) 
SELECT id, 
       userid, 
       version, 
       datetime 
FROM   cte 
WHERE  rn = 1 
ORDER BY userid

Demo

ROW_NUMBER 即使有多个用户使用相同的(顶级)版本,也始终返回一条记录。如果要返回所有“顶级版本用户记录”,则必须替换 行编号 具有 DENSE_RANK .

Joachim Isaksson 13 年前

您没有指定希望如何处理领带,但如果您希望显示重复项,则可以这样做;

SELECT a.* FROM MyTable a
LEFT JOIN MyTable b
  ON a.userid=b.userid
 AND CAST(a.version AS INT) < CAST(b.version AS INT)
WHERE b.version IS NULL

An SQLfiddle to test with .

如果您想消除重复项,并且如果存在重复项,请选择最新的重复项,则必须在一定程度上扩展查询;

WITH cte AS (SELECT *, CAST(version AS INT) num_version FROM MyTable)
SELECT a.id, a.userid, a.version, a.datetime 
FROM cte a LEFT JOIN cte b
  ON a.userid=b.userid
 AND (a.num_version < b.num_version OR 
     (a.num_version = b.num_version AND a.[datetime]<b.[datetime]))
WHERE b.version IS NULL

Another SQLfiddle .

John Woo 13 年前

WITH records
AS
(
    SELECT  id, userid, version, datetime,
            ROW_NUMBER() OVER (PARTITION BY userID
                                ORDER BY version DESC) rn
    FROM    tableName
)
SELECT id, userid, version, datetime
FROM    records
WHERE   RN =1

SQLFiddle Demo

Rdy 13 年前

我认为这可能会解决你的问题:

 SELECT id,
       userid,
       Version,
       datetime FROM (
           SELECT id,
                  userid,
                  Version,
                  datetime , 
                  DENSE_Rank() over (Partition BY id order by datetime asc) AS Rankk
           FROM [dbo].[table]) RS 
WHERE Rankk<2

我使用了RANK函数来满足您的要求。。。。

Burleigh Bear 13 年前

select l.* from the_table l
left outer join the_table r
on l.userid = r.userid and l.version < r.version
where r.version is null

Malka 11 年前

下面的代码将显示您想要的内容,并且非常适合性能!

select * from the_table t where cast([version] as int) = 
(select max(cast([version] as int)) from the_table where userid = t.userid)

pim 9 年前

如果说我的调优经验教会了我什么的话,那么概括就是糟糕——糟糕——糟糕。

但是,如果你的桌子 Top X from很大(即数十万或数百万)。 CROSS APPLY 几乎是世界上最好的。事实上,如果您对其进行基准测试,则交叉应用会执行 始终如一地&令人钦佩 也以较小的规模(数万) 带领带 潜在需求。

类似于:

select
    id
    ,userid
    ,version
    ,datetime
from
    TheTable t
cross apply
(
    select top 1 --with ties
        id
    from
        TheTable
    where
        userid = t.userid
    order by
        datetime desc
)