ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сложный запрос с формированием текстовых строк
6 сообщений из 6, страница 1 из 1
Сложный запрос с формированием текстовых строк
    #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
29.04.2021, 17:41
| Ответить | Цитировать | Написать  
Сложный запрос с формированием текстовых строк
    #40067674
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
m7n5g,

слишком сложно, чтобы вдумываться. Если хотите обсудить, то напишите что вы хотите получить и заполните таблицы демонстрационными данными.
...
Рейтинг: 0 / 0
30.04.2021, 17:27
| Ответить | Цитировать | Написать  
Сложный запрос с формированием текстовых строк
    #40067684
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
И используйте тег [SRС] при оформлении языка SQL
...
Рейтинг: 0 / 0
30.04.2021, 17:58
| Ответить | Цитировать | Написать  
Сложный запрос с формированием текстовых строк
    #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
01.05.2021, 14:55
| Ответить | Цитировать | Написать  
Сложный запрос с формированием текстовых строк
    #40067855
Фотография SIMPLicity_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SIMPLicity_
Участник
Маркетплейсы закончились, логисты подтянулись... Думаю что топик надо перенести в "Работа"
...
Рейтинг: 0 / 0
02.05.2021, 09:53
| Ответить | Цитировать | Написать  
Сложный запрос с формированием текстовых строк
    #40068265
Гулин Федор
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гулин Федор
Участник
SIMPLicity_
Маркетплейсы закончились, логисты подтянулись... Думаю что топик надо перенести в "Работа"

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

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


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