• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

批量取数据的问题

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万次查询,有没有什么办法呢?能否一次查询或者几次查询就搞掂呢?
...全文
49 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
同一个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)
回复
一次查出所有

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)
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-09 03:16
社区公告
暂无公告