查询获取排除最后一行的最后n行
我有一个包含400行的表。我想选择除最后一行之外的最后6行。查询获取排除最后一行的最后n行
用下面的代码我得到最后的6行,但我不希望最后(第400),排在那里
SELECT *
FROM ImagesInfo
WHERE Image_Id IN
( SELECT TOP 6 Image_Id
FROM ImagesInfo
ORDER BY Image_Id DESC)
什么:
SELECT * FROM ImagesInfo
WHERE Image_Id IN (SELECT TOP 6 Image_Id FROM ImagesInfo ORDER BY Image_Id DESC) AND
Image_Id <> (SELECT TOP 1 Image_Id FROM ImagesInfo ORDER BY Image_Id DESC)
OR
SELECT * FROM ImagesInfo
WHERE Image_Id IN (SELECT TOP 6 Image_Id FROM ImagesInfo ORDER BY Image_Id DESC) AND
Image_Id <> (SELECT MAX(Image_Id) FROM ImagesInfo)
如果我的理解正确(我不确定),那么你应该能够做到:
SELECT *
FROM ImagesInfo
WHERE Image_Id IN
( SELECT TOP 7 Image_Id
FROM ImagesInfo
ORDER BY Image_Id DESC)
and Image_Id not IN
( SELECT TOP 1 Image_ID
FROM ImagesInfo
ORDER BY Image_Id DESC)
使用ROW_NUMBER()
语法
SELECT *
from
(
SELECT *, ROW_NUMBER() over (ORDER By Image_ID) rn
FROM yourtable
) v
WHERE rn between x and y
试试这个:
你只需要消除的结果,最大记录集
SELECT *
FROM ImagesInfo
WHERE Image_Id IN
( SELECT TOP 6 Image_Id
FROM ImagesInfo
where Image_Id<> (select MAX(Image_Id) from ImagesInfo)
ORDER BY Image_Id DESC)
OR
如果使用SQL Server 2005或以上: 就可以使用,ROW_NUMBER()与CTE
with cte as(
SELECT *,
ROW_NUMBER() over (order by Image_Id desc) as row_num
FROM ImagesInfo)
select * from cte where row_num between 2 and 7
SELECT top 6 *
FROM
( SELECT TOP 7 *
FROM ImagesInfo
ORDER BY Image_Id DESC
) t
ORDER BY Image_Id
OP将包含max(记录) – 2012-08-17 10:19:18
@Joe G Joseph:你确定吗?我刚刚检查过它,它工作正常... – 2012-08-17 10:29:15
哦..我的坏..我没有注意到你的最后一个订单的条款.. +1为您的答案 – 2012-08-17 10:35:49
select top 6 * from image_info
where image_id not in(select max(image_id) from image_info)
order by image_id desc
看OFFSET
SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
[ Microsoft SQL Server网站]
谢谢大家给出答案。几乎所有的答案都是正确的。 – Shan 2012-08-17 10:40:24