извлича конкретен диапазон от редове в таблица на SQL Server

Имам структура на таблица като (OrderID [uniqueidentifier], OrderDesciption [nvarchar]), използвам ADO.Net + C# + VSTS 2008 + SQL Server 2008. Таблицата е голяма и искам да позволя на клиента да ми даде два входа, индекс на началния диапазон и индекс на крайния диапазон и ще върна конкретни редове от таблицата, която е в диапазона (между индекса на началния диапазон и индекса на крайния диапазон).

Например, ако клиентът ми въведе 50, 100 и искам да върна 50-ия ред до 100-ия ред.

благодаря предварително, Джордж


.net c# sql-server-2008 ado.net
person George2    schedule 21.06.2009    source източник

Отговори (4)


arrow_upward
15
arrow_downward

Можете да използвате ROW_NUMBER в TSQL (2005 нататък), за да направите това:

SELECT  ID, Foo, Bar
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY ID ASC) AS Row,
          ID, Foo, Bar
FROM    SomeTable) tmp
WHERE   Row >= 50 AND Row <= 100

Или с LINQ-to-SQL и т.н.:

var qry = ctx.Table.Skip(50).Take(50); // or similar
person Marc Gravell    schedule 21.06.2009
comment
Нещо грешен резултат от студио за управление на SQL Server. Опитах вашия SQL оператор по този начин, изберете [OrderStatus].[OrderID], [OrderStatus].[OrderStatus] от (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, [OrderStatus].[OrderID], [ OrderStatus].[OrderStatus] от [OrderStatus]), където ред ›= 0 и ред ‹ 100 Съобщението за грешка е, Msg 156, ниво 15, състояние 1, ред 5 Неправилен синтаксис близо до ключовата дума „къде“. - person George2; 21.06.2009
comment
Къде ти е поправката? Объркан съм. Бихте ли го посочили, моля? :-) - person George2; 21.06.2009
comment
Добавих нова колона, наречена OrderCreateTime, и мисля, че ако ИЗБЕРЕМ xxx, ROW_NUMBER() OVER (ORDER BY OrderCreateTime) трябва да е по-добро от поръчка по поръчка по ID на поръчка, нали? И защо? - person George2; 21.06.2009
comment
tmp? Не видях в текущата ви публикация да има термин, наречен tmp? объркан. :-) - person George2; 21.06.2009
comment
Ако OrderID е колона IDENTITY, той така или иначе вероятно ще се увеличава с времето и е по-прост тип данни. Разбира се, ако имате клъстерен индекс на OrderCreateTime, той е идеален за поръчка... - person Marc Gravell; 21.06.2009
comment
вижте ОТ SomeTable) tmp - този псевдоним е всичко, което е необходимо, за да работи. - person Marc Gravell; 21.06.2009
comment
Благодаря, Марк, тъй като OrderID е GUID и вече е клъстерен индекс (тъй като клиентите често ще заявяват информация за поръчката по ID на поръчка), така че смятате ли, че създаването на нормален индекс на OrderCreateTime е приемливо решение? - person George2; 21.06.2009
comment
Guid обикновено прави много лош клъстерен индекс (но отличен неклъстърен индекс). Използвате ли разресване, за да генерирате увеличаващи се водачи? Ако не, бих препоръчал да превключите клъстерирания индекс към OrderCreateTime. Неклъстърният индекс също е добър при сортирането - но просто не е толкова добър, така че ако е необходимо, неклъстерен индекс на OrderCreateTime би свършил работа - но рядко правите съвпадения за равенство на неща, които включват време, така че обикновено пъти прави лош неклъстъриран индекс. - person Marc Gravell; 21.06.2009
comment
Възможно ли е да поръчате такава маса? Създадох таблица, която съдържа колона DateTime и искам да подредя тази колона, но очевидно той игнорира ORDER BY. - person thomasvdb; 26.04.2011
comment
Това е моята заявка, но не я подрежда на [eventdate]: SELECT [ID] ,[operation] ,[reason] ,[eventdate] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS Row, [ID ] ,[операция] ,[причина] ,[дата на събитие] FROM myTable ) tmp WHERE Ред ›= @rowFrom И Ред ‹ @rowTo ORDER BY eventdate DESC; - person thomasvdb; 27.04.2011
comment
@thomasvdb вижте OVER (ORDER BY ID ASC)... вероятно трябва да промените това? - person Marc Gravell; 27.04.2011
comment
Някой има ли статистика кое решение е по-ефективно, това или публикуваното от @marc_s? - person Mason G. Zhwiti; 07.10.2011
comment
@MasonG.Zhwiti, ако имате предвид сравняване на 2-те SQL версии, тогава те имат идентични планове за заявки; изпълняват същото - person Marc Gravell; 07.10.2011

arrow_upward
4
arrow_downward

По принцип най-добрият ви залог в SQL Server 2005 и 2008 е CTE - Common Table Expression - с функция ROW_NUMBER() в него - нещо подобно:

WITH MyOrders AS
(
  SELECT
    OrderID,
    OrderDescription,
    ROW_NUMBER() OVER (ORDER BY OrderID) as 'RowNum'
  FROM YourOrders
)
SELECT * FROM MyOrders
WHERE RowNum BETWEEN 50 AND 100

Но това изисква полезна и подходяща ORDER BY клауза и подреждането по GUID наистина не е добра идея. DATETIME или непрекъснато нарастващ ID ще бъде най-добре.

Марк

person marc_s    schedule 21.06.2009
comment
Здравей, Марк, съжалявам за лошото ми описание, не искам да позволя на клиента да указва диапазон чрез стойността на GUID orderID, а да указва диапазон чрез номера на физическия ред на таблицата. Някакви идеи как да се приложи това? - person George2; 21.06.2009
comment
Е, моят CTE по-горе в публикацията ми прави това - просто избирането чрез GUID е наистина лош начин за подреждане на вашите данни - по същество е напълно произволно...... но ако това е, което търсите, моят кодовият фрагмент трябва да постигне това. - person marc_s; 21.06.2009
comment
Объркан относно това твърдение -- ROW_NUMBER() OVER (ORDER BY OrderID) като 'RowNum' -- тъй като orderID е произволен GUID, искам да извлека диапазон по физически диапазон на ред в таблицата на базата данни, а не по диапазона на стойността на ID на поръчката. Изглежда, че от изявлението, което цитирах, избирате диапазон от редове по orderID? - person George2; 21.06.2009
comment
Съгласен съм, Марк, но вашето изявление има някаква грешка при изпълнение в студио за управление на SQL Server, публикувах изпълнението на моя SQL код и съобщение за грешка, какво не е наред? - person George2; 21.06.2009
comment
Добавих нова колона, наречена OrderCreateTime, и мисля, че ако ИЗБЕРЕМ xxx, ROW_NUMBER() OVER (ORDER BY OrderCreateTime) трябва да е по-добро от поръчка по поръчка по ID на поръчка, нали? И защо? - person George2; 21.06.2009
comment
@George2: да, избирането по OrderCreateTime би било много по-добро - тъй като дефинира реална поръчка - по дата/час, когато поръчката е създадена. GUID за ID на поръчката е просто произволно число, което наистина няма характеристика за поръчка. - person marc_s; 21.06.2009
comment
Функцията ROW_NUMBER() трябва да бъде дефинирана върху дефиниция за подреждане. Само когато поставите всичките си редове в ПОРЪЧКА (от всякакъв вид), само тогава можете да прикачите ROW_NUMBER към тях. Наборът редове на SQL Server сам по себе си няма никакъв ред - ето защо трябва да дефинирате ред, като издадете OVER (ORDER BY (име на колона)), за да може функцията ROW_NUMBER да знае как да прикачи номера на редове към редове. - person marc_s; 21.06.2009
comment
Тъй като OrderID е GUID и вече е клъстерен индекс (тъй като клиентите често ще правят заявки за информация за поръчка чрез ID на поръчка), така че смятате ли, че създаването на нормален индекс на OrderCreateTime е приемливо решение? - person George2; 21.06.2009
comment
Благодаря! Допълнителен въпрос, смятате ли, че използването на CTE е по-добро за моя сценарий или използването на обикновен T-SQL, както предлага Kiewic, е по-добро? И защо? - person George2; 21.06.2009
comment
Мога само да подкрепя препоръката на Marc Gravell: GUID създават много слабо представящи се CLUSTERED индекси - би било МНОГО ПО-ДОБРЕ да имате OrderID INT ИДЕНТИЧНОСТ и да поставите CLUSTERED Index в тази колона. - person marc_s; 21.06.2009
comment
В крайна сметка планът за изпълнение на заявката на Kiewic и моят са идентични. Предпочитам CTE - изглежда ми по-ясно - но това е субективно, лично мнение. - person marc_s; 21.06.2009

arrow_upward
2
arrow_downward

Опитайте това, резултатът ще бъде подреден по колона OrderID. Променете MyTable с вашата маса.

SELECT * 
FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID) AS row FROM MyTable) a 
WHERE row > 50 AND row <= 100

Избраните редове са от 50 до 100, но забележете, че 50-ият ред не е включен в резултата.

person kiewic    schedule 21.06.2009
comment
Изпълнявам вашето изявление, но нещо от SQL Server Management Studio. изберете * от (SELECT ROW_NUMBER() OVER (ORDER BY [OrderStatus].[OrderID]) AS Row from [OrderStatus]), където ред ›= 0 и ред ‹ 100 Съобщението за грешка е, Msg 156, ниво 15, състояние 1, ред 4 Неправилен синтаксис до ключовата дума 'къде'. - person George2; 21.06.2009
comment
Хей, изтри буквата a, която беше след SELECT на третия ред. Това е псевдоним и е необходимо. - person kiewic; 21.06.2009
comment
Добавих нова колона, наречена OrderCreateTime, и мисля, че ако ИЗБЕРЕМ *, ROW_NUMBER() OVER (ORDER BY OrderCreateTime) трябва да е по-добро от поръчка по поръчка по ID на поръчка, правилно и защо? - person George2; 21.06.2009
comment
Буквата a дава име на анонимната заявка в скобите. - person kiewic; 22.06.2009
comment
Съжалявам, как мога да го направя в MYSQL SELECt. Благодаря - person LTEHUB; 12.07.2012
comment
@maydenec Това е еквивалентно на SELECT * FROM MyTable LIMIT 50, 50 в MySQL. - person kiewic; 12.07.2012

arrow_upward
1
arrow_downward

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderId) като ред FROM Orders) a WHERE ред > 5 и ред ‹= 10

person balint    schedule 21.06.2009
comment
Опитах вашето решение, но нещо не е наред, SELECT * FROM [dbo].[OrderStatus] (SELECT *, ROW_NUMBER() OVER (ORDER BY [OrderID]) as row FROM [dbo].[OrderStatus]) where row › 0 и ред ‹ 100 Съобщенията за грешка са, Msg 156, Level 15, State 1, Line 2 Неправилен синтаксис близо до ключовата дума 'SELECT'. Msg 102, ниво 15, състояние 1, ред 2 Неправилен синтаксис близо до ')'. - person George2; 21.06.2009
comment
Добавих нова колона, наречена OrderCreateTime, и мисля, че ако ИЗБЕРЕМ xxx, ROW_NUMBER() OVER (ORDER BY OrderCreateTime) трябва да е по-добро от поръчка по поръчка по ID на поръчка, нали? И защо? - person George2; 21.06.2009
comment
пропуснал си да посочиш избрания .. от Поръчки) ››a‹‹ където.. - person balint; 21.06.2009