Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4 5 6   вперед  Ctrl      все
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Goga-Gola
тут на Русском :)

У MS, часто, очень не качественный перевод. Во многих местах даже машинный. И временами он исказажает смысл до противоположного. Поэтому стараюсь все читать все же в оригинале, по-английски. Чего и Вам желаю )
21 дек 17, 14:48    [21052069]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Rismen
ваш запрос не работает так как нужно

Попробуйте мой последний вариант запроса:
SELECT C.Surname, SUM(Q.PriceOfResidency)-SUM(Q.Payment) AS Debt
FROM Clients C
JOIN (SELECT R.ClientID, R.PriceOfResidency, SUM(ISNULL(P.Payment,0)) AS Payment
  FROM Residency R
  LEFT JOIN Payment P ON R.ResidencyID=P.ResidencyID
  GROUP BY R.ClientID, R.ResidencyID ) Q
  ON C.ClientID=Q.ClientID
GROUP BY Surname
HAVING ABS(SUM(Q.PriceOfResidency)-SUM(Q.Payment))>0.005
21 дек 17, 14:51    [21052098]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
Rismen
Member

Откуда:
Сообщений: 11
ptr128
Rismen
ваш запрос не работает так как нужно

Попробуйте мой последний вариант запроса:
SELECT C.Surname, SUM(Q.PriceOfResidency)-SUM(Q.Payment) AS Debt
FROM Clients C
JOIN (SELECT R.ClientID, R.PriceOfResidency, SUM(ISNULL(P.Payment,0)) AS Payment
  FROM Residency R
  LEFT JOIN Payment P ON R.ResidencyID=P.ResidencyID
  GROUP BY R.ClientID, R.ResidencyID ) Q
  ON C.ClientID=Q.ClientID
GROUP BY Surname
HAVING ABS(SUM(Q.PriceOfResidency)-SUM(Q.Payment))>0.005


Выдает ошибку: Столбец "Residency.PriceOfResidency" недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.
21 дек 17, 15:07    [21052206]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Rismen
Выдает ошибку


Логично с его стороны )
Исправил
SELECT C.Surname, SUM(Q.PriceOfResidency)-SUM(Q.Payment) AS Debt
FROM Clients C
JOIN (SELECT R.ClientID, MAX(R.PriceOfResidency) AS PriceOfResidency, SUM(ISNULL(P.Payment,0)) AS Payment
  FROM Residency R
  LEFT JOIN Payment P ON R.ResidencyID=P.ResidencyID
  GROUP BY R.ClientID, R.ResidencyID ) Q
  ON C.ClientID=Q.ClientID
GROUP BY Surname
HAVING ABS(SUM(Q.PriceOfResidency)-SUM(Q.Payment))>0.005
21 дек 17, 15:09    [21052220]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9350
ptr128
Вот именно почитайте
Ну и где там подтверждение вашего изречения "Объекты CTE, табличных переменных, вложенных запросов и т.п. размещаются в tempdb"?
21 дек 17, 15:15    [21052254]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
invm
ptr128
Вот именно почитайте
Ну и где там подтверждение вашего изречения "Объекты CTE, табличных переменных, вложенных запросов и т.п. размещаются в tempdb"?

О господи! Откройте ссылку и внимательно изучите содержимое таблицы после фразы "The following table lists the features in SQL Server that create user objects, internal objects, or row versions in tempdb."
21 дек 17, 15:18    [21052276]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ptr128
invm
пропущено...
Ну и где там подтверждение вашего изречения "Объекты CTE, табличных переменных, вложенных запросов и т.п. размещаются в tempdb"?

О господи! Откройте ссылку и внимательно изучите содержимое таблицы после фразы "The following table lists the features in SQL Server that create user objects, internal objects, or row versions in tempdb."

дарагуля, ты наверное хотел сказать изначально что ингода попадают в tempdb? только когда spool есть? А мы тут в ужасе что всегда cte/вложенные запросы в tepmdb падают
21 дек 17, 15:28    [21052333]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
TaPaK
дарагуля, ты наверное хотел сказать изначально что ингода попадают в tempdb?

Нет, детка, нет. Читайте внимательно. И цитируйте оттуда, если желаете возразить.
21 дек 17, 15:30    [21052348]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ptr128
TaPaK
дарагуля, ты наверное хотел сказать изначально что ингода попадают в tempdb?

Нет, детка, нет. Читайте внимательно. И цитируйте оттуда, если желаете возразить.

милочка, позвольте
автор
When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation.
21 дек 17, 15:32    [21052359]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
TaPaK
ptr128
пропущено...

Нет, детка, нет. Читайте внимательно. И цитируйте оттуда, если желаете возразить.

милочка, позвольте
автор
When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation.

Да, в этом случае tempdb используется еще и для этого. Кроме того, что написано было в этой же ячейке перед приведенной Вами фразой:
"A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement."
21 дек 17, 15:50    [21052469]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ptr128
TaPaK
пропущено...

милочка, позвольте
пропущено...

Да, в этом случае tempdb используется еще и для этого. Кроме того, что написано было в этой же ячейке перед приведенной Вами фразой:
"A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement."

а как вы понимаете приведённую вам фразу?
21 дек 17, 15:53    [21052483]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
TaPaK
ptr128
пропущено...

Да, в этом случае tempdb используется еще и для этого. Кроме того, что написано было в этой же ячейке перед приведенной Вами фразой:
"A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement."

а как вы понимаете приведённую вам фразу?

Была же выше ссылка. В лом открыть?
"Обобщенные табличные выражения могут рассматриваться как временные результирующие наборы, определенные в области выполнения одиночных инструкций SELECT, INSERT, UPDATE, DELETE и CREATE VIEW. "
21 дек 17, 16:03    [21052532]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ptr128
TaPaK
пропущено...

а как вы понимаете приведённую вам фразу?

Была же выше ссылка. В лом открыть?
"Обобщенные табличные выражения могут рассматриваться как временные результирующие наборы, определенные в области выполнения одиночных инструкций SELECT, INSERT, UPDATE, DELETE и CREATE VIEW. "

и это означает что cte в tempdb всегда?
21 дек 17, 16:05    [21052541]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9350
ptr128
О господи!
Вот именно! Но и "рукалицо" тоже подойдет.
Медитируйте над фразой "When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation." пока полностью не поймете смысл.
21 дек 17, 16:15    [21052596]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
Goga-Gola
Guest
К чему этот спор? Кто-то надеется, что собеседник скажет - ну да, я был не прав?
Гордыня так и прет. И невежество тоже...

Остановитесь! /(С) Янукович/ :)
21 дек 17, 16:20    [21052615]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
TaPaK
ptr128
пропущено...

Была же выше ссылка. В лом открыть?
"Обобщенные табличные выражения могут рассматриваться как временные результирующие наборы, определенные в области выполнения одиночных инструкций SELECT, INSERT, UPDATE, DELETE и CREATE VIEW. "

и это означает что cte в tempdb всегда?

Ну раз эта фраза находится в колонке "tempdb use"...

На самом деле, в частных случаях, когда план запроса не подразумевает хранения промежуточных результатов, могу предполагать, что объект для результирующих наборов данных CTE в tempdb остается пустым. Оптимизатор все же не настолько туп. Но достаточно только уйти в рекурсию, агрегацию или JOIN не во вложенном цикле, а через хеш, как данные туда посыпятся.
К сожалению, информации о внутреннем устройстве MS SQL очень мало. Как я уже писал выше, от CTE я отказался, когда обнаружил, что он демонстрирует худшую производительность, по сравнению с явным использованием временных таблиц с разумно созданными индексами. А в тех случаях, когда можно обойтись без временных таблиц, он не очень то и нужен, так как легко заменяется вложенными запросами или вьюхами.
21 дек 17, 16:20    [21052620]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Goga-Gola
Кто-то надеется, что собеседник скажет - ну да, я был не прав?

Мне приходится говорить эту фразу регулярно. Даже на этом форуме я уже несколько раз признавал свою ошибку только за эту неделю. Как может быть всегда прав человек, который что-то делает?
21 дек 17, 16:22    [21052636]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ptr128,

ptr128,

автор
Ну раз эта фраза находится в колонке "tempdb use"...
агонь!


На самом деле, в частных случаях, когда план запроса не подразумевает хранения промежуточных результатов, могу предполагать, что объект для результирующих наборов данных CTE в tempdb остается пустым. Оптимизатор все же не настолько туп. Но достаточно только уйти в рекурсию, агрегацию или JOIN не во вложенном цикле, а через хеш, как данные туда посыпятся.

т.е.

;WITH C(I) AS (SELECT TOP 1 number FROM master..spt_Values ),X AS (SELECT a.I FROM C a INNER HASH JOIN C b ON a.I = b.I) SELECT * FROM x

полезет в tempdb?

автор
А в тех случаях, когда можно обойтись без временных таблиц, он не очень то и нужен, так как легко заменяется вложенными запросами или вьюхами.
cte заменяется вложенными запросами.... я не ем сахар, я заменяю его сахаром
21 дек 17, 16:32    [21052685]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
Rismen
Member

Откуда:
Сообщений: 11
ptr128
Rismen
Выдает ошибку


Исправил


Теперь уже работает, но выдает не тех клиентов, которые нужны. Нужны последние три
21 дек 17, 16:38    [21052715]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Rismen
ptr128
пропущено...


Исправил


Теперь уже работает, но выдает не тех клиентов, которые нужны. Нужны последние три


По какому признаку последние?
21 дек 17, 16:40    [21052724]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
TaPaK
полезет в tempdb?

Проверяйте сами. Вы уже потеряли в моих глазах всякое уважение. Поэтому не дождетесь, чтобы я ради Вас хоть пальцем пошевелил.
21 дек 17, 17:01    [21052841]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9350
ptr128
К сожалению, информации о внутреннем устройстве MS SQL очень мало.
Информации полно. Видимо, вы просто не умеете искать.
А если бы нашли и почитали, то не писали бы бред типа
ptr128
На самом деле, в частных случаях, когда план запроса не подразумевает хранения промежуточных результатов, могу предполагать, что объект для результирующих наборов данных CTE в tempdb остается пустым.

К вседению - statistics io покажет все объекты, включая Worktable/Workfile. У неиспользуемых будет 0 чтений.
21 дек 17, 17:14    [21052910]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
Rismen
Member

Откуда:
Сообщений: 11
ptr128
Rismen
пропущено...


Теперь уже работает, но выдает не тех клиентов, которые нужны. Нужны последние три


По какому признаку последние?


Если открыть бд, то в таблице Residency столбце PriceOfResidency полная стоимость за проживание. В таблице Payment столбце Pay сколько оплачено. И нужно вывести тех клиентов, которые либо не заплатили, либо оплатили частично.
21 дек 17, 17:21    [21052941]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
invm
К вседению - statistics io покажет все объекты, включая Worktable/Workfile. У неиспользуемых будет 0 чтений.

Спасибо! Этого я не знал. Воспользуюсь в будущем )
21 дек 17, 17:23    [21052952]     Ответить | Цитировать Сообщить модератору
 Re: Вложенный запрос  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Rismen
ptr128
пропущено...


По какому признаку последние?


Если открыть бд, то в таблице Residency столбце PriceOfResidency полная стоимость за проживание. В таблице Payment столбце Pay сколько оплачено. И нужно вывести тех клиентов, которые либо не заплатили, либо оплатили частично.

Я вижу, что клиенты 1,2,3 полностью оплатили.
В запросе я, так как писал его без БД, неправильно указал имя поля. Вместо SUM(ISNULL(P.Payment,0)) должно быть SUM(ISNULL(P.Pay,0))
И запрос честно выдает оставшихся двух клиентов.

Еще раз запрос, с исправленным именем поля:
SELECT C.Surname, SUM(Q.PriceOfResidency)-SUM(Q.Payment) AS Debt
FROM Clients C
JOIN (SELECT R.ClientID, MAX(R.PriceOfResidency) AS PriceOfResidency, SUM(ISNULL(P.Pay,0)) AS Payment
  FROM Residency R
  LEFT JOIN Payment P ON R.ResidencyID=P.ResidencyID
  GROUP BY R.ClientID, R.ResidencyID ) Q
  ON C.ClientID=Q.ClientID
GROUP BY Surname
HAVING ABS(SUM(Q.PriceOfResidency)-SUM(Q.Payment))>0.005
21 дек 17, 17:37    [21053000]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4 5 6   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить