批量取数据的问题

2008-04-09 03:16:40 136 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

运动目标成像必学：5分钟搞懂keystone变换原理与避坑指南本文深入浅出地解析了雷达信号处理中的Keystone变换原理，它是解决运动目标距离徙动问题的关键技术。文章通过直观比喻和简化公式，阐述了该变换如何通过变量代换实现距离与慢时间的解耦合，将倾斜轨迹“掰直”，从而提升成像与检测性能。同时，文中重点指出了实际应用中关于大速度场景、插值误差、多目标处理及高阶运动补偿等关键避坑点，为工程实践提供了宝贵指南。阅读详情

我的表结构大致如下

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万次查询，有没有什么办法呢？能否一次查询或者几次查询就搞掂呢？

...全文

蓝桥杯STM32G431RBT6学习——GPIO 蓝桥杯STM32G431RBT6学习——GPIO 阅读详情

sinxy

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

昵称被占用了 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)

批量取数据的问题

相关推荐