Microsoft SQL Server로 LIMIT를 구현하는 방법은 무엇입니까?

Microsoft SQL Server로 LIMIT를 구현하는 방법은 무엇입니까?

---

mysql 에이 쿼리가 있습니다.

select * from table1 LIMIT 10,20

Microsoft sql로 어떻게 할 수 있습니까?

---

SQL SERVER 2005를 시작하면 다음을 수행 할 수 있습니다.

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;

또는 2000 이하 버전에서는 이와 같은 것입니다 ...

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC

---

어리석은 일이지만 효과가 있습니다.

SELECT TOP 10 * FROM table WHERE id NOT IN (SELECT TOP 10 id FROM table ORDER BY id) FROM table ORDER BY id

MSSQL의 LIMIT 절 누락은 형사 IMO입니다. 이런 종류의 복잡한 해결 방법을 수행하지 않아도됩니다.

---

SQL SERVER 2012부터 OFFSET FETCH 절을 사용할 수 있습니다.

USE AdventureWorks;
GO
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader 
ORDER BY SalesOrderID
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
GO

http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx

by by가 고유하지 않으면 올바르게 작동하지 않을 수 있습니다.

조회가 ORDER BY OrderDate로 수정되면 리턴 된 결과 세트가 예상과 다릅니다.

---

이것은 10 월에 제가 물었던 질문과 거의 같습니다 : Microsoft SQL Server 2000의 MySQL LIMIT 절 에뮬레이션

Microsoft SQL Server 2000을 사용하는 경우 좋은 해결책이 없습니다. 대부분의 사람들은 IDENTITY기본 키를 사용하여 임시 테이블에서 쿼리 결과를 캡처해야 합니다. 그런 다음 BETWEEN조건을 사용하여 기본 키 열에 대해 쿼리하십시오 .

Microsoft SQL Server 2005 이상을 사용하는 경우 ROW_NUMBER()함수가 있으므로 동일한 결과를 얻을 수 있지만 임시 테이블은 피하십시오.

SELECT t1.*
FROM (
    SELECT ROW_NUMBER OVER(ORDER BY id) AS row, t1.*
    FROM ( ...original SQL query... ) t1
) t2
WHERE t2.row BETWEEN @offset+1 AND @offset+@count;

@Leon Tayson의 답변에 표시된 것처럼 이것을 공통 테이블 표현식 으로 작성할 수도 있습니다 .

---

SELECT  *
FROM    (
        SELECT  TOP 20
                t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
        FROM    table1 t
        ORDER BY
                field1
        ) t
WHERE   rn > 10

---

이것이 MS SQL Server 2012의 결과를 제한하는 방법입니다.

SELECT * 
FROM table1
ORDER BY columnName
  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

참고 : OFFSET와 함께 또는 함께 사용할 수 있습니다 ORDER BY.

코드 줄을 설명하려면 OFFSET xx ROWS FETCH NEXT yy ROW ONLY

The xx is the record/row number you want to start pulling from in the table, i.e: If there are 40 records in table 1, the code above will start pulling from row 10.

The yy is the number of records/rows you want to pull from the table.

To build on the previous example: If table 1 has 40 records and you began pulling from row 10 and grab the NEXT set of 10 (yy). That would mean, the code above will pull the records from table 1 starting at row 10 and ending at 20. Thus pulling rows 10 - 20.

Check out the link for more info on OFFSET

---

Syntactically MySQL LIMIT query is something like this:

SELECT * FROM table LIMIT OFFSET, ROW_COUNT

This can be translated into Microsoft SQL Server like

SELECT * FROM 
(
    SELECT TOP #{OFFSET+ROW_COUNT} *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum 
    FROM table
) a
WHERE rnum > OFFSET

Now your query select * from table1 LIMIT 10,20 will be like this:

SELECT * FROM 
(
    SELECT TOP 30 *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rnum 
    FROM table1
) a
WHERE rnum > 10 

---

This is one of the reasons I try to avoid using MS Server... but anyway. Sometimes you just don't have an option (yei! and I have to use an outdated version!!).

My suggestion is to create a virtual table:

From:

SELECT * FROM table

To:

CREATE VIEW v_table AS    
    SELECT ROW_NUMBER() OVER (ORDER BY table_key) AS row,* FROM table

Then just query:

SELECT * FROM v_table WHERE row BETWEEN 10 AND 20

If fields are added, or removed, "row" is updated automatically.

The main problem with this option is that ORDER BY is fixed. So if you want a different order, you would have to create another view.

UPDATE

There is another problem with this approach: if you try to filter your data, it won't work as expected. For example, if you do:

SELECT * FROM v_table WHERE field = 'test' AND row BETWEEN 10 AND 20

WHERE becomes limited to those data which are in the rows between 10 and 20 (instead of searching the whole dataset and limiting the output).

---

This is a multi step approach that will work in SQL2000.

-- Create a temp table to hold the data
CREATE TABLE #foo(rowID int identity(1, 1), myOtherColumns)

INSERT INTO #foo (myColumns) SELECT myData order By MyCriteria

Select * FROM #foo where rowID > 10

---

SELECT 
    * 
FROM 
    (
        SELECT 
            top 20              -- ($a) number of records to show
            * 
        FROM
            (
                SELECT 
                    top 29      -- ($b) last record position
                    * 
                FROM 
                    table       -- replace this for table name (i.e. "Customer")
                ORDER BY 
                    2 ASC
            ) AS tbl1 
        ORDER BY 
            2 DESC
    ) AS tbl2 
ORDER BY 
    2 ASC;

-- Examples:

-- Show 5 records from position 5:
-- $a = 5;
-- $b = (5 + 5) - 1
-- $b = 9;

-- Show 10 records from position 4:
-- $a = 10;
-- $b = (10 + 4) - 1
-- $b = 13;

-- To calculate $b:
-- $b = ($a + position) - 1

-- For the present exercise we need to:
-- Show 20 records from position 10:
-- $a = 20;
-- $b = (20 + 10) - 1
-- $b = 29;

---

Must try. In below query, you can see group by, order by, Skip rows, and limit rows.

select emp_no , sum(salary_amount) from emp_salary
Group by emp_no 
ORDER BY emp_no 
OFFSET 5 ROWS       -- Skip first 5 
FETCH NEXT 10 ROWS ONLY; -- limit to retrieve next 10 row after skiping rows

---

In SQL there's no LIMIT keyword exists. If you only need a limited number of rows you should use a TOP keyword which is similar to a LIMIT.

---

If your ID is unique identifier type or your id in table is not sorted you must do like this below.

select * from
(select ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber,* from table1) a
where a.RowNumber between 2 and 5

The code will be

select * from limit 2,5

---

better use this in MSSQLExpress 2017.

SELECT * FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) as [Count], * FROM table1
) as a
WHERE [Count] BETWEEN 10 and 20;

--Giving a Column [Count] and assigning every row a unique counting without ordering something then re select again where you can provide your limits.. :)

---

If i remember correctly (it's been a while since i dabbed with SQL Server) you may be able to use something like this: (2005 and up)

SELECT
    *
   ,ROW_NUMBER() OVER(ORDER BY SomeFields) AS [RowNum]
FROM SomeTable
WHERE RowNum BETWEEN 10 AND 20

---

SELECT TOP 10 * FROM table;

Is the same as

SELECT * FROM table LIMIT 0,10;

Here's an article about implementing Limit in MsSQL Its a nice read, specially the comments.

참고URL : https://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server