在sqlserver 2008上查询 [英] query on sqlserver 2008

问题描述

请告诉我选择表格中的第5行，但不输入行号.我该怎么办?请告诉我

hi please any one tell me select 5th row in table with out enter row number how can i do it please tell me

推荐答案

WITH myTableWithRows AS (SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,* FROM myTable)
   SELECT * FROM myTableWithRows WHERE row = 5

或:

Or:

SELECT * 
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNum
    FROM [Order Details]   
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum = 5

这是一种实现方法

Here is one way to do it

CREATE TABLE #SampleTable
(
    ID INT,
    [Name] VARCHAR(50)
)


INSERT INTO #SampleTable
SELECT 1, 'XYZ 1'UNION ALL
SELECT 2, 'XYZ 2' UNION ALL
SELECT 3, 'XYZ 3' UNION ALL
SELECT 4, 'XYZ 4' UNION ALL
SELECT 5, 'XYZ 5' UNION ALL
SELECT 6, 'XYZ 6' UNION ALL
SELECT 7, 'XYZ 7' UNION ALL
SELECT 8, 'XYZ 8'


SELECT TOP 1 * FROM
(
    SELECT TOP 5 * FROM
    (
        SELECT TOP 5 * FROM #SampleTable
        ORDER BY Id ASC
    ) T
    ORDER BY Id DESC
) T1

DROP TABLE #SampleTable

这篇关于在sqlserver 2008上查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！