如何找出每一组数据的前n条数据？

2007-08-21 07:05:53 146 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

yarn的资源调优 1.概念作业，job，application，应用，都是同一个意思 Container: 容器，Yarn的组件，虚拟的概念将一定的内存CPU 比如 1G 1vcore资源抽象为一个小房间，来运行任务 2.yarn的资源调优参照这张图： ①内存方面：假如一台机器是64G物理内存，一般16物理core，对应的32个虚拟core（32vcore） Linux系统本身要占内存+空留: 20% ... 阅读详情

比如找出CSDN用户当天发的前10个帖子？用一句sql怎么实现不用游标循环。

...全文

spark性能调优（二）之资源调优前言：理解资源调优，说白了就是为spark程序提供合理的内存资源、cpu资源等。所以需要知道有哪些参数可以设置这些资源。 –conf PROP=VALUE 手动给sparkConf指定相关配置，比如–conf spark.serializer=org.apache.spark.serializer.KryoSeri... 阅读详情

ASSASSIN_GEN

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

hellowork 2007-08-21

打赏
举报

抱歉,更正一下:
----创建测试数据
declare @t table(id varchar(10), dt datetime)
insert @t
select '1001', '2007-08-21 19:19:02' union all
select '1001', '2007-08-21 19:19:03' union all
select '1001', '2007-08-21 19:19:04' union all
select '1001', '2007-08-21 19:19:05' union all
select '1002', '2007-08-21 19:19:02' union all
select '1002', '2007-08-21 19:19:03' union all
select '1002', '2007-08-21 19:19:04' union all
select '1002', '2007-08-21 19:19:05' union all
select '1002', '2007-08-21 19:19:06'

----方法1:
select * from @t as a where (select count(*) from @t where ID = a.ID and dt > a.dt) < 3
----方法2:
select * from @t as a where not exists(select 1 from @t where ID = a.ID and dt > a.dt group by ID having count(*) > 2)
----方法3:
select * from @t as a where dt in(select top 3 dt from @t where ID = a.ID order by dt DESC)

/*结果
id dt
---------- ------------------------
1001 2007-08-21 19:19:03.000
1001 2007-08-21 19:19:04.000
1001 2007-08-21 19:19:05.000
1002 2007-08-21 19:19:04.000
1002 2007-08-21 19:19:05.000
1002 2007-08-21 19:19:06.000
*/

hellowork 2007-08-21

打赏
举报

假设每个用户的前3个帖子:
----创建测试数据
declare @t table(id varchar(10), dt datetime)
insert @t
select '1001', '2007-08-21 19:19:02' union all
select '1001', '2007-08-21 19:19:03' union all
select '1001', '2007-08-21 19:19:04' union all
select '1001', '2007-08-21 19:19:05' union all
select '1002', '2007-08-21 19:19:02' union all
select '1002', '2007-08-21 19:19:03' union all
select '1002', '2007-08-21 19:19:04' union all
select '1002', '2007-08-21 19:19:05' union all
select '1002', '2007-08-21 19:19:06'

----方法1:
select * from @t as a where (select count(*) from @t where ID = a.ID and dt > a.dt) < 3
----方法2:
select * from @t as a where not exists(select 1 from @t where ID = a.ID and dt > a.dt group by ID having count(*) > 2)
----方法3:
select * from @t as a where dt in(select top 3 dt from @t where ID = a.ID)

/*结果
id dt
---------- ------------------------
1001 2007-08-21 19:19:02.000
1001 2007-08-21 19:19:03.000
1001 2007-08-21 19:19:04.000
1002 2007-08-21 19:19:02.000
1002 2007-08-21 19:19:03.000
1002 2007-08-21 19:19:04.000
*/

Limpire 2007-08-21

打赏
举报

--上面有点乱，看下面的，根据C1分组，提取前2条记录：
DECLARE @Test TABLE (C1 int, C2 int)
INSERT @Test
SELECT 1, 11 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 1, 13 UNION ALL
SELECT 2, 21 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 23 UNION ALL
SELECT 3, 31 UNION ALL
SELECT 3, 32 UNION ALL
SELECT 3, 33
SELECT * FROM @Test a WHERE C2 IN (SELECT TOP 2 C2 FROM @Test WHERE C1 = a.C1) ORDER BY C1, C2

Limpire 2007-08-21

打赏
举报

DECLARE @Test TABLE (C1 int, C2 int)
INSERT @Test SELECT 1, 11 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 21 UNION ALL
SELECT 3, 33 UNION ALL
SELECT 3, 32 UNION ALL
SELECT 3, 31 UNION ALL
SELECT 4, 42 UNION ALL
SELECT 4, 41 UNION ALL
SELECT 5, 51
SELECT * FROM @Test
SELECT * FROM @Test a WHERE C2 IN (SELECT TOP 2 C2 FROM @Test WHERE C1 = a.C1) ORDER BY C1, C2
/*
1．C2为主键，C1有索引的情况下，运行速度最快；
1．C2为主键，C1没有索引的情况下，运行速度超慢；
*/

如何找出每一组数据的前n条数据？

相关推荐