问题描述
嗨。
如何返回一系列行。
我知道前5名会返回行0 - 5
但是，我怎么得到6-10？
谢谢
Hi.
How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?
thanks
推荐答案
部分取决于您使用的SQL Server版本。
2000或2005？
2000，你需要类似的东西
从dbo.orders中选择前5名orderid，其中orderid在
（选择前10名orderid）来自dbo.orders订购orderid订购
desc
for 2005类似于：
，OrdersCTE为
（
选择row_number（）结束（按orderid排序）为rownum，*
来自dbo.orders
）
从OrdersCTE中选择*，其中rownum在5到10之间
Depends partly on which version of SQL Server you''re using.
2000 or 2005?
2000, you need something like
select top 5 orderid from dbo.orders where orderid in
(select top 10 orderid from dbo.orders order by orderid) order by orderid
desc
for 2005 something like:
with OrdersCTE as
(
select row_number() over (order by orderid) as rownum, *
from dbo.orders
)
select * from OrdersCTE where rownum between 5 and 10
(obviously don''t use * in your actual production code)
-
Greg Moore
SQL Server DBA咨询远程和现场可用！
电子邮件：sql（at）greenms.com
嗨。
我正在跟踪我自己的消息，提供更详细的信息。
如果某些"kind"" ;灵魂可以告诉我如何隔离一个范围
的行以下每种情况我都会真的
（**真的**）
apprecaite它。 SQL是根据用户
的要求动态生成的。由此产生的陈述变化很大，但是，以下
三个
看起来或多或少与其他人一样：
我非常感谢你的帮助。谢谢！
------------------------------------ -------------------------------------------------- ---------------------------
例一（订购案例陈述）：
选择coalesce（tab1.postalAddress，tab2.name，tab3.postaladdress，
tab4.postaladdress）id，tab1.bdayday，tab1.bdaymonth，
tab1.bdayyear，tab1.sex，tab1.zipCode，tab1.siteId，tab1.userID，
tab2.photolocation，tab2.photoname，tab2.photodefault，
tab2.nophoto，tab3.headline，tab3.aboutme，tab4.signindate from
（从个人资料中选择不同的postalAddress联合选择不同
来自ProfilesPictures的名称联合选择明显的postaladdress来自
ChangeSettings联盟从LastSignIn中选择明显的postaladdress）
drv左加入个人资料tab1 on（drv.postalAddress =
tab1.postalAddress）Left Join
（drv.postalAddress = tab2.name）上的ProfilesPictures tab2 Left Join La stSignIn tab4 on
（drv.postalAddress = tab4.postaladdress）Left Join ChangeSettings tab3
on（drv.postalAddress = tab3.postaladdress）其中tab1.sex =''men ''和
tab1.bdayyear介于''0''和''1988''之间，tab2.photodefault = 1或
tab2.nophoto = 1 by CASE WHEN userID = 67 THEN 1当userID = 103
THEN 2当userID = 102那么3当userID = 81那么4当userID = 94
那么5当userID = 87那么6当用户ID = 104时7当用户ID = 82时
那么8当用户ID = 54时9当用户ID = 64时那么10当用户ID = 63时
那么11当用户ID = 6那么12当用户ID = 58那么13当用户ID = 100
那么14当用户ID = 96那么15当用户ID = 70那么16当用户ID = 98时
那么17当时userID = 69那么18当userID = 92那么19当userID = 93
那么20当userID = 68那么21当userID = 65那么22当userID = 86
那么23当用户ID = 76时24当用户ID = 80时25当用户ID = 76时为25 br />
THEN 26当userID = 73那么27当userID = 72那么28当userID = 74
那么29当userID = 83那么30当userID = 84那么31当userID = 88
那么32当用户ID = 75时那么33当用户ID = 77时那么34当用户ID = 78时
那么35当用户ID = 79时那么36当用户ID = 61那么37当时userID = 101
THEN 38当userID = 97时39当userID = 99那么40当userID = 95
那么41当userID = 71那么42当userID = 85那么43当用户ID = 62
那么44结束;
示例二（订购一些列......可能是日期，时间，数字，
ETC.这总是在考虑）：
选择coalesce（tab1.postalAddress，tab2.name，tab3.postaladdress，
tab4.postaladdress）id，tab1.bdayday ，tab1.bdaymonth，
tab1.bdayyear，tab1.sex，tab1.zipCode，tab1.siteId，tab1.userID，
tab2.photolocation，tab2.photoname，tab2 .photodefault，
tab2.nophoto，tab3.headline，tab3.aboutme，tab4.signindate，
tab4.online from（从Profiles union中选择不同的postalAddress
从ProfilesPictures中选择不同的名称联盟选择不同
来自ChangeSettings联盟的
postaladdress选择明显的postaladdress
来自LastSignIn的
）drv左加入个人资料tab1 on（drv.postalAddress =
tab1.postalAddress）左加入ProfilesPictures tab2 on
（drv.postalAddress = tab2.name）左加入LastSignIn tab4 on
（drv.postalAddress = tab4.postaladdress）Left Join ChangeSettings tab3
on（drv.postalAddress = tab3.postaladdress）w这里tab1.bdayyear
介于''0''和''1988''之间，tab2.photodefault = 1或tab2.nophoto = 1
由tab1.registerDate订购;
示例三（订购的最后时间 - 总是下降）：
选择合并（tab1.postalAddress，tab2.name，tab3。 postaladdress，
tab4.postaladdress）id，tab1.bdayday，tab1.bdaymonth，
tab1.bdayyear，tab1.sex，tab1.zipCode，tab1.siteId，tab1。 userID，
tab2.photolocation，tab2.photoname，tab2.photodefault，
tab2.nophoto，tab3.headline，tab3.aboutme，tab4.signindate，
tab4.online from（从Profiles union中选择不同的postalAddress
从ProfilesPictures中选择不同的名称，选择不同的
来自ChangeSettings union的
postaladdress选择不同的postaladdress
来自LastSignIn的
）drv左连接配置文件tab1 on（drv.postalAddress =
tab1.postalAddress）Left Join ProfilesPictures选项卡2 on
（drv.postalAddress = tab2.name）Left Join LastSignIn tab4 on
（drv.postalAddress = tab4.postaladdress）Left Join ChangeSettings tab3
on（drv.postalAddress = tab3.postaladdress）tab1.bdayyear
介于''0''和''1989''之间，tab2.photodefault = 1而不是tab2.nophoto
= 1个tab4.signindate DESC的订单;
Hi.
I am following up my own message with more detailed info.
If some """kind""" soul could tell me how to isolate a range
of rows for each of the following conditions I would really
(**really**)
apprecaite it. The SQL is dynamically generated based on user
requirements. The resulting statements vary wildly but, the below
three
look more-or-less like the rest of them:
I REALLY appreciate your help. Thanks!
-----------------------------------------------------------------------------------------------------------------
EXAMPLE ONE (ORDERBY CASE STATEMENT):
select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate from
( select distinct postalAddress from Profiles union select distinct
name from ProfilesPictures union select distinct postaladdress from
ChangeSettings union select distinct postaladdress from LastSignIn )
drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.sex=''men'' and
tab1.bdayyear between ''0'' and ''1988'' and tab2.photodefault = 1 or
tab2.nophoto = 1 order by CASE WHEN userID=67 THEN 1 WHEN userID=103
THEN 2 WHEN userID=102 THEN 3 WHEN userID=81 THEN 4 WHEN userID=94
THEN 5 WHEN userID=87 THEN 6 WHEN userID=104 THEN 7 WHEN userID=82
THEN 8 WHEN userID=54 THEN 9 WHEN userID=64 THEN 10 WHEN userID=63
THEN 11 WHEN userID=6 THEN 12 WHEN userID=58 THEN 13 WHEN userID=100
THEN 14 WHEN userID=96 THEN 15 WHEN userID=70 THEN 16 WHEN userID=98
THEN 17 WHEN userID=69 THEN 18 WHEN userID=92 THEN 19 WHEN userID=93
THEN 20 WHEN userID=68 THEN 21 WHEN userID=65 THEN 22 WHEN userID=86
THEN 23 WHEN userID=91 THEN 24 WHEN userID=80 THEN 25 WHEN userID=76
THEN 26 WHEN userID=73 THEN 27 WHEN userID=72 THEN 28 WHEN userID=74
THEN 29 WHEN userID=83 THEN 30 WHEN userID=84 THEN 31 WHEN userID=88
THEN 32 WHEN userID=75 THEN 33 WHEN userID=77 THEN 34 WHEN userID=78
THEN 35 WHEN userID=79 THEN 36 WHEN userID=61 THEN 37 WHEN userID=101
THEN 38 WHEN userID=97 THEN 39 WHEN userID=99 THEN 40 WHEN userID=95
THEN 41 WHEN userID=71 THEN 42 WHEN userID=85 THEN 43 WHEN userID=62
THEN 44 END ;
EXAMPLE TWO (ORDERBY "SOME COLUMN"... COULD BE DATE, TIME, NUMBER,
ETC. THIS IS ALWAYS ASCENDING):
select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
tab4.online from ( select distinct postalAddress from Profiles union
select distinct name from ProfilesPictures union select distinct
postaladdress from ChangeSettings union select distinct postaladdress
from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
between ''0'' and ''1988'' and tab2.photodefault = 1 or tab2.nophoto = 1
order by tab1.registerDate ;
EXAMPLE THREE (ORDERBY LAST TIME LOGGED IN - ALWAYS DESCENDING):
select coalesce (tab1.postalAddress, tab2.name, tab3.postaladdress,
tab4.postaladdress) id , tab1.bdayday , tab1.bdaymonth ,
tab1.bdayyear , tab1.sex , tab1.zipCode , tab1.siteId , tab1.userID ,
tab2.photolocation , tab2.photoname , tab2.photodefault ,
tab2.nophoto , tab3.headline , tab3.aboutme , tab4.signindate ,
tab4.online from ( select distinct postalAddress from Profiles union
select distinct name from ProfilesPictures union select distinct
postaladdress from ChangeSettings union select distinct postaladdress
from LastSignIn ) drv Left Join Profiles tab1 on (drv.postalAddress =
tab1.postalAddress) Left Join ProfilesPictures tab2 on
(drv.postalAddress = tab2.name) Left Join LastSignIn tab4 on
(drv.postalAddress = tab4.postaladdress) Left Join ChangeSettings tab3
on (drv.postalAddress = tab3.postaladdress) where tab1.bdayyear
between ''0'' and ''1989'' and tab2.photodefault = 1 and not tab2.nophoto
= 1 order by tab4.signindate DESC ;
您好pbd22，
有些人甚至已经编写了一整套完整网页的网页
实现这一目标。本页的第一部分重点介绍ASP技术，但第二部分介绍了用于分页的数据库技术。
-
Hugo Kornelis，SQL Server MVP
我的SQL Server博客：
这篇关于如何返回“行范围”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！