需要MSSQL查询才能获取数据 [英] Need a MSSQL Query to get the datas

问题描述

亲爱的专家，



我需要一个MSSql查询来获取数据集。



以下是下面的样本表。

Dear Expert,

I need a MSSql query, to get the datas in a set.

Here is the below sample table.

ID  | Name     | Code
----------------------
1   | Emil     | AB
10  | Remu     | AD
15  | Martin   | AB
17  | Dil      | AC
31  | Nav      | AC
42  | Prv      | AB
43  | Pra      | AD
45  | Mob      | AB
57  | ILD      | AB

我的sqlquery将执行三次，每次查询输出如下。



首次执行结果。

My sqlquery will execute in three times, Each time the query output below.

First Execution Outupt.

ID  | Name     | Code
----------------------
1   | Emil     | AB
10  | Remu     | AD
15  | Martin   | AB

第二次执行结果。

Second Execution Outupt.

ID  | Name     | Code
----------------------
17  | Dil      | AC
31  | Nav      | AC
42  | Prv      | AB

第三次执行结果。

Third Execution Outupt.

ID  | Name     | Code
----------------------
43  | Pra      | AD
45  | Mob      | AB
57  | ILD      | AB

实际上这是为了分页.....



请帮帮我



谢谢和问候，

Dileep

Actually this is for like a paging .....

please help me

Thanks and Regards,
Dileep

推荐答案

寻找什么？ ASP.NET控件自动进行分页，如果您只有9条记录，每页3条，那就足够了。否则，您可以使用TOP关键字生成给定数量的记录，您可以按顺序按升序和降序排序，以获取下降到第一条记录的记录，然后按3个记录升序以获取页面上的三条记录。您将需要字符串mash SQL，不能在T-SQL中使用变量作为TOP值。

Paging for what ? The ASP.NET controls do paging automatically and if you only have 9 records and 3 per page, that is good enough. Otherwise, you can use the TOP keyword to generate a given number of records, you can user order by to order by ascending and descending, to get records descending up to the first record, then 3 records ascending to get the three on your page. You will need to string mash SQL, you can't use a variable for a TOP value in T-SQL.

select top 3 *

从XX

按ID排序

select top 3 *
From XX
order by ID

select top 3 *
From XX
Where ID > 15
order by ID

...

...

首次执行

First Execution

declare @pageindex int;
set @pageindex=0
select top 3 * from [tablename]
where ID not in (select top (@pageindex*3) id from [tablename] order by id ) order by id

第二次执行

Second execution

declare @pageindex int;
set @pageindex=1
select top 3 * from [tablename]
where ID not in (select top (@pageindex*3) id from [tablename] order by id ) order by id

第三次执行

Third execution

declare @pageindex int;
set @pageindex=2
select top 3 * from [tablename]
where ID not in (select top (@pageindex*3) id from [tablename] order by id ) order by id

这篇关于需要MSSQL查询才能获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！