批量取数据的问题

sinxy 2008-04-09 03:16:40
我的表结构大致如下
Name    ID                                      Value 

a	56EEDEB3-D039-46AA-8BDD-33A5C45BE40E	411

b	2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F	15

b	F0E2F342-73AA-4A60-ACBD-492ECE6925B6	3

b	0CB5B416-3071-421F-B910-5A3392C5BD19	2

b	11106A31-461A-4925-ADB3-61F13CDF1AB5	250

b	D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B	79

b	F23550AA-731B-4EAF-9369-F9C458321B9E	71

c	F0E2F342-73AA-4A60-ACBD-492ECE6925B6	374

c	FD0DF7B1-A94B-4F70-8E66-75E9625A632C	5

c	7A4D5DE5-07DD-46CE-8B49-90CAFD1B5377	50

c	8A615CA3-941B-40FB-9BD5-A4EA7C119B1D	20

c	3D11CB76-8373-401D-9E9A-A6B24DD73EFF	40

c	D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B	104

d	1C3BA778-9DC7-4F44-8BD8-28DD86A80DAE	4

d	FD0DF7B1-A94B-4F70-8E66-75E9625A632C	20

d	D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B	6262

d	6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561	17

d	A61DD1C4-108A-4502-94D3-D85E90CB844A	20

e	7012A157-39B1-4241-BDA5-18634A29115D	160

e	2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F	46

e	D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B	21

e	6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561	40

e	56EEDEB3-D039-46AA-8BDD-33A5C45BE40E	5178

e	F0E2F342-73AA-4A60-ACBD-492ECE6925B6	5971

e	3D11CB76-8373-401D-9E9A-A6B24DD73EFF	50

e	D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B	1349

e	6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561	2

我批量的传入了一个Name的字符串给SQL Server，现在希望得到的结果是：
每个人（Name）取他的Value值最高的前5个ID，如果不足5个，取实际个数；

我一次大概需要查询1万个人对应的ID，如果对每个Name按照top 5 来取，就需要做1万次查询，有没有什么办法呢？能否一次查询或者几次查询就搞掂呢？
...全文
75 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
昵称被占用了 2008-04-09
  • 打赏
  • 举报
 回复
同一个name的id不重复就可以了
sinxy 2008-04-09
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 libin_ftsafe 的回复:]
汗一个，ID有重复的情况，Value有重复的可能吗？

SQL codedeclare@ttable(Namevarchar(8),IDvarchar(36),Valueint)insertinto@tselect'a','56EEDEB3-D039-46AA-8BDD-33A5C45BE40E',411insertinto@tselect'b','2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F',15insertinto@tselect'b','F0E2F342-73AA-4A60-ACBD-492ECE6925B6',3insertinto@tselect'b','0CB5B416-3071-421F-B910-5A3392C5BD19',2insertinto@tselect'b','11106A31-461A-…
[/Quote]同一个Name 对应的 ID是不会有重复的 ，但是对应的Value可能会有相同的
flairsky 2008-04-09
  • 打赏
  • 举报
 回复
select * from tab a
where id in (select top 5 id from tab where name=a.name order by value desc)


太阳，写出来的都一样
子陌红尘 2008-04-09
  • 打赏
  • 举报
 回复
汗一个，ID有重复的情况，Value有重复的可能吗？



declare @t table(Name varchar(8),ID varchar(36),Value int)

insert into @t select 'a','56EEDEB3-D039-46AA-8BDD-33A5C45BE40E',411 

insert into @t select 'b','2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F',15  

insert into @t select 'b','F0E2F342-73AA-4A60-ACBD-492ECE6925B6',3   

insert into @t select 'b','0CB5B416-3071-421F-B910-5A3392C5BD19',2   

insert into @t select 'b','11106A31-461A-4925-ADB3-61F13CDF1AB5',250 

insert into @t select 'b','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',79  

insert into @t select 'b','F23550AA-731B-4EAF-9369-F9C458321B9E',71  

insert into @t select 'c','F0E2F342-73AA-4A60-ACBD-492ECE6925B6',374 

insert into @t select 'c','FD0DF7B1-A94B-4F70-8E66-75E9625A632C',5   

insert into @t select 'c','7A4D5DE5-07DD-46CE-8B49-90CAFD1B5377',50  

insert into @t select 'c','8A615CA3-941B-40FB-9BD5-A4EA7C119B1D',20  

insert into @t select 'c','3D11CB76-8373-401D-9E9A-A6B24DD73EFF',40  

insert into @t select 'c','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',104 

insert into @t select 'd','1C3BA778-9DC7-4F44-8BD8-28DD86A80DAE',4   

insert into @t select 'd','FD0DF7B1-A94B-4F70-8E66-75E9625A632C',20  

insert into @t select 'd','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',6262

insert into @t select 'd','6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561',17  

insert into @t select 'd','A61DD1C4-108A-4502-94D3-D85E90CB844A',20  

insert into @t select 'e','7012A157-39B1-4241-BDA5-18634A29115D',160 

insert into @t select 'e','2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F',46  

insert into @t select 'e','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',21  

insert into @t select 'e','6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561',40  

insert into @t select 'e','56EEDEB3-D039-46AA-8BDD-33A5C45BE40E',5178

insert into @t select 'e','F0E2F342-73AA-4A60-ACBD-492ECE6925B6',5971

insert into @t select 'e','3D11CB76-8373-401D-9E9A-A6B24DD73EFF',50  

insert into @t select 'e','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',1349

insert into @t select 'e','6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561',2   



select

    t.*

from

    @t t

where

    t.Value in(select top 5 Value from @t where name=t.name order by value desc)

order by

    t.Name,t.Value desc



/*

Name     ID                                   Value       

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

a        56EEDEB3-D039-46AA-8BDD-33A5C45BE40E 411

b        11106A31-461A-4925-ADB3-61F13CDF1AB5 250

b        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 79

b        F23550AA-731B-4EAF-9369-F9C458321B9E 71

b        2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F 15

b        F0E2F342-73AA-4A60-ACBD-492ECE6925B6 3

c        F0E2F342-73AA-4A60-ACBD-492ECE6925B6 374

c        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 104

c        7A4D5DE5-07DD-46CE-8B49-90CAFD1B5377 50

c        3D11CB76-8373-401D-9E9A-A6B24DD73EFF 40

c        8A615CA3-941B-40FB-9BD5-A4EA7C119B1D 20

d        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 6262

d        FD0DF7B1-A94B-4F70-8E66-75E9625A632C 20

d        A61DD1C4-108A-4502-94D3-D85E90CB844A 20

d        6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561 17

d        1C3BA778-9DC7-4F44-8BD8-28DD86A80DAE 4

e        F0E2F342-73AA-4A60-ACBD-492ECE6925B6 5971

e        56EEDEB3-D039-46AA-8BDD-33A5C45BE40E 5178

e        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 1349

e        7012A157-39B1-4241-BDA5-18634A29115D 160

e        3D11CB76-8373-401D-9E9A-A6B24DD73EFF 50

*/
子陌红尘 2008-04-09
  • 打赏
  • 举报
 回复 


declare @t table(Name varchar(8),ID varchar(36),Value int)

insert into @t select 'a','56EEDEB3-D039-46AA-8BDD-33A5C45BE40E',411 

insert into @t select 'b','2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F',15  

insert into @t select 'b','F0E2F342-73AA-4A60-ACBD-492ECE6925B6',3   

insert into @t select 'b','0CB5B416-3071-421F-B910-5A3392C5BD19',2   

insert into @t select 'b','11106A31-461A-4925-ADB3-61F13CDF1AB5',250 

insert into @t select 'b','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',79  

insert into @t select 'b','F23550AA-731B-4EAF-9369-F9C458321B9E',71  

insert into @t select 'c','F0E2F342-73AA-4A60-ACBD-492ECE6925B6',374 

insert into @t select 'c','FD0DF7B1-A94B-4F70-8E66-75E9625A632C',5   

insert into @t select 'c','7A4D5DE5-07DD-46CE-8B49-90CAFD1B5377',50  

insert into @t select 'c','8A615CA3-941B-40FB-9BD5-A4EA7C119B1D',20  

insert into @t select 'c','3D11CB76-8373-401D-9E9A-A6B24DD73EFF',40  

insert into @t select 'c','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',104 

insert into @t select 'd','1C3BA778-9DC7-4F44-8BD8-28DD86A80DAE',4   

insert into @t select 'd','FD0DF7B1-A94B-4F70-8E66-75E9625A632C',20  

insert into @t select 'd','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',6262

insert into @t select 'd','6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561',17  

insert into @t select 'd','A61DD1C4-108A-4502-94D3-D85E90CB844A',20  

insert into @t select 'e','7012A157-39B1-4241-BDA5-18634A29115D',160 

insert into @t select 'e','2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F',46  

insert into @t select 'e','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',21  

insert into @t select 'e','6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561',40  

insert into @t select 'e','56EEDEB3-D039-46AA-8BDD-33A5C45BE40E',5178

insert into @t select 'e','F0E2F342-73AA-4A60-ACBD-492ECE6925B6',5971

insert into @t select 'e','3D11CB76-8373-401D-9E9A-A6B24DD73EFF',50  

insert into @t select 'e','D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B',1349

insert into @t select 'e','6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561',2   



select

    t.*

from

    @t t

where

    t.id in(select top 5 ID from @t where name=t.name order by value desc)

order by 

    t.name,t.value desc



/*

Name     ID                                   Value       

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

a        56EEDEB3-D039-46AA-8BDD-33A5C45BE40E 411

b        11106A31-461A-4925-ADB3-61F13CDF1AB5 250

b        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 79

b        F23550AA-731B-4EAF-9369-F9C458321B9E 71

b        2BF027AB-3C6B-4EFA-AA4F-978D9A5E761F 15

b        F0E2F342-73AA-4A60-ACBD-492ECE6925B6 3

c        F0E2F342-73AA-4A60-ACBD-492ECE6925B6 374

c        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 104

c        7A4D5DE5-07DD-46CE-8B49-90CAFD1B5377 50

c        3D11CB76-8373-401D-9E9A-A6B24DD73EFF 40

c        8A615CA3-941B-40FB-9BD5-A4EA7C119B1D 20

d        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 6262

d        FD0DF7B1-A94B-4F70-8E66-75E9625A632C 20

d        A61DD1C4-108A-4502-94D3-D85E90CB844A 20

d        6E1A79E0-CC17-4E14-A7C7-ACF82CD3B561 17

d        1C3BA778-9DC7-4F44-8BD8-28DD86A80DAE 4

e        F0E2F342-73AA-4A60-ACBD-492ECE6925B6 5971

e        56EEDEB3-D039-46AA-8BDD-33A5C45BE40E 5178

e        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 1349

e        7012A157-39B1-4241-BDA5-18634A29115D 160

e        3D11CB76-8373-401D-9E9A-A6B24DD73EFF 50

e        D5CE81EF-7DAB-4ABB-A7CA-AC03342AE03B 21

*/
wlinglong 2008-04-09
  • 打赏
  • 举报
 回复
declare @tb table (dt datetime,stockid char(6),fvol numeric(20,5))
insert into @tb select '2008-3-11','000800',289928.4375
insert into @tb select '2008-2-22','000800',503016.1875
insert into @tb select '2008-2-25','000800',-10095.21582
insert into @tb select '2008-2-26','000800',-36981.878906
insert into @tb select '2008-2-27','000800',644.995361
insert into @tb select '2008-2-28','000800',2198.187988
insert into @tb select '2008-2-29','000800',5654.983398
insert into @tb select '2008-3-3','000800', 5546.563965

insert into @tb select '2008-3-2','600000',-24493.21875
insert into @tb select '2008-2-22','600000',550059.6875
insert into @tb select '2008-2-25','600000',-18295.4375
insert into @tb select '2008-2-26','600000', -81992.4375
insert into @tb select '2008-2-27','600000',84372.671875
insert into @tb select '2008-2-28','600000',-10039.753906
insert into @tb select '2008-2-29','600000',-715.375
insert into @tb select '2008-3-3','600000',26328.328125



select * From
(
select top 10 stockid, (dt) ,fvol
from
@tb
where stockid = '你的条件'
group by stockid ,dt ,fvol
order by max(dt) desc
)c


order by stockid ,dt

stockid dt fvol
------- ------------------------------------------------------ ----------------------
000800 2008-02-27 00:00:00.000 644.99536
000800 2008-02-28 00:00:00.000 2198.18799
000800 2008-02-29 00:00:00.000 5654.98340
000800 2008-03-03 00:00:00.000 5546.56397
000800 2008-03-11 00:00:00.000 289928.43750
600000 2008-02-27 00:00:00.000 84372.67188
600000 2008-02-28 00:00:00.000 -10039.75391
600000 2008-02-29 00:00:00.000 -715.37500
600000 2008-03-02 00:00:00.000 -24493.21875
600000 2008-03-03 00:00:00.000 26328.32813


xiaomeixiang 2008-04-09
  • 打赏
  • 举报
 回复 


select * 

from tb a

where id in (select top 5 id from tb where name=a.name order by id desc)
昵称被占用了 2008-04-09
  • 打赏
  • 举报
 回复
一次查出所有

select * from tab a

where id in (select top 5 id from tab where name=a.name order by value desc)
子陌红尘 2008-04-09
  • 打赏
  • 举报
 回复 
select

    t.*

from

    表 t

where

    t.id in(select top 5 ID from 表 where name=t.name order by value desc)
Spring Batch批量处理数据实战教程
本课程通过讲解Spring Batch的基础知识点以及一些实际的项目示例让大家熟悉如何使用Spring Batch进行大批量数据的同步、处理及转换等。
rabbitmq 批量监听拿取数据
项目需求为： 现有一共有三四十个队列，每个队列都必须要做到顺序监听，每个队列里面接收到的数据需要...所以现在改成把需要存储的数据发送到rabbitmq上，然后一次性监听几千条数据，做批量存储。 这是rabbitmq的 ...
java批量处理数据_Java批量处理数据
要求：共1000条数据，第一次批量插入100条，第二次批量插入101到200条，依次插入数据；实现方式这里选择了两种常用的方式，都是使用List操作；第一种实现思路如下：<1> 原先存放数据的List为recordList，求出...
HBase批量写入数据
HBase批量写入数据
Java实现从Redis中批量读取数据
Java实现从Redis中批量读取数据
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章