14.02.2014, 20:22
общий
это ответ
Здравствуйте, Александр!
Создана база со следующими таблицами:
Tip справочник видов автомобилей
Kod счетчик, ключевое поле
Tip строка ( 20 ) 1-легковой, 2-грузовой
SprAvto Справочник наименований и характеристик автомобилей
Kod счетчик, ключевое поле
Name строка ( 50 ) марка автомобиля
Gruz целое грузоподъемность, кг ( только для грузовых )
Vid = Tip.Kod
Avto Справочник автомобилей организации
Kod счетчик, ключевое поле
Avto = SrAvto.Kod
GosNom строка ( 12 ) госномер
NomKuz строка ( 12 ) номер кузова
NomDvig строка ( 12 ) номер двигателя
NomShas строка ( 12 ) номер шасси
Photo строка ( 150 ) путь к файлу фото автомобиля ( не используется в запросах )
Rash целое расход, л/100км
Otv строка ( 50 ) ФИО ответственного
Smena таблица выездов автомобилей
Kod счетчик, ключевое поле
Mash = Avto.Kod
Dat краткий формат даты дата смены
Beg краткий формат времени время выезда
Kon краткий формат времени время заезда
NacProb целое показания пробега при выезде
KonProb целое показания пробега при въезде
В течение одного дня один автомобиль совершает один выезд. Без перехода смены на следующий день.
Базы справочников заполнены, база движений заполнена с 3.2.2014 по 7.2.2014 – рабочая неделя. Поэтому при отборе дат вводится всего один параметр – текущая дата. Отчеты за период, для которых нужно вводить два параметра – начальная и конечная дата отчета, не создавались.
Было создано несколько вспомогательных запросов
1. Список дат
SELECT [dat] FROM Smena GROUP BY [dat];
2. Количество выездов
SELECT [dat], count([kod]) AS viezd FROM Smena GROUP BY [dat];
Суммирует количество выездов по датам
3. Пробег и время работы
SELECT ([konprob]-[nacprob]) AS probeg, datediff('n',[beg],[kon]) AS rabota, *
FROM Smena ORDER BY [dat], [mash];
Основной запрос, по данным которого создаются все остальные. Выдает значения суточного пробега для каждого автомобиля и время рабочей смены в минутах.
4. Расход топлива
SELECT [probeg]/100*[rash] AS rashod, a.kod AS kod_avto, *
FROM [Пробег и время работы] AS pr INNER JOIN Avto AS a ON pr.mash=a.Kod;
Определяется расход топлива на основании суточного пробега и нормы расхода.
5. Расход по возрастанию
SELECT [dat], [rashod], [mash], * FROM [Расход топлива] ORDER BY [dat], [rashod], [mash];
6. Расход по убыванию
SELECT [dat], [rashod], [mash], * FROM [Расход топлива] ORDER BY [dat], [rashod] DESC , [mash];
7. Пробег по возрастанию
SELECT [dat], [probeg], [mash], * FROM [Расход топлива] ORDER BY [dat], [probeg], [mash];
8. Пробег по убыванию
SELECT [dat], [probeg], [mash], * FROM [Расход топлива] ORDER BY [dat], [probeg] DESC , [mash];
Запросы 5-8 используются для формирования статистических отчетов за смену.
9. Минимальный пробег
SELECT s.dat,
(select top 1 probeg from [Пробег по возрастанию] ss1 where ss1.dat=s.dat) AS prob,
(select top 1 mash from [Пробег по возрастанию] ss2 where ss2.dat=s.dat) AS mas
FROM [Список дат] AS s;
10. Максимальный пробег
SELECT s.dat,
(select top 1 probeg from [Пробег по убыванию] ss1 where ss1.dat=s.dat) AS prob,
(select top 1 mash from [Пробег по убыванию] ss2 where ss2.dat=s.dat) AS mas
FROM [Список дат] AS s;
11. Минимальный расход
SELECT s.dat,
(select top 1 rashod from [Расход по возрастанию] ss1 where ss1.dat=s.dat) AS ras,
(select top 1 mash from [Расход по возрастанию] ss2 where ss2.dat=s.dat) AS mas
FROM [Список дат] AS s;
12. Максимальный расход
SELECT s.dat,
(select top 1 rashod from [Расход по убыванию] ss1 where ss1.dat=s.dat) AS ras,
(select top 1 mash from [Расход по убыванию] ss2 where ss2.dat=s.dat) AS mas
FROM [Список дат] AS s;
В запросах 9-12 используются вложенные подзапросы. Разберем их подробнее на примере
(select top 1 probeg from [Пробег по возрастанию] ss1 where ss1.dat=s.dat)
Данные берутся из запроса [Пробег по возрастанию] , в котором данные отсортированы в порядке возрастания расхода за каждую дату. Для начала отбираются только те данные, которые удовлетворяют условию нужной даты - where ss1.dat=s.dat . Т.к. за эту дату может быть несколько записей, а нам нужно выбрать минимальную, то возвращаем только первую строку - top 1 . Поскольку во вложенном запросе может возвращаться только одно значение, то указываем его – используем поле probeg. Именно поэтому приходится использовать два запроса для вывода данных о минимальном пробеге за этот день и код автомобиля, у которого это значение. Если не нужно знать, у какого автомобиля минимальный пробег за этот день, то можно не использовать второй вложенный запрос.
Вот и добрались до запросов для задания.
13. 1_Расход топлива с параметром даты и нужными полями вывода
SELECT [SprAvto].[name],
[Расход топлива].[gosnom], [Расход топлива].[otv],
[Расход топлива].[beg], [Расход топлива].[kon],
[Расход топлива].[probeg], [Расход топлива].[rashod]
FROM [Расход топлива] INNER JOIN SprAvto ON [Расход топлива].[avto]=[SprAvto].[Kod]
WHERE ((([Расход топлива].[Dat])=[ДатаОтчета]));
Используем соединение INNER JOIN для получения данных о марке авто из справочника.
14. 2_Расход топлива в течение недели автомобилями с нужными полями
SELECT [Расход топлива].[dat], [SprAvto].[name],
[Расход топлива].[gosnom], [Расход топлива].[otv],
[Расход топлива].[beg], [Расход топлива].[kon],
[Расход топлива].[probeg], [Расход топлива].[rashod]
FROM [Расход топлива] INNER JOIN SprAvto ON [Расход топлива].[avto]=[SprAvto].[Kod]
ORDER BY [SprAvto].[name], [Расход топлива].[gosnom], [Расход топлива].[dat];
Сортировка производится по наименованию автомобиля, его госномеру и дате смены.
15. 3_Статистика по дням с нужными полями
SELECT [Количество выездов].[dat],
[Количество выездов].[viezd],
[Минимальный пробег].[prob],
[Максимальный пробег].[prob]
FROM ([Минимальный пробег] INNER JOIN [Максимальный пробег] ON [Минимальный пробег].[dat]=[Максимальный пробег].[dat]) INNER JOIN [Количество выездов] ON [Максимальный пробег].[dat]=[Количество выездов].[dat];
В этом запросе соединено три отчета со статистикой. Т.к. во всех используются одни и те же исходные данные, то за конкретную дату будут данные либо во всех таблицах ( если был хотя бы один выезд в этот день ) , либо данных не будет и эта дата выводиться не будет ( если выездов не было ) .
Дополнительно создано еще несколько запросов, но они повторяют уже описанные с небольшими изменениями, что видно по их названию.
Такое количество запросов создано для простоты понимания работы SQL. Любой из запросов можно написать, используя только основные таблицы, но тогда будет сложно разобраться в десятке строк.
Базу ( немного подкорректировал ) отправлю на почтовый ящик.
С уважением.