求一条sql语句分类返回前n条记录

2008-10-04 05:59:22 290 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Ruoyi-Cloud集成Seata2.0实现分布式事务在网络上，关于Spring Cloud和Ruoyi-Cloud整合Seata的教程有很多，针对不同版本的 Seata 也有不少相关教程。事实上，无论是 Spring Cloud 还是 Ruoyi-Cloud，其整合 Seata 的原理基本相同。这篇博文将详细介绍如何在本地开发环境下，在中实现Seata 2.0的整合，希望能为大家在分布式项目中实现 Seata 的整合提供实用的帮助，提供一个清晰的操作思路。阅读详情

源表:
Id Category AddedDate
1 1 2008-1-2
2 1 2008-1-2
3 1 2008-1-2
4 1 2008-1-3
5 1 2008-1-3
6 2 2008-1-3
7 2 2008-1-3
8 2 2008-1-3
9 2 2008-1-3
10 2 2008-1-3

返回相同Category中的前3条
结果集:
Id Category AddedDate
1 1 2008-1-2
2 1 2008-1-2
3 1 2008-1-2
6 2 2008-1-3
7 2 2008-1-3
8 2 2008-1-3

...全文

非线性功率放大器对QAM传输BER性能的影响及补偿 1、线性功率放大器对传输性能的影响为表述非线性功率放大器对传输性能的影响，以Saleh模型进行观察。具体来说，在QAM调制器之后添加一个非线性模块，使调制后的波形经历AM / AM和AM / PM转换。在Saleh模型中，转换用四个参数表示，即AM / AM转换为????a和????a，AM / PM转换为????p和????p。在Saleh模型中，AM / AM和AM / PM方程以输入电压u的大小表示为：式中，FAM / AM是AM / AM转换的输出电压与输入电压的关系，而FAM 阅读详情

thinsoft

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

+ 关注 + 加入社区

切换为时间正序

hyqwan11112 2008-10-05

打赏
举报



create table 表(Id  int,  Category int,     AddedDate datetime)

insert into 表 

select 1 ,1 ,'2008-1-2' union all

select 2 ,1 ,'2008-1-2' union all

select 3 ,1 ,'2008-1-2' union all

select 4 ,1 ,'2008-1-3' union all

select 5 ,1 ,'2008-1-3' union all

select 6 ,2 ,'2008-1-3' union all

select 7 ,2 ,'2008-1-3' union all

select 8 ,2 ,'2008-1-3' union all

select 9 ,2 ,'2008-1-3' union all

select 10 ,2 ,'2008-1-3'



go

--sql server 2000

select a.* from 表 a 

where 3 > (select count(*) from 表 where Category = a.Category and id < a.id ) 

order by a.Category,a.id

--sql server 2005及以后

select ID,Category,AddedDate

from

(

  select *,rowid=ROW_NUMBER() over(partition by Category order by AddedDate)

  from 表

) t

where rowid<=3



drop table 表

fcuandy 2008-10-05

打赏
举报

http://topic.csdn.net/u/20080123/18/9731d130-0d4b-4c11-8d89-f2c3ca331f0c.html

Limpire 2008-10-05

打赏
举报

路过

tianhuo_soft 2008-10-04

打赏
举报



create table tb(Id  int,  Category int,     AddedDate datetime)

insert into tb values(1 ,     1 ,         '2008-1-2')

insert into tb values(2 ,     1 ,         '2008-1-2') 

insert into tb values(3 ,     1 ,         '2008-1-2') 

insert into tb values(4 ,     1 ,         '2008-1-3') 

insert into tb values(5 ,     1 ,         '2008-1-3') 

insert into tb values(6 ,     2 ,         '2008-1-3') 

insert into tb values(7 ,     2 ,         '2008-1-3') 

insert into tb values(8 ,     2 ,         '2008-1-3') 

insert into tb values(9 ,     2 ,         '2008-1-3') 

insert into tb values(10,     2 ,         '2008-1-3') 

go



select a.* from tb a where 3 > (select count(*) from tb where Category = a.Category and id < a.id ) order by a.Category,a.id

tianhxk 2008-10-04

打赏
举报

[Quote=引用 4 楼 liangCK 的回复:]
SQL code
SELECT *
FROM
(
SELECT *,RID=ROW_NUMBER() OVER(PARTITION BY Category ORDER BY AddedDate)
FROM tb_name
) AS t
WHERE RID<=3

[/Quote]
Sql server2005支持,sql server2000不支持!
55555,我原来忘记装sample文件了，现在装不上去了....

dawugui 2008-10-04

打赏
举报

create table tb(Id  int,  Category int,     AddedDate datetime)

insert into tb values(1 ,     1 ,         '2008-1-2')

insert into tb values(2 ,     1 ,         '2008-1-2') 

insert into tb values(3 ,     1 ,         '2008-1-2') 

insert into tb values(4 ,     1 ,         '2008-1-3') 

insert into tb values(5 ,     1 ,         '2008-1-3') 

insert into tb values(6 ,     2 ,         '2008-1-3') 

insert into tb values(7 ,     2 ,         '2008-1-3') 

insert into tb values(8 ,     2 ,         '2008-1-3') 

insert into tb values(9 ,     2 ,         '2008-1-3') 

insert into tb values(10,     2 ,         '2008-1-3') 

go



--按Category分组取id最小的3个ID

select a.* from tb a where 3 > (select count(*) from tb where Category = a.Category and id < a.id ) order by a.Category,a.id

select a.* from tb a where id in (select top 3 id from tb where Category=a.Category order by id) order by a.Category,a.id

select a.* from tb a where exists (select count(*) from tb where Category = a.Category and id < a.id having Count(*) < 3) order by a.Category,a.id

/*

Id          Category    AddedDate                                              

----------- ----------- ------------------------------------------------------ 

1           1           2008-01-02 00:00:00.000

2           1           2008-01-02 00:00:00.000

3           1           2008-01-02 00:00:00.000

6           2           2008-01-03 00:00:00.000

7           2           2008-01-03 00:00:00.000

8           2           2008-01-03 00:00:00.000



（所影响的行数为 6 行）

*/



--按Category分组取id最大的3个ID

select a.* from tb a where 3 > (select count(*) from tb where Category = a.Category and id > a.id ) order by a.Category,a.id desc

select a.* from tb a where id in (select top 3 id from tb where Category=a.Category order by id desc) order by a.Category,a.id desc

select a.* from tb a where exists (select count(*) from tb where Category = a.Category and id > a.id having Count(*) < 3) order by a.Category,a.id desc

/*

Id          Category    AddedDate                                              

----------- ----------- ------------------------------------------------------ 

5           1           2008-01-03 00:00:00.000

4           1           2008-01-03 00:00:00.000

3           1           2008-01-02 00:00:00.000

10          2           2008-01-03 00:00:00.000

9           2           2008-01-03 00:00:00.000

8           2           2008-01-03 00:00:00.000



（所影响的行数为 6 行）

*/



--按Category分组取id最先出现的3个ID

select a.* from tb a where id in (select top 3 id from tb where Category = a.Category) order by a.Category , a.id

/*

Id          Category    AddedDate                                              

----------- ----------- ------------------------------------------------------ 

1           1           2008-01-02 00:00:00.000

2           1           2008-01-02 00:00:00.000

3           1           2008-01-02 00:00:00.000

6           2           2008-01-03 00:00:00.000

7           2           2008-01-03 00:00:00.000

8           2           2008-01-03 00:00:00.000



（所影响的行数为 6 行）

*/

drop table tb

liangCK 2008-10-04

打赏
举报

SELECT *

FROM

(

  SELECT *,RID=ROW_NUMBER() OVER(PARTITION BY Category ORDER BY AddedDate)

  FROM tb_name

) AS t

WHERE RID<=3

dawugui 2008-10-04

打赏
举报

create table tb(Id  int,  Category int,     AddedDate datetime)

insert into tb values(1 ,     1 ,         '2008-1-2')

insert into tb values(2 ,     1 ,         '2008-1-2') 

insert into tb values(3 ,     1 ,         '2008-1-2') 

insert into tb values(4 ,     1 ,         '2008-1-3') 

insert into tb values(5 ,     1 ,         '2008-1-3') 

insert into tb values(6 ,     2 ,         '2008-1-3') 

insert into tb values(7 ,     2 ,         '2008-1-3') 

insert into tb values(8 ,     2 ,         '2008-1-3') 

insert into tb values(9 ,     2 ,         '2008-1-3') 

insert into tb values(10,     2 ,         '2008-1-3') 

go



select a.* from tb a where 3 > (select count(*) from tb where Category = a.Category and id < a.id ) order by a.Category,a.id



drop table tb



/*

Id          Category    AddedDate                                              

----------- ----------- ------------------------------------------------------ 

1           1           2008-01-02 00:00:00.000

2           1           2008-01-02 00:00:00.000

3           1           2008-01-02 00:00:00.000

6           2           2008-01-03 00:00:00.000

7           2           2008-01-03 00:00:00.000

8           2           2008-01-03 00:00:00.000



（所影响的行数为 6 行）

*/

dawugui 2008-10-04

打赏
举报

按日期取最小的三条?

wzy_love_sly 2008-10-04

打赏
举报

--按某一字段分组取最大(小)值所在行的数据(2007-10-23于浙江杭州)

/*

数据如下：

name val memo

a    2   a2(a的第二个值)

a    1   a1--a的第一个值

a    3   a3:a的第三个值

b    1   b1--b的第一个值

b    3   b3:b的第三个值

b    2   b2b2b2b2

b    4   b4b4

b    5   b5b5b5b5b5

*/

--创建表并插入数据：

create table tb(name varchar(10),val int,memo varchar(20))

insert into tb values('a',    2,   'a2(a的第二个值)')

insert into tb values('a',    1,   'a1--a的第一个值')

insert into tb values('a',    3,   'a3:a的第三个值')

insert into tb values('b',    1,   'b1--b的第一个值')

insert into tb values('b',    3,   'b3:b的第三个值')

insert into tb values('b',    2,   'b2b2b2b2')

insert into tb values('b',    4,   'b4b4')

insert into tb values('b',    5,   'b5b5b5b5b5')

go



--一、按name分组取val最大的值所在行的数据。

--方法1：

select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name

--方法2：

select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)

--方法3：

select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name

--方法4：

select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name

--方法5

select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name

/*

name       val         memo                 

---------- ----------- -------------------- 

a          3           a3:a的第三个值

b          5           b5b5b5b5b5

*/



--二、按name分组取val最小的值所在行的数据。

--方法1：

select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name

--方法2：

select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)

--方法3：

select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name

--方法4：

select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name

--方法5

select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name

/*

name       val         memo                 

---------- ----------- -------------------- 

a          1           a1--a的第一个值

b          1           b1--b的第一个值

*/



--三、按name分组取第一次出现的行所在的数据。

select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name

/*

name       val         memo                 

---------- ----------- -------------------- 

a          2           a2(a的第二个值)

b          1           b1--b的第一个值

*/



--四、按name分组随机取一条数据。

select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name

/*

name       val         memo                 

---------- ----------- -------------------- 

a          1           a1--a的第一个值

b          5           b5b5b5b5b5

*/



--五、按name分组取最小的两个(N个)val

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val

select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val

select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name

/*

name       val         memo                 

---------- ----------- -------------------- 

a          1           a1--a的第一个值

a          2           a2(a的第二个值)

b          1           b1--b的第一个值

b          2           b2b2b2b2

*/



--六、按name分组取最大的两个(N个)val

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val

select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val

select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name

/*

name       val         memo                 

---------- ----------- -------------------- 

a          2           a2(a的第二个值)

a          3           a3:a的第三个值

b          4           b4b4

b          5           b5b5b5b5b5

*/

求一条sql语句分类返回前n条记录

相关推荐