我可以在SQL Server数据库上使用LIMIT N,M吗? [英] Can I use LIMIT N,M on SQL Server database
问题描述
我有以下SQL语句:
SELECT [id], [name]
FROM [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 ON t1.[id] = t2.[itmid]
ORDER BY t2.[iid] ASC;
这似乎很简单,但我无法弄清楚.我需要在其中添加LIMIT N,M
,以从第N个中检索M个项目,但是在'limit'字周围,我不断出错.我尝试将LIMIT
子句放在上面的sql语句中的任何地方,但无济于事.
This seems very simple, but I can't figure it out. I need to add LIMIT N,M
to it to retrieve M items from the N'th one, but I keep getting errors around 'limit' word. I tried putting that LIMIT
clause everywhere I could inside the sql statement above with no avail.
PS.我正在写VS2010附带的SQL Server.
PS. I'm writing for SQL Server that comes with VS2010.
推荐答案
要回答您的查询,您可能需要:(取决于M
和N
的值)
To answer for your query, you may want: (depending on your values for M
and N
)
WITH cte AS
(
SELECT [id], [name], ROW_NUMBER() OVER (ORDER BY t2.[iid] ASC) AS rowNumber
FROM [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 ON t1.[id] = t2.[itmid]
)
SELECT [id], [name]
FROM cte
WHERE rowNumber BETWEEN 3 AND 5
需要注意的是,两者之间的值是BETWEEN N AND N + M
Something to watch out for, the values in the between are BETWEEN N AND N + M
此外,这是一个链接,其中包含有关公用表表达式的信息是我使用的WITH cte
语法.
Also, here's a link with information about Common Table Expressions which is the WITH cte
syntax I used.
这篇关于我可以在SQL Server数据库上使用LIMIT N,M吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!