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

Откуда:
Сообщений: 2
Есть база данных (СУБД MS SQL 2008) с таблицами

машины
CREATE TABLE [dbo].[Cars](
[CarID] int NOT NULL,
[CurRouteOrder] int NULL, -- порядковый номер текущего маршрута
[CarDescription] nvarchar(30) NULL
)


маршруты
CREATE TABLE [dbo].[Routes](
[RouteId] int NOT NULL,
[RouteOrder] int, -- порядковый номер текущего маршрута
[CarID] int NOT NULL,
[RouteDescription] nvarchar(30) NULL
)


маршрутные точки
CREATE TABLE [dbo].[RoutePoints](
[RouteId] int NOT NULL,
[NRP] int NOT NULL,
[status] int NOT NULL, -- 3 - доставка пропущена
[DeliveryTime] datetime NOT NULL, -- планируемое время доставки
[Address] nvarchar(30) NOT NULL,
)


нужно для каждой машины вывести CarDescription, количество пропущенных точек суммарно в текущем и предыдущем маршрутах,
наиболее раннее пропущенное время доставки в текущем и предыдущем маршрутах, а также строчки с номерами 3-х пропущенных
точек маршрута с минимальными номерами для текущего и предыдущего маршрута. Предыдущий маршрут - с максимальным RouteOrder
меньше текущего для данной машины.

например:

Газель A777AA777   12   2021.04.20 10:45   2,5   5,8,9
.....
12 - количество пропущенный точек доставки суммарно в текущем и предыдущем маршрутах.
2021.04.20 10:45 - наиболее раннее пропущенное время доставки.
2,5 - строка с номерами пропущенных точек доставки в текущем маршруте через запятую.
5,8,9 - тоже, но для предыдущего маршрута.

RouteId текущего и предыдущего маршрутов для CarID = NNN
SELECT @CurRouteId=RouteId, @PrevRouteId=(SELECT TOP(1) RouteId FROM dbo.Routes WHERE RouteOrder < Curr.RouteOrder AND CarID=NNN ORDER BY RouteOrder DESC) FROM dbo.Routes AS Curr WHERE CarID=NNN and RouteOrder=(SELECT CurRouteOrder from dbo.Cars WHERE CarID=NNN)


Вопросы:
1)
для текущего и предыдущего маршрутов получение пропущенного времени доставки и кол-во пропущенных точек
SELECT COUNT(*), MIN(DeliveryTime) FROM dbo.RoutePoints WHERE (RouteId=@CurRouteId OR RouteId=@PrevRouteId) AND status=3

получение номеров 3-х пропущенных точек текущего маршрута с минимальными номерами в виде строки:
DECLARE @STR NVARCHAR(30);

SELECT TOP(3) @STR = CASE WHEN @STR IS NULL THEN CAST(NRP AS NVARCHAR) ELSE @STR + ', ' + CAST(NRP AS NVARCHAR) END FROM dbo.RoutePoints WHERE RouteId=@CurRouteId AND status=3 ORDER BY NR ASC


для предыдущего маршрута - аналогично, только RouteId=@PrevRouteId

Можно ли эти операции как-то объединить в одну? Если да, как? Или может оставить их как есть в надежде, что прочитанные
записи кэшируются и значительного проигрыша от прохода второй раз по тем же записям не будет.

2) как более или менее оптимально получить таблицу, содержащую CarID, CarDescription, RouteId текущего, RouteId предыдущего
маршрутов. Т.е. хорошо бы, чтобы MS SQL Server догадался пройти по таблице Routes только один раз (т.к. она заметно больше, чем Cars)

Если кто-нибудь поможет собрать все перечисленное выше в один запрос, получающий сразу нужную таблицу данных - будет вообще здорово.
29 апр 21, 17:41    [22316495]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос с формированием текстовых строк  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8581
m7n5g,

слишком сложно, чтобы вдумываться. Если хотите обсудить, то напишите что вы хотите получить и заполните таблицы демонстрационными данными.

Сообщение было отредактировано: 30 апр 21, 17:20
30 апр 21, 17:27    [22317022]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос с формированием текстовых строк  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
И используйте тег [SRС] при оформлении языка SQL
30 апр 21, 17:58    [22317036]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос с формированием текстовых строк  [new]
m7n5g
Member

Откуда:
Сообщений: 2
Тестовые данные
+
-- (CarID, CurRouteOrder, CarDescription)
INSERT INTO dbo.Cars VALUES (1, 1, 'Car_1')
INSERT INTO dbo.Cars VALUES (2, 2, 'Car_2')
INSERT INTO dbo.Cars VALUES (3, 4, 'Car_3')

-- (RouteID, RouteOrder, CarID, RouteDescription)
INSERT INTO dbo.Routes VALUES (1, 1, 1, 'R_1')
INSERT INTO dbo.Routes VALUES (2, 2, 1, 'R_2')

INSERT INTO dbo.Routes VALUES (3, 1, 2, 'R_3')
INSERT INTO dbo.Routes VALUES (4, 2, 2, 'R_4')
INSERT INTO dbo.Routes VALUES (5, 3, 2, 'R_5')

INSERT INTO dbo.Routes VALUES (9, 1, 3, 'R_9')
INSERT INTO dbo.Routes VALUES (8, 2, 3, 'R_8')
INSERT INTO dbo.Routes VALUES (7, 4, 3, 'R_7')
INSERT INTO dbo.Routes VALUES (6, 5, 3, 'R_6')

--(RouteId, NRP, status, DeliveryTime, Address)
INSERT INTO dbo.RoutePoints VALUES (1, 1, 1, '2021-01-20T10:20:00.000', 'A_1')
INSERT INTO dbo.RoutePoints VALUES (1, 2, 2, '2021-01-20T11:21:00.000', 'A_2')
INSERT INTO dbo.RoutePoints VALUES (1, 3, 3, '2021-01-20T12:30:00.000', 'A_3')
INSERT INTO dbo.RoutePoints VALUES (1, 4, 3, '2021-01-20T14:00:00.000', 'A_4')

INSERT INTO dbo.RoutePoints VALUES (2, 1, 2, '2021-01-21T09:11:00.000', 'A_5')
INSERT INTO dbo.RoutePoints VALUES (2, 2, 3, '2021-01-21T10:30:00.000', 'A_6')
INSERT INTO dbo.RoutePoints VALUES (2, 3, 1, '2021-01-21T11:50:00.000', 'A_7')
INSERT INTO dbo.RoutePoints VALUES (2, 4, 3, '2021-01-21T12:20:00.000', 'A_8')

INSERT INTO dbo.RoutePoints VALUES (3, 1, 3, '2021-01-20T10:20:00.000', 'A_8')
INSERT INTO dbo.RoutePoints VALUES (3, 2, 1, '2021-01-20T11:20:00.000', 'A_9')
INSERT INTO dbo.RoutePoints VALUES (3, 3, 1, '2021-01-20T12:20:00.000', 'A_10')
INSERT INTO dbo.RoutePoints VALUES (3, 4, 1, '2021-01-20T13:20:00.000', 'A_11')

INSERT INTO dbo.RoutePoints VALUES (4, 1, 1, '2021-01-20T10:20:00.000', 'A_12')
INSERT INTO dbo.RoutePoints VALUES (4, 2, 3, '2021-01-20T11:20:00.000', 'A_13')
INSERT INTO dbo.RoutePoints VALUES (4, 3, 3, '2021-01-20T12:20:00.000', 'A_14')
INSERT INTO dbo.RoutePoints VALUES (4, 4, 2, '2021-01-20T13:20:00.000', 'A_15')

INSERT INTO dbo.RoutePoints VALUES (5, 1, 3, '2021-01-20T10:20:00.000', 'A_16')
INSERT INTO dbo.RoutePoints VALUES (5, 2, 3, '2021-01-20T11:20:00.000', 'A_17')
INSERT INTO dbo.RoutePoints VALUES (5, 3, 1, '2021-01-20T12:20:00.000', 'A_18')
INSERT INTO dbo.RoutePoints VALUES (5, 4, 1, '2021-01-20T13:20:00.000', 'A_19')

INSERT INTO dbo.RoutePoints VALUES (9, 1, 1, '2021-01-20T10:20:00.000', 'A_20')
INSERT INTO dbo.RoutePoints VALUES (9, 2, 1, '2021-01-20T11:20:00.000', 'A_21')
INSERT INTO dbo.RoutePoints VALUES (9, 3, 3, '2021-01-20T12:20:00.000', 'A_22')
INSERT INTO dbo.RoutePoints VALUES (9, 4, 1, '2021-01-20T13:20:00.000', 'A_23')

INSERT INTO dbo.RoutePoints VALUES (8, 1, 2, '2021-01-20T10:20:00.000', 'A_24')
INSERT INTO dbo.RoutePoints VALUES (8, 2, 2, '2021-01-20T11:20:00.000', 'A_25')
INSERT INTO dbo.RoutePoints VALUES (8, 3, 2, '2021-01-20T12:20:00.000', 'A_26')
INSERT INTO dbo.RoutePoints VALUES (8, 4, 3, '2021-01-20T13:20:00.000', 'A_27')

INSERT INTO dbo.RoutePoints VALUES (7, 1, 1, '2021-01-20T10:20:00.000', 'A_28')
INSERT INTO dbo.RoutePoints VALUES (7, 2, 3, '2021-01-20T11:20:00.000', 'A_29')
INSERT INTO dbo.RoutePoints VALUES (7, 3, 1, '2021-01-20T12:20:00.000', 'A_30')
INSERT INTO dbo.RoutePoints VALUES (7, 4, 3, '2021-01-20T13:20:00.000', 'A_31')

INSERT INTO dbo.RoutePoints VALUES (6, 1, 3, '2021-01-20T10:20:00.000', 'A_32')
INSERT INTO dbo.RoutePoints VALUES (6, 2, 3, '2021-01-20T11:20:00.000', 'A_33')
INSERT INTO dbo.RoutePoints VALUES (6, 3, 1, '2021-01-20T12:20:00.000', 'A_34')
INSERT INTO dbo.RoutePoints VALUES (6, 4, 3, '2021-01-20T13:20:00.000', 'A_35')


В итоге должно получиться
Cars.CarIDПропущено Первый пропуск Пропуски в текущем маршруте Пропуски в предыдущем
Car_122021-01-20T12:303; 4
Car_232021-01-20T10:2012; 3
Car_332021-01-20T11:2042; 4


Первый вопрос, как эффективно сформировать временную таблицу (а можно ли без нее обойтись?)
DECLARE @routeIdTbl TABLE
(
  CarID int,
  PrevRouteID int,  -- RouteID предыдущего маршрута
  CurRouteID int,   -- RouteID текущего маршрута
);


В этой таблице должно оказаться
CarID ID предыдущего маршрута ID текущего маршрута
1NULL1
2 34
387


Машин не очень много. Допустим 100.
А вот маршруты [Routes], скорее всего, чистить вообще никто не будет (нужна история). Т.е. их может быть десятки тысяч. [RoutePoints] в десятки раз больше.

Сообщение было отредактировано: 1 май 21, 15:00
1 май 21, 14:55    [22317226]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос с формированием текстовых строк  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8854
Маркетплейсы закончились, логисты подтянулись... Думаю что топик надо перенести в "Работа"
2 май 21, 09:53    [22317383]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос с формированием текстовых строк  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1314
SIMPLicity_
Маркетплейсы закончились, логисты подтянулись... Думаю что топик надо перенести в "Работа"

Да ну обычные ленивые студиозусы :)

зы помню у нас на практике БД
по фоксу разные задания были
так препод сказал вы ж тока данные набивайте нормальные
а то я была задача про поезд
так автор сделал поезд из 3 вагонов в каждом по 4 места
:)
4 май 21, 10:30    [22318140]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить