|
Доброго времени суток, Посетитель!
|
|
|
|
|
|
|
|
|
вид форума:
|
| |
|
|
| | Здравствуйте все!
Что-то я запнулся на такой задаче.
В процессе обсуждения критики оказались правы, и исходная задача должна быть поставлена более корректно следующим образом.
На сервере (MS SQL Server 2005) расположены две таблицы: таблица Заказчики включает поля ИмяЗаказчика, КодЗаказчика; таблица Заказы включает поля КодЗаказчика, КодЗаказа, ДатаЗаказа, ОбъемЗаказа. Подразумевается, что любой Заказчик в течение любого одного дня имеет не более чем один Заказ, но вообще, естественно, у любого Заказчика может быть любое количество Заказов. Также предполагается, что каждый Заказ сделан каким-либо одним Заказчиком. В этом случае таблицы, очевидно, связаны по полю КодЗаказчика отношением "один-ко-многим".
Требуется сформировать на том же сервере на основании этих таблиц представление, которое выводит поля ИмяЗаказчика, КодЗаказчика, КодЗаказа, ДатаЗаказа, ОбъемЗаказа, но не для всех имеющихся в базе данных Заказов (это-то элементарно), а для каждого Заказчика выводит данные только одного Заказа с самым последним по времени значением поля ДатаЗаказа. Само собой, у каждого Заказчика это самое последнее по времени значение поля ДатаЗаказа может быть свое и заранее неизвестное. Могут потребоваться вариации этой задачи: с самым первым значением ДатаЗаказа; с наибольшим значением ОбъемЗаказа и т. п., это, видимо, решается аналогично.
Мне очень жаль, что первоначальная постановка задчи была некорректна, но и для уточненной задачи пока непонятно, как нужно делать.
Заранее спасибо. | |
| |
| |
| |
|
|
| | Спасибо коллегам, по рекомендации которых я сформировал нижеследующий фрагмент (представление НЕПУСТОЕ), который решает поставленную задачу:
SELECT dbo.Заказчики.КодЗаказчика, dbo.Заказчики.ИмяЗаказчика, dbo.Заказы.КодЗаказа, dbo.Заказы.ДатаЗаказа, dbo.Заказы.ОбъемЗаказа
FROM dbo.Заказчики INNER JOIN
dbo.Заказы ON dbo.Заказчики.КодЗаказчика = dbo.Заказы.КодЗаказчика INNER JOIN
(SELECT Заказчики_1.КодЗаказчика, MAX(Заказы_1.ДатаЗаказа) AS MD
FROM dbo.Заказчики AS Заказчики_1 INNER JOIN
dbo.Заказы AS Заказы_1 ON Заказчики_1.КодЗаказчика = Заказы_1.КодЗаказчика
GROUP BY Заказчики_1.КодЗаказчика) AS zw ON dbo.Заказчики.КодЗаказчика = zw.КодЗаказчика AND dbo.Заказы.ДатаЗаказа = zw.MD
В этом представлении Заказчики, у которых вообще нет Заказов, не выводятся.
Поскольку задача модельная, могут быть ситуации, когда, напротив, Заказчики, у которых вообще нет Заказов, должны выводиться. Казалось бы, следующий фрагмент (представление КВАЗИПУСТОЕ) должен это реализовать, однако здесь также Заказчики, у которых вообще нет Заказов, не выводятся:
SELECT dbo.Заказчики.КодЗаказчика, dbo.Заказчики.ИмяЗаказчика, dbo.Заказы.КодЗаказа, dbo.Заказы.ДатаЗаказа, dbo.Заказы.ОбъемЗаказа
FROM dbo.Заказчики INNER JOIN
dbo.Заказы ON dbo.Заказчики.КодЗаказчика = dbo.Заказы.КодЗаказчика INNER JOIN
(SELECT Заказчики_1.КодЗаказчика, MAX(Заказы_1.ДатаЗаказа) AS MD
FROM dbo.Заказчики AS Заказчики_1 INNER JOIN
dbo.Заказы AS Заказы_1 ON Заказчики_1.КодЗаказчика = Заказы_1.КодЗаказчика
GROUP BY Заказчики_1.КодЗаказчика) AS zw ON dbo.Заказчики.КодЗаказчика = zw.КодЗаказчика AND (zw.MD IS NULL OR
dbo.Заказы.ДатаЗаказа = zw.MD)
Чтобы выводить Заказчиков, у которых вообще нет Заказов, пришлось составить вспомогательное представление zw:
SELECT dbo.Заказчики.КодЗаказчика, MAX(dbo.Заказы.ДатаЗаказа) AS MD
FROM dbo.Заказчики LEFT OUTER JOIN
dbo.Заказы ON dbo.Заказы.КодЗаказчика = dbo.Заказчики.КодЗаказчика
GROUP BY dbo.Заказчики.КодЗаказчика
и такой фрагмент (представление ПУСТОЕ), который это вспомогательное представление использует:
SELECT dbo.Заказчики.КодЗаказчика, dbo.Заказчики.ИмяЗаказчика, dbo.Заказы.КодЗаказа, dbo.Заказы.ДатаЗаказа, dbo.Заказы.ОбъемЗаказа
FROM dbo.Заказчики LEFT OUTER JOIN
dbo.Заказы ON dbo.Заказчики.КодЗаказчика = dbo.Заказы.КодЗаказчика INNER JOIN
dbo.zw ON dbo.Заказчики.КодЗаказчика = dbo.zw.КодЗаказчика AND (dbo.zw.MD IS NULL OR
dbo.Заказы.ДатаЗаказа = dbo.zw.MD)
Поскольку в обоих вариантах поставленная задача получается решенной, можно было бы и успокоиться. Тем не менее есть еще вопросы, и если кто-то знает ответы, пожалуйста, подскажите:
1. Можно ли как-то исправить фрагмент (представление КВАЗИПУСТОЕ) или сочинить что-то иное, чтобы все же выводить Заказчиков, у которых вообще нет Заказов.
2. Если "Да", то будет ли это более эффективно прежде всего по скорости работы, но и по удобству составления таких представлений на будущее (на основе этой модельной задачи должны будут потом аналогичным образом решаться и некоторые другие задачи).
Заранее спасибо. | |
| |
| |
| |
|
|
| | В процессе обсуждения с коллегами выяснилось, что для решения этой задачи надо использовать APPLY, причем, как оказывается, эту штуку при построении представлений нельзя использовать в конструкторе запросов, а только в сценарии. После этого все проблемы были решены.
Ниже привожу два варианта (с учетом всех уточнений постановки задачи): первый View_Order выводит требуемые сведения только для тех Заказчиков, у которых фактически есть хотя бы один Заказ, второй View_Order_Nulls выводит требуемые сведения для всех зарегистрированных Заказчиков, причем для тех Заказчиков, у которых Заказов нет, выводится Null.
Теперь вопрос закрыт полностью.
USE [Base]
GO
/****** Object: View [dbo].[View_Order] Script Date: 06/04/2009 14:07:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_Order] AS
WITH T_ALL AS
(SELECT t1.КодЗаказчика, t1.ИмяЗаказчика, t2.КодЗаказа, t2.ДатаЗаказа, t2.ОбъемЗаказа
FROM dbo.Заказчики AS t1 INNER JOIN dbo.Заказы AS t2 ON t1.КодЗаказчика = t2.КодЗаказчика)
SELECT * FROM
(SELECT КодЗаказчика, ИмяЗаказчика
FROM dbo.Заказчики) t3 CROSS APPLY
(SELECT TOP 1 КодЗаказа, ДатаЗаказа, ОбъемЗаказа FROM T_ALL
WHERE T_ALL.КодЗаказчика = t3.КодЗаказчика ORDER BY ДатаЗаказа DESC) t4
USE [Base]
GO
/****** Object: View [dbo].[View_Order_Nulls] Script Date: 06/04/2009 14:11:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_Order_Nulls] AS
WITH T_ALL AS (SELECT t1.КодЗаказчика, t1.ИмяЗаказчика, t2.КодЗаказа, t2.ДатаЗаказа, t2.ОбъемЗаказа
FROM dbo.Заказчики AS t1 INNER JOIN dbo.Заказы AS t2 ON t1.КодЗаказчика = t2.КодЗаказчика)
SELECT * FROM (SELECT КодЗаказчика, ИмяЗаказчика FROM dbo.Заказчики) t3 OUTER APPLY
(SELECT TOP 1 КодЗаказа, ДатаЗаказа, ОбъемЗаказа FROM T_ALL WHERE T_ALL.КодЗаказчика = t3.КодЗаказчика
ORDER BY ДатаЗаказа DESC) t4 | |
| |
| |
HiProg.com - Технологии программирования
|
