Почему ROW_NUMBER OVER (столбец ORDER BY) возвращает другой порядок результатов, чем просто столбец ORDER BY?

Я работаю на SQL Server 2008, используя NHibernate в качестве уровня сохраняемости (хотя я считаю, что эта проблема связана исключительно с SQL).

Я свел свою проблему к следующему оператору SQL:

SELECT TOP 2
    this_.Id   as Id36_0_,
    this_.Name as Name36_0_,
    ROW_NUMBER() OVER (ORDER BY this_.IsActive) as MyOrder
FROM    Campsites this_
ORDER BY this_.IsActive  /* a bit field */

Это часть запроса, который генерирует NH для получения постраничного набора результатов. Приведенное выше утверждение дает мне следующий результат:

Id36_0_ Name36_0_                       MyOrder
9806    Camping A Cassagnau                 1
8869    Camping a la ferme La Bergamotte    2

Однако, если я опускаю ROW_NUMBER() OVER (ORDER BY this_.IsActive) — это то, что генерирует NH для получения результатов на первой странице — я получаю две совершенно разные записи таблицы в моем результате:

SELECT   TOP 2
    this_.Id   as Id36_0_,
    this_.Name as Name36_0_
    /* ROW_NUMBER() OVER(ORDER BY this_.IsActive) as MyOrder */
FROM     Campsites this_
ORDER BY this_.IsActive  /* a bit field */

возвращается

Id36_0_ Name36_0_
22876   Centro Vacanze Pra delle Torri
22135   Molecaten Park Napoleon Hoeve

Это полностью сбивает меня с толку и приводит к ошибке в нашем приложении, когда я получаю ту же запись Campsite, что и первый элемент на первой и второй странице нашего поиска.

Почему одно и то же предложение ORDER BY работает по-разному внутри выражения ROW_NUMBER OVER()?


tsql sql-server-2008 row-number sql-order-by nhibernate
person Oliver    schedule 01.06.2010    source источник
comment
странно, потому что для диалекта 2005 года столбец row_number всегда называется __hibernate_sort_row, и порядок всегда использует этот столбец.   -  person dotjoe    schedule 02.06.2010

Ответы (3)


arrow_upward
3
arrow_downward

В обоих случаях это в основном случайно, потому что битовое поле плохо для любого порядка (как заметил SQL Menace). Они оцениваются движком БД отдельно, потому что они не имеют ничего общего друг с другом.

Примечание:

  • Внутренний ORDER BY только применяется к порядку значений ROW_NUMBER().
  • Ваш вывод ORDER BY только this_.IsActive
person gbn    schedule 01.06.2010
comment
Спасибо за понимание. Это имеет смысл. Так что мой вопрос действительно должен быть: почему NHibernate делает это именно так? Должен ли я открыть новый вопрос для этого? - person Oliver; 01.06.2010
comment
Я бы открыл новый вопрос. Тогда это вопрос NHibernate, а не SQL. хотя это странно, если это то, что делает NHibernate, если только он не сортирует на стороне клиента, используя новый столбец. Но это базовый материал SQL, хотя в большинстве механизмов БД. - person gbn; 02.06.2010

arrow_upward
5
arrow_downward

ORDER BY this_.IsActive /* битовое поле */

поскольку это битовое поле, оно может быть только 0 или 1... Я предполагаю, что у вас есть много строк с этим битовым полем, равным 0 или 1, упорядочивание по которому не имеет смысла, что, если 90% активно... вы на самом деле не заказываете правильно в этом случае, потому что у вас нет второго заказа.

почему бы вам не выбрать что-то уникальное... например, его_.имя

или что с этим?

ROW_NUMBER() OVER (ORDER BY this_.IsActive, this_.Name)
person SQLMenace    schedule 01.06.2010
comment
Конечно, ты прав. Большинство моих кемпингов активны, поэтому даже 99% из них имеют значение 1. Тем не менее, иногда я хочу упорядочить по (другому) битовому полю, чтобы получить, например. сначала все кемпинги с определенным атрибутом, а затем остальные. Насколько я понимаю, мне нужно использовать второй критерий ORDER BY. - person Oliver; 02.06.2010

arrow_upward
3
arrow_downward

Вы ожидаете, что результат также будет упорядочен по Имени, но только с IsActive в предложении ORDER BY.

Природа SELECT основана на наборах, поэтому вам не следует полагаться на произвольные (но кажущиеся правильными) упорядоченные результаты вашего запроса, если вы явно не определяете порядок.

person devio    schedule 01.06.2010
comment
Спасибо за ответ, Девио. Хорошо, что вы упомянули основанный на SET, потому что теперь имеет смысл, что ORDER BY ‹bit› возвращает непредсказуемый порядок результатов (конечно, внутри каждого значения). - person Oliver; 02.06.2010