Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сложный запрос с формированием текстовых строк / 6 сообщений из 6, страница 1 из 1
29.04.2021, 17:41
    #40067353
m7n5g
m7n5g
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сложный запрос с формированием текстовых строк
Есть база данных (СУБД 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
меньше текущего для данной машины.

например:

Код: plaintext
Газель 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)

Если кто-нибудь поможет собрать все перечисленное выше в один запрос, получающий сразу нужную таблицу данных - будет вообще здорово.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.04.2021, 17:27
    #40067674
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сложный запрос с формированием текстовых строк
m7n5g,

слишком сложно, чтобы вдумываться. Если хотите обсудить, то напишите что вы хотите получить и заполните таблицы демонстрационными данными.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.04.2021, 17:58
    #40067684
iap
iap
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сложный запрос с формированием текстовых строк
И используйте тег [SRС] при оформлении языка SQL
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.05.2021, 14:55
    #40067765
m7n5g
m7n5g
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сложный запрос с формированием текстовых строк
Тестовые данные
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
-- (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; 4Car_232021-01-20T10:2012; 3Car_332021-01-20T11:2042; 4

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



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

Машин не очень много. Допустим 100.
А вот маршруты [Routes], скорее всего, чистить вообще никто не будет (нужна история). Т.е. их может быть десятки тысяч. [RoutePoints] в десятки раз больше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.05.2021, 09:53
    #40067855
SIMPLicity_
SIMPLicity_
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сложный запрос с формированием текстовых строк
Маркетплейсы закончились, логисты подтянулись... Думаю что топик надо перенести в "Работа"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.05.2021, 10:30
    #40068265
Гулин Федор
Гулин Федор
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сложный запрос с формированием текстовых строк
SIMPLicity_
Маркетплейсы закончились, логисты подтянулись... Думаю что топик надо перенести в "Работа"

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

зы помню у нас на практике БД
по фоксу разные задания были
так препод сказал вы ж тока данные набивайте нормальные
а то я была задача про поезд
так автор сделал поезд из 3 вагонов в каждом по 4 места
:)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сложный запрос с формированием текстовых строк / 6 сообщений из 6, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 514ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1684738]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 121ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 305ms
total:  514ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]