FoxClub Forums > Visual Foxpro, Foxpro for DOS > Как сформировать журнал из 2 таблиц? ::

• Войти

Как сформировать журнал из 2 таблиц?
shumik73 Автор Сообщений: 513 Откуда: Алматы Дата регистрации: 10.05.2006	shumik73 23.03.23 19:10:25 Добрый день всем! Есть таблица с заявками - она периодически пополняется CREATE TABLE Z ( ID_ZAYVKA int, NAME_ZAYVKA char (100)) INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (1,'Заявка 1') INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (2,'Заявка 2') INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (3,'Заявка 3') INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (4,'Заявка 4') INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (5,'Заявка 5') INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (6,'Заявка 6') Пользователи выбирают заявки и формируют оплаты Могут выбрать одну заявку, а могут несколько В момент формирования оплаты происходит проверка каждой заявки - чтобы заявка не смогла быть оплачена дважды Таким образом сформировалась вот такая таблица оплат: CREATE TABLE O ( ID_OPLAT int, NAME_OPLAT char (100), LIST_OPLAT char(20) ) INSERT INTO O (ID_OPLAT, NAME_OPLAT, LIST_OPLAT) VALUES (1,'Оплата 1','1,2') INSERT INTO O (ID_OPLAT, NAME_OPLAT, LIST_OPLAT) VALUES (2,'Оплата 2','3') INSERT INTO O (ID_OPLAT, NAME_OPLAT, LIST_OPLAT) VALUES (3,'Оплата 3','4,5') В каждой строке оплаты есть поле LIST_OPLAT где содержится номера заявок с которых была сформирована оплата Пользователь отрывает журнал с заявками, который формируется вот с такого запроса. при этом он видит все заявки видит какие заявки оплачены, а какие нет. видит номера с какой заявки - какой номер оплаты SELECT Z.ID_ZAYVKA, Z.NAME_ZAYVKA, ISNULL(O.ID_OPLAT,0) as ID_OPLAT, ISNULL(O.NAME_OPLAT,'') as NAME_OPLAT FROM Z LEFT OUTER JOIN O ON CHARINDEX(CHAR(44)+TRIM(CAST(ISNULL(Z.ID_ZAYVKA,SPACE(0)) as char))+CHAR(44),CHAR(44)+TRIM(CAST(ISNULL(O.LIST_OPLAT,SPACE(0)) as char))+CHAR(44))>0 Такой SQL-запрос не стандартной как обычно "ON Z.ID_ZAYVKA=O.LIST_OPLAT" необходимо зная Z.ID_ZAYVKA найти и отобразить все записи из таблицы O, где в поле O.LIST_OPLAT может нахотся этот самый Z.ID_ZAYVKA Использование CHARINDEX в этом случае не оптимальный вариант Ситуация в том что таблица заявок в 10 тыс записей открывается за 5-7 секунд. а запрос указанный выше грузится 40 секунд. Если бы с одной заявки формировалась только одна оплата, то поле O.LIST_OPLAT было бы цифровое. SELECT Z.ID_ZAYVKA, Z.NAME_ZAYVKA, ISNULL(O.ID_OPLAT,0) as ID_OPLAT, ISNULL(O.NAME_OPLAT,'') as NAME_OPLAT FROM Z LEFT OUTER JOIN O ON Z.ID_ZAYVKA=O.LIST_OPLAT Вариант Split тоже не вариант ON Z.ID_ZAYVKA IN (SELECT value FROM STRING_SPLIT(O.LIST_OPLAT, ',')) надо то всего из двух таблиц заявок и оплат сформировать один журнал [attachment 36520 .png]

shumik73
Автор

Сообщений: 513
Откуда: Алматы
Дата регистрации: 10.05.2006

shumik73

23.03.23 19:10:25

Добрый день всем!

Есть таблица с заявками - она периодически пополняется


CREATE TABLE Z (    ID_ZAYVKA int,    NAME_ZAYVKA char (100))
INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (1,'Заявка 1')
INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (2,'Заявка 2')
INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (3,'Заявка 3')
INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (4,'Заявка 4')
INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (5,'Заявка 5')
INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (6,'Заявка 6')

Пользователи выбирают заявки и формируют оплаты
Могут выбрать одну заявку, а могут несколько
В момент формирования оплаты происходит проверка каждой заявки - чтобы заявка не смогла быть оплачена дважды
Таким образом сформировалась вот такая таблица оплат:


CREATE TABLE O (    ID_OPLAT int,    NAME_OPLAT char (100), LIST_OPLAT char(20) )
INSERT INTO O (ID_OPLAT, NAME_OPLAT, LIST_OPLAT) VALUES (1,'Оплата 1','1,2')
INSERT INTO O (ID_OPLAT, NAME_OPLAT, LIST_OPLAT) VALUES (2,'Оплата 2','3')
INSERT INTO O (ID_OPLAT, NAME_OPLAT, LIST_OPLAT) VALUES (3,'Оплата 3','4,5')

В каждой строке оплаты есть поле LIST_OPLAT где содержится номера заявок с которых была сформирована оплата

Пользователь отрывает журнал с заявками, который формируется вот с такого запроса.

при этом он видит все заявки

видит какие заявки оплачены, а какие нет.

видит номера с какой заявки - какой номер оплаты


SELECT  Z.ID_ZAYVKA, Z.NAME_ZAYVKA, ISNULL(O.ID_OPLAT,0) as ID_OPLAT, ISNULL(O.NAME_OPLAT,'') as NAME_OPLAT
FROM    Z  LEFT OUTER JOIN O 
ON CHARINDEX(CHAR(44)+TRIM(CAST(ISNULL(Z.ID_ZAYVKA,SPACE(0)) as char))+CHAR(44),CHAR(44)+TRIM(CAST(ISNULL(O.LIST_OPLAT,SPACE(0)) as char))+CHAR(44))>0

Такой SQL-запрос не стандартной как обычно "ON Z.ID_ZAYVKA=O.LIST_OPLAT"
необходимо зная Z.ID_ZAYVKA найти и отобразить все записи из таблицы O, где в поле O.LIST_OPLAT может нахотся этот самый Z.ID_ZAYVKA
Использование CHARINDEX в этом случае не оптимальный вариант

Ситуация в том что таблица заявок в 10 тыс записей открывается за 5-7 секунд.
а запрос указанный выше грузится 40 секунд.

Если бы с одной заявки формировалась только одна оплата, то поле O.LIST_OPLAT было бы цифровое.


SELECT  Z.ID_ZAYVKA, Z.NAME_ZAYVKA, ISNULL(O.ID_OPLAT,0) as ID_OPLAT, ISNULL(O.NAME_OPLAT,'') as NAME_OPLAT
FROM    Z  LEFT OUTER JOIN O 
ON Z.ID_ZAYVKA=O.LIST_OPLAT

Вариант Split тоже не вариант


ON Z.ID_ZAYVKA IN (SELECT value FROM STRING_SPLIT(O.LIST_OPLAT, ','))

надо то всего из двух таблиц заявок и оплат сформировать один журнал

[attachment 36520 .png]

Re: Как сформировать журнал из 2 таблиц?
leonid Сообщений: 3204 Откуда: Рига Дата регистрации: 03.02.2006	leonid 23.03.23 19:18:02 shumik73 В каждой строке оплаты есть поле LIST_OPLAT где содержится номера заявок с которых была сформирована оплата Вот что бы я посоветовал, это почитать, что такое Первая нормальная форма, и почему не следует ее нарушать. А заодно, почему не следует нарушать вторую и третью.

Re: Как сформировать журнал из 2 таблиц?

leonid

Сообщений: 3204
Откуда: Рига
Дата регистрации: 03.02.2006

leonid

23.03.23 19:18:02

shumik73
В каждой строке оплаты есть поле LIST_OPLAT где содержится номера заявок с которых была сформирована оплата

Вот что бы я посоветовал, это почитать, что такое Первая нормальная форма, и почему не следует ее нарушать. А заодно, почему не следует нарушать вторую и третью.

Re: Как сформировать журнал из 2 таблиц?
sphinx Сообщений: 31179 Откуда: Каменск-Уральски Дата регистрации: 22.11.2006	sphinx 23.03.23 21:34:51 Прмсоединяюсь. INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (1,'Заявка 1') Разве код заявки неоднозначно идентифицирует НАЗВАНИЕ заявки? Если так - то это неправильно, и надо нормализовать отношения между сущностями. ------------------ "Veni, vidi, vici!"(с)

Re: Как сформировать журнал из 2 таблиц?

sphinx

Сообщений: 31179
Откуда: Каменск-Уральски
Дата регистрации: 22.11.2006

sphinx

23.03.23 21:34:51

Прмсоединяюсь.


INSERT INTO Z (ID_ZAYVKA, NAME_ZAYVKA) VALUES (1,'Заявка 1')

Разве код заявки неоднозначно идентифицирует НАЗВАНИЕ заявки? Если так - то это неправильно, и надо нормализовать отношения между сущностями.

------------------
"Veni, vidi, vici!"(с)

Re: Как сформировать журнал из 2 таблиц?
Артём Сообщений: 116 Дата регистрации: 23.04.2001	Артём 23.03.23 22:26:45 а я бы попробовал сразу учесть, что будут недоплаты и переплаты. то есть , сумма оплаты не обязательно равна сумме заявок Исправлено 1 раз(а). Последнее : Артём, 23.03.23 22:27

Re: Как сформировать журнал из 2 таблиц?
PaulWist Сообщений: 14618 Дата регистрации: 01.04.2004	PaulWist 24.03.23 09:26:17 Это что за сервер БД (MSSQL)? STRING_SPLIT ------------------ Есть многое на свете, друг Горацио... Что и не снилось нашим мудрецам. (В.Шекспир Гамлет)

Re: Как сформировать журнал из 2 таблиц?
Владимир Максимов Сообщений: 14097 Откуда: Москва Дата регистрации: 02.09.2000	Владимир Максимов 24.03.23 09:58:42 SELECT Z.ID_ZAYVKA, Z.NAME_ZAYVKA, ISNULL(O.ID_OPLAT,0) as ID_OPLAT, ISNULL(O.NAME_OPLAT,'') as NAME_OPLAT FROM Z LEFT JOIN O ON ','+RTRIM(LTRIM(O.LIST_OPLAT))+',' like '%,' + cast(Z.ID_ZAYVKA as nvarchar(20)) + ',%' Общая идея, сформировать условие вида ",1,2,3," LIKE "%,1,%" Как это лучше сделать, зависит от SQL-сервера. Хотя, похоже, речь идет об MS SQL. Ну и еще от того, как номера записаны в списке. Возможно, надо будет еще удалить внутренние пробелы. Т.е. не только ведущие/концевые, но и которые могут быть между запятыми PS: Ну, про то, что лучше бы, вместо (в дополнение) поля O.LIST_OPLAT сделать дополнительную (подчиненную) таблицу со списком кодов заявок уже сказали PPS: Хотя, да, через CHARINDEX() именно это и сделано. Просто сразу было не понятно из-за громоздкой конструкции Ну, остается только проверить, что будет быстрее работать Like или CHARINDEX() Исправлено 2 раз(а). Последнее : Владимир Максимов, 24.03.23 10:03

Re: Как сформировать журнал из 2 таблиц?

Владимир Максимов

Сообщений: 14097
Откуда: Москва
Дата регистрации: 02.09.2000

Владимир Максимов

24.03.23 09:58:42


SELECT  
    Z.ID_ZAYVKA, 
    Z.NAME_ZAYVKA, 
    ISNULL(O.ID_OPLAT,0) as ID_OPLAT, 
    ISNULL(O.NAME_OPLAT,'') as NAME_OPLAT
FROM    Z 
LEFT JOIN O ON ','+RTRIM(LTRIM(O.LIST_OPLAT))+',' like '%,' + cast(Z.ID_ZAYVKA as nvarchar(20)) + ',%'

Общая идея, сформировать условие вида

",1,2,3," LIKE "%,1,%"

Как это лучше сделать, зависит от SQL-сервера. Хотя, похоже, речь идет об MS SQL.

Ну и еще от того, как номера записаны в списке. Возможно, надо будет еще удалить внутренние пробелы. Т.е. не только ведущие/концевые, но и которые могут быть между запятыми

PS: Ну, про то, что лучше бы, вместо (в дополнение) поля O.LIST_OPLAT сделать дополнительную (подчиненную) таблицу со списком кодов заявок уже сказали

PPS: Хотя, да, через CHARINDEX() именно это и сделано. Просто сразу было не понятно из-за громоздкой конструкции

Ну, остается только проверить, что будет быстрее работать Like или CHARINDEX()

Исправлено 2 раз(а). Последнее : Владимир Максимов, 24.03.23 10:03

Re: Как сформировать журнал из 2 таблиц?
ssa Сообщений: 13007 Откуда: Москва Дата регистрации: 23.03.2005	ssa 24.03.23 10:09:57 PaulWist Это что за сервер БД (MSSQL)? STRING_SPLIT Угу, не самые старые версии. ------------------ Лень - это неосознанная мудрость.

Re: Как сформировать журнал из 2 таблиц?
Владимир Максимов Сообщений: 14097 Откуда: Москва Дата регистрации: 02.09.2000	Владимир Максимов 24.03.23 10:30:14 CHARINDEX(CHAR(44)+TRIM(CAST(ISNULL(Z.ID_ZAYVKA,SPACE(0)) as char))+CHAR(44),CHAR(44)+TRIM(CAST(ISNULL(O.LIST_OPLAT,SPACE(0)) as char))+CHAR(44))>0 Конструкция избыточная. Много лишних преобразований, которые могут в итоге увеличить время выборки IsNull() - это имеет смысл, если в самих полях может быть значение NULL. Но исходя из описания такого быть не может. TRIM(CAST(Z.ID_ZAYVKA as char)) - кроме типа char есть еще тип varchar, который как раз тем и отличается, что не дополняет поле концевыми пробелами. Т.е. после преобразования в тип varchar нет необходимости отсекать ведущие и концевые пробелы через TRIM() Итого, в остатке, получается так CHARINDEX(','+CAST(Z.ID_ZAYVKA as varchar)+',', ','+TRIM(O.LIST_OPLAT)+',')>0 Возможно, такое "очищенное" выражение будет работать быстрее Исправлено 2 раз(а). Последнее : Владимир Максимов, 24.03.23 10:36

Re: Как сформировать журнал из 2 таблиц?

Владимир Максимов

Сообщений: 14097
Откуда: Москва
Дата регистрации: 02.09.2000

Владимир Максимов

24.03.23 10:30:14


CHARINDEX(CHAR(44)+TRIM(CAST(ISNULL(Z.ID_ZAYVKA,SPACE(0)) as char))+CHAR(44),CHAR(44)+TRIM(CAST(ISNULL(O.LIST_OPLAT,SPACE(0)) as char))+CHAR(44))>0

Конструкция избыточная. Много лишних преобразований, которые могут в итоге увеличить время выборки

IsNull() - это имеет смысл, если в самих полях может быть значение NULL. Но исходя из описания такого быть не может.

TRIM(CAST(Z.ID_ZAYVKA as char)) - кроме типа char есть еще тип varchar, который как раз тем и отличается, что не дополняет поле концевыми пробелами. Т.е. после преобразования в тип varchar нет необходимости отсекать ведущие и концевые пробелы через TRIM()

Итого, в остатке, получается так


CHARINDEX(','+CAST(Z.ID_ZAYVKA as varchar)+',', ','+TRIM(O.LIST_OPLAT)+',')>0

Возможно, такое "очищенное" выражение будет работать быстрее

Исправлено 2 раз(а). Последнее : Владимир Максимов, 24.03.23 10:36

Re: Как сформировать журнал из 2 таблиц?
PaulWist Сообщений: 14618 Дата регистрации: 01.04.2004	PaulWist 24.03.23 10:38:00 Не будет, будет NL, поскольку неизвестна статистика "правой" таблицы в соединении. ------------------ Есть многое на свете, друг Горацио... Что и не снилось нашим мудрецам. (В.Шекспир Гамлет) Исправлено 1 раз(а). Последнее : PaulWist, 24.03.23 11:21

Re: Как сформировать журнал из 2 таблиц?

PaulWist

Сообщений: 14618
Дата регистрации: 01.04.2004

PaulWist

24.03.23 10:38:00

Не будет, будет NL, поскольку неизвестна статистика "правой" таблицы в соединении.

------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)

Исправлено 1 раз(а). Последнее : PaulWist, 24.03.23 11:21

Re: Как сформировать журнал из 2 таблиц?
ssa Сообщений: 13007 Откуда: Москва Дата регистрации: 23.03.2005	ssa 24.03.23 14:40:43 shumik73 Вариант Split тоже не вариант ON Z.ID_ZAYVKA IN (SELECT value FROM STRING_SPLIT(O.LIST_OPLAT, ',')) надо то всего из двух таблиц заявок и оплат сформировать один журнал [attachment 36520 .png] Правильно надо всё использовать... SELECT z.ID_ZAYVKA int, NAME_ZAYVKA, NAME_OPLAT FROM #Z z left join ( select NAME_OPLAT,value as ID_ZAYVKA from #O cross apply STRING_SPLIT(#O.LIST_OPLAT, ',') ) t on Z.ID_ZAYVKA = t.ID_ZAYVKA ------------------ Лень - это неосознанная мудрость.

Re: Как сформировать журнал из 2 таблиц?

ssa

Сообщений: 13007
Откуда: Москва
Дата регистрации: 23.03.2005

ssa

24.03.23 14:40:43

shumik73
Вариант Split тоже не вариант
ON Z.ID_ZAYVKA IN (SELECT value FROM STRING_SPLIT(O.LIST_OPLAT, ','))
надо то всего из двух таблиц заявок и оплат сформировать один журнал

[attachment 36520 .png]

Правильно надо всё использовать...


SELECT z.ID_ZAYVKA int, NAME_ZAYVKA, NAME_OPLAT 
FROM #Z z
left join 
(
select NAME_OPLAT,value as ID_ZAYVKA from #O
cross apply STRING_SPLIT(#O.LIST_OPLAT, ',')
) t on Z.ID_ZAYVKA = t.ID_ZAYVKA

------------------
Лень - это неосознанная мудрость.

Re: Как сформировать журнал из 2 таблиц?
Vedmak Сообщений: 5967 Откуда: CiTY Дата регистрации: 30.10.2003	Vedmak 24.03.23 18:27:20 JSON or HTML. Логгирование событий приложения приятнее анализировать без зависимости от самого приложения. ------------------ Говорить стоит лишь для тех, кто слушает.

:: Список Форумов•Список тем•Поиск•Войти

Извините, только зарегистрированные пользователи могут оставлять сообщения в этом форуме.

On-line: 32 PaulWist (Гостей: 31)