Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Не брать лишнее в запросе  [new]
Crvik
Member

Откуда: Казахстан, Петропавловск
Сообщений: 302
Всем привет!
На ровном месте споткнулся и не могу догнать как правильно вырулить запрос :)
Есть две таблицы, услуги и цены за эти услуги в разрезе дат, нужно получить Услугу и свежую за нее цену.
Пример:
declare @Services table (id int, ServiceName varchar(15))
INSERT INTO @Services VALUES(1,'Услуга 1')
INSERT INTO @Services VALUES(2,'Услуга 2')
INSERT INTO @Services VALUES(3,'Услуга 3')

declare @Service_History table (id int, Service_id int, cost money, DateBegin datetime)
INSERT INTO @Service_History VALUES(1,1, 250, '2011.01.01')
INSERT INTO @Service_History VALUES(1,2, 100, '2011.01.01')
INSERT INTO @Service_History VALUES(1,1, 300, '2011.02.01')

SELECT * FROM @Services 
SELECT * FROM @Service_History

Хочу получить следующий результат:
Услуга 1 300,00
Услуга 2 100,00

Пробую следующим запросом получить нужные данные
SELECT s.ServiceName, (select TOP(1) Cost 
		FROM @Service_History sh WHERE sh.Service_id=s.id and DateBegin<=GetDate() order by DateBegin DESC ) as summ
		FROM @Services s 
Выводит лишнюю услугу по которой не заведена цена
Услуга 1 300,00
Услуга 2 100,00
Услуга 3 0,00

Подскажите, что не так, может другим запросом красивее можно получить эти данные?
15 ноя 11, 18:10    [11601739]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Замените корелляционный подзапрос на APPLY.
15 ноя 11, 18:14    [11601777]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
Crvik
Member

Откуда: Казахстан, Петропавловск
Сообщений: 302
pkarklin
Замените корелляционный подзапрос на APPLY.


Прикольно! Не знал про Apply, крутая штука, спасибо pkarklin!
Получилось то что нужно!

SELECT Name, Cost from Services s 
cross apply (select TOP(1) Cost 
		FROM Service_History sh WHERE sh.Service_id=s.id and DateBegin<=GetDate() order by DateBegin DESC ) summ
15 ноя 11, 18:33    [11601903]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
SomeUserSQL
Guest
Изначально надо из таблицы цен на услуги выбрать услуги с наибольшей датой но одинаковым айди услуги (намек на группировку ;)) и потом уже только из справочника услуг подсоединить названия услуг ) Запрос постарайтесь написать сами, иначе пользы не будет
15 ноя 11, 18:35    [11601919]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
Crvik
Member

Откуда: Казахстан, Петропавловск
Сообщений: 302
SomeUserSQL
Изначально надо из таблицы цен на услуги выбрать услуги с наибольшей датой но одинаковым айди услуги (намек на группировку ;)) и потом уже только из справочника услуг подсоединить названия услуг ) Запрос постарайтесь написать сами, иначе пользы не будет


Такой алгоритм у меня крутился изначально, но реализовать не получается, вот что получилось:
Select Service_id, MAX(DateBegin)
 from @Service_History 
group by Service_id

Нашел номер услуги и нужную дату, а стоимость потерял :( Если стоимость выводить, то она просится в группировку, после чего смысла от этого запроса нет, т.е.
Select Service_id, MAX(DateBegin), Cost
 from @Service_History 
group by Service_id, Cost

Уверен запрос проще некуда в итоге получится, но что то я торможу :)
15 ноя 11, 19:15    [11602153]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Crvik
Нашел номер услуги и нужную дату, а стоимость потерял :(

а джойнить по Service_id и MAX(DateBegin) не пробовали?
15 ноя 11, 19:20    [11602170]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
Crvik
Member

Откуда: Казахстан, Петропавловск
Сообщений: 302
kDnZP
Crvik
Нашел номер услуги и нужную дату, а стоимость потерял :(

а джойнить по Service_id и MAX(DateBegin) не пробовали?


Спасибо за наводку, получилось следующая конструкция:
Select (Select ServiceName from @Services where sh.Service_id=id) as ServiceName, sh.cost
 from @Service_History sh right join 
	(Select Service_id, MAX(DateBegin) as DateBegin
		from @Service_History 
		group by Service_id) ss on sh.Service_id=ss.Service_id and sh.dateBegin=ss.DateBegin

Дайте оценку получившемуся, чувствую, что у спецов найдется еще парочка решения данной задачи, хотелось бы посмотреть в ознакомительно-образовательных целях :)
15 ноя 11, 20:14    [11602342]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Crvik, уверяю, у pkarklin-а как раз тот случай когда 43'157 сообщений о чём-то да говорит.

Двойное обращение к табле (или точнее зырим в план запроса), объекты без указания схемы, нет алиасов, подзапрос в SELECT, RIGHT JOIN и не форматированный код. Ад какой-то.
SELECT	 S.Name
	,H.Cost
FROM	dbo.Services	S
	CROSS APPLY (
		SELECT	Top(1) *
		FROM	dbo.Service_History	H
		WHERE	    H.Service_ID	 = S.ID
			AND H.DateBegin		<= GetDate()
		ORDER BY H.DateBegin DESC
		)	H
Да, там можно спорить про план, но всё равно сведётся к частностям.
16 ноя 11, 00:14    [11602978]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Crvik,
Crvik
Дайте оценку получившемуся, чувствую, что у спецов найдется еще парочка решения данной задачи, хотелось бы посмотреть в ознакомительно-образовательных целях :)


Mnior
Crvik, уверяю, у pkarklin-а как раз тот случай когда 43'157 сообщений о чём-то да говорит.

Тут Mnior кагбы намекает, что решение с CROSS APPLY по-видимому будет лучшим решением. Скорее всего он прав. Но для общего развития знать другие варианты безусловно полезно. Вот чуть ниже ваш, а также еще один слегка модифицированный вариант.

SELECT  (
          SELECT ServiceName FROM @Services WHERE sh.Service_id = id
        ) AS ServiceName
      , sh.cost
FROM    @Service_History sh
JOIN (
             SELECT Service_id
                  , MAX(DateBegin) AS DateBegin
             FROM   @Service_History
             GROUP BY Service_id
           ) ss
ON      sh.Service_id = ss.Service_id
        AND sh.dateBegin = ss.DateBegin
SELECT  (
          SELECT ServiceName FROM @Services s WHERE t.Service_id = s.id
        ) AS ServiceName
      , t.cost
FROM    (
          SELECT    Service_id
                  , MAX(DateBegin) OVER ( PARTITION BY Service_id ) MDateBegin
                  , DateBegin
                  , cost
          FROM      @Service_History
        ) t
WHERE   t.MDateBegin = t.DateBegin

Поглядите планы запросов, узнаете какой из них лучше и почему.
16 ноя 11, 10:46    [11603904]     Ответить | Цитировать Сообщить модератору
 Re: Не брать лишнее в запросе  [new]
Crvik
Member

Откуда: Казахстан, Петропавловск
Сообщений: 302
kDnZP
Crvik,
Crvik
Дайте оценку получившемуся, чувствую, что у спецов найдется еще парочка решения данной задачи, хотелось бы посмотреть в ознакомительно-образовательных целях :)


Mnior
Crvik, уверяю, у pkarklin-а как раз тот случай когда 43'157 сообщений о чём-то да говорит.

Тут Mnior кагбы намекает, что решение с CROSS APPLY по-видимому будет лучшим решением. Скорее всего он прав. Но для общего развития знать другие варианты безусловно полезно. Вот чуть ниже ваш, а также еще один слегка модифицированный вариант.


Полностью с вами согласен, решение которое подсказал pkarklin хорошее и работает намного быстрей. Спасибо за дополнительный пример реализации данной задачи, для самообразования это отличный пример!
16 ноя 11, 12:25    [11604704]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить