:: Visual Foxpro, Foxpro for DOS
• Войти
Можно ли решить запросом?
ry
Автор

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
07.06.18 09:12:33
Есть три таблицы (для примера сделаю три курсора с минимально необходимым количеством полей): 


create cursor good (good_id i, code c (8), name c(50), pro c(15)) && Изделия

insert into good values (1, "01010001", "Любое название 1", "АБ-123")

insert into good values (2, "01010002", "Любое название 2", "АБ-124")

insert into good values (3, "02010001", "Любое название 1", "АБ-123")

insert into good values (4, "02010002", "Любое название 2", "АБ-124")

insert into good values (5, "02020001", "Любое название 1", "АБ-123")

insert into good values (6, "02020002", "Любое название 3", "АБ-125")



create cursor material (mat_id i, name c(30)) && Материалы

insert into material values (1, "Материал 1")

insert into material values (2, "Материал 2")

insert into material values (3, "Материал 3")

insert into material values (4, "Материал 4")



create cursor norma (good_id i, mat_id i, quant i) && Нормы расхода

insert into norma values (1, 1, 1)

insert into norma values (1, 2, 5)

insert into norma values (1, 3, 10)

insert into norma values (2, 1, 1)

insert into norma values (2, 2, 5)

insert into norma values (2, 3, 10)

insert into norma values (3, 1, 1)

insert into norma values (3, 2, 10)

insert into norma values (3, 4, 5)

insert into norma values (4, 1, 1)

insert into norma values (4, 2, 5)

insert into norma values (4, 3, 5)

insert into norma values (5, 1, 1)

insert into norma values (5, 4, 1)

insert into norma values (5, 5, 1)

insert into norma values (6, 1, 1)

insert into norma values (6, 2, 1)

insert into norma values (6, 3, 1)

Нужно сделать выборку, отображающую различие в нормах расхода для изделий с одинаковым полем pro, сравнивая при этом записи для двух заданных значений left(code,4). Для записей с одинаковым left(code,4) значения поля pro уникальные.

Например, требуется сравнить изделия с left(code,4)=="0101" и left(code,4)=="0201", в результате надо получить выборку, которая отобразит лишь не совпадающие значения:

"01010001", "Любое название 1", "Материал 2", 5, "02010001", "Любое название 1", "Материал 2", 10
"01010001", "Любое название 1", "Материал 3", 10, "02010001", "Любое название 1", "Материал 3", 0
"01010001", "Любое название 1", "Материал 4", 0, "02010001", "Любое название 1", "Материал 4", 5
"01010002", "Любое название 2", "Материал 3", 10, "02010002", "Любое название 2", "Материал 3", 5

Решить без запросов смогу и сам, и вообще сомневаюсь, что здесь можно одним-двумя запросами обойтись, но если можно как-нибудь красиво извернуться, подскажите.
Ratings: 0 negative/0 positive
Re: Можно ли решить запросом?
AndyNigmatec

Сообщений: 1573
Откуда: Волгоград
Дата регистрации: 28.06.2015
AndyNigmatec
07.06.18 12:00:17
Если я прально понял задачу, то полагаю в лоб так: 

SELECT CAST(LEFT(a.code,4) as c(4)) as code1, a.code, a.name,;

    c.name as mat_name, b.quant;

    FROM good a JOIN norma b ON a.good_id=b.good_id;

    JOIN material c ON b.mat_id=c.mat_id;

    INTO CURSOR c1 READWRITE



lcCode1='0101'

lcCode2='0201'



SELECT DISTINCT a1.code, a1.name, a1.mat_name, a1.quant,;

    a2.code as code2, a2.name as name2, a2.mat_name as mat_name2, a2.quant as quant2;

    FROM c1 a1 JOIN c1 a2 ON a1.name=a2.name AND a1.code1==lcCode1 AND a2.code1==lcCode2;

    INTO CURSOR c2 READWRITE
Ratings: 0 negative/0 positive
Re: Можно ли решить запросом?
ry
Автор

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
07.06.18 12:00:59
Тему можно закрывать, уже реализовал через scan. Правда, время формирования отчета вышло около минуты (больше 20 тыс. изделий, больше сотни материалов, записей в нормах - более 500 тыс.). Но это не критично - выборка, можно считать, разовая.
Ratings: 0 negative/0 positive
Re: Можно ли решить запросом?
ry
Автор

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
07.06.18 12:03:29
AndyNigmatec
Если я прально понял задачу, то полагаю в лоб так: 
SELECT CAST(LEFT(a.code,4) as c(4)) as code1, a.code, a.name,;

    c.name as mat_name, b.quant;

    FROM good a JOIN norma b ON a.good_id=b.good_id;

    JOIN material c ON b.mat_id=c.mat_id;

    INTO CURSOR c1 READWRITE



lcCode1='0101'

lcCode2='0201'



SELECT DISTINCT a1.code, a1.name, a1.mat_name, a1.quant,;

    a2.code as code2, a2.name as name2, a2.mat_name as mat_name2, a2.quant as quant2;

    FROM c1 a1 JOIN c1 a2 ON a1.name=a2.name AND a1.code1==lcCode1 AND a2.code1==lcCode2;

    INTO CURSOR c2 READWRITE

Сегодня не успеваю, завтра попробую проверить на реальных данных.
Ratings: 0 negative/0 positive
Re: Можно ли решить запросом?
Igor Korolyov

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
07.06.18 12:52:18
LOCAL lcCode1, lcCode2

lcCode1 = "0101"

lcCode2 = "0201"



SELECT NVL(p1.pro, p2.pro), material.mat_id, material.NAME, ;

 p1.CODE, p1.NAME, p1.quant, p2.CODE, p2.NAME, p2.quant FROM ;

 (SELECT good.CODE, good.NAME, good.pro, NORMA.mat_id, NORMA.quant ;

 FROM good INNER JOIN NORMA ON NORMA.good_id = good.good_id ;

 WHERE LEFT(good.CODE,4) == m.lcCode1) p1 ;

 FULL OUTER JOIN ;

 (SELECT good.CODE, good.NAME, good.pro, NORMA.mat_id, NORMA.quant ;

 FROM good INNER JOIN NORMA ON NORMA.good_id = good.good_id ;

 WHERE LEFT(good.CODE,4) == m.lcCode2) p2 ;

 ON p2.pro == p1.pro AND p2.mat_id = p1.mat_id;

 LEFT JOIN material ON material.mat_id = NVL(p1.mat_id, p2.mat_id) ;

 WHERE NVL(p1.quant, 0) <> NVL(p2.quant, 0) ;

 ORDER BY 1, 2

Большого смысла писать "виртуальные" данные для различающихся материалов (там где null в курсоре) я не вижу. В принципе эти данные можно вывести, дописав ещё пару join по "странному" условию и брать поля code/name уже из g1 и g2 (если твоё уточнение про "уникальность" реально соблюдается) 
 INNER JOIN good g1 ON LEFT(g1.CODE, 4) == m.lcCode1 AND g1.pro = NVL(p1.pro, p2.pro) ;

 INNER JOIN good g2 ON LEFT(g2.CODE, 4) == m.lcCode2 AND g2.pro = NVL(p1.pro, p2.pro)
Только учти, что без индексов и по большим объёмам это будет лишний тормоз.

Дублировать наименование материала тоже не вижу смысла (как я понял логику постановки задачи, они в конечном курсоре в одной записи идентичны должны быть).

Структуры данных неоптимальна - если уж требуется работать по LEFT(good.CODE,4) то его стоило оформить отдельным полем. Тогда не нужен будет странноватый индекс по LEFT(good.CODE,4) для оптимизации такого рода запросов (или же играться с SET ANSI и длиной переменной, убрав LEFT из текста запроса - тогда и просто индекс по code поможет). Заодно можно сделать реально защищающий от "дубликатов по хитрому условию" candidate индекс - не плохо выглядящий LEFT(CODE,4)+pro а "красивый" code4+pro


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Можно ли решить запросом?
ry
Автор

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
08.06.18 10:56:21
To Igor Korolyov:
Почти то, что нужно. Дублировать название материала, конечно же, не обязательно. Это я просто в примере делал. Еще добавил отсев записей с пустым полем pro (то есть уникальности по полю на самом деле нет, так как пустые значения могут повторяться в пределах одного left(code,4). Проверил на реальных данных, работает быстро - 2-3 секунды. Правда, в выборку также попадают "лишние" изделия, которые есть только в одном из подзапросов (впрочем, таких оказалось не много, так что не проблема). А вот отобразить одновременно коды и названия изделий в обеих группах мне не удалось, что не понравилось заказчику. То есть хотелось бы полностью избавиться от NULL. Пока оставил свое решение как есть, время выполнения заказчика пока не напрягает.

Структура таблиц и поля code не оптимальная, но "так исторически сложилось" - менять не могу, надо только анализировать данные. Индексы есть по всем полям в запросе (кроме количества), но нет индекса по left(code,4). Все равно запрос работает почти мгновенно.

To AndyNigmatec:
Немного не то - получается что-то вроде декартова произведения отобранных записей.
Ratings: 0 negative/0 positive
Re: Можно ли решить запросом?
Igor Korolyov

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
08.06.18 13:13:00
Я имел в виду вот так добавлять код/имя для null-строк. Заодно использование INNER JOIN отбросит "изделия, которые есть только в одном из подзапросов".
Ну и оптимизация - раз структуру не поменять, то индекс по code можно вот таким финтом использовать - только обязательно задавать параметры 4-мя символами, иначе ANSI OFF и = не тот результат дадут. 
SET ANSI OFF

SELECT NVL(p1.pro, p2.pro) pro, material.mat_id, material.NAME material_name, ;

 g1.CODE goods_code1, g1.NAME goods_name1, CAST(NVL(p1.quant, 0) AS I) material_quantity1, ;

 g2.CODE goods_code2, g2.NAME goods_name2, CAST(NVL(p2.quant, 0) AS I) material_quantity2 FROM ;

 (SELECT good.CODE, good.NAME, good.pro, NORMA.mat_id, NORMA.quant ;

 FROM good INNER JOIN NORMA ON NORMA.good_id = good.good_id ;

 WHERE good.CODE = m.lcCode1) p1 ;

 FULL OUTER JOIN ;

 (SELECT good.CODE, good.NAME, good.pro, NORMA.mat_id, NORMA.quant ;

 FROM good INNER JOIN NORMA ON NORMA.good_id = good.good_id ;

 WHERE good.CODE = m.lcCode2) p2 ;

 ON p2.pro = p1.pro AND p2.mat_id = p1.mat_id;

 LEFT JOIN material ON material.mat_id = NVL(p1.mat_id, p2.mat_id) ;

 INNER JOIN good g1 ON g1.CODE = m.lcCode1 AND g1.pro = NVL(p1.pro, p2.pro) ;

 INNER JOIN good g2 ON g2.CODE = m.lcCode2 AND g2.pro = NVL(p1.pro, p2.pro) ;

 WHERE NVL(p1.quant, 0) <> NVL(p2.quant, 0) ;

 ORDER BY 1, 2


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Можно ли решить запросом?
ry
Автор

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
12.06.18 09:25:24
Сегодня добрался поэкспериментировать. Время выполнения запроса на реальных данных возросло (в зависимости от начальных параметров выборки) где-то до 10 секунд и даже до полминуты. При SET ANSI OFF и сравнении по предложенному варианту 4 символов с полным полем через "=" на реальных данных получил неверные результаты. С чем это связано, разбираться не стал. При сравнении через left(code,4) и "==" результаты верные, но для некоторых пар lcCode1 и lcCode2 неожиданно получил ошибку "File ...tmp is too large". Я так понимаю, промежуточный запрос формирует временной файл, вылетающий за предел 2 ГБ. В общем, решил дальше не продолжать. Конечно, перебор сканом работает дольше (от одной до трех минут), но зато выдает нужный результат, а время выполнения пока не критично.
Ratings: 0 negative/0 positive


:: Список Форумов•Список тем•Поиск•Войти
Извините, только зарегистрированные пользователи могут оставлять сообщения в этом форуме.

On-line: 21 (Гостей: 21)
© 2000-2024 Fox Club 
Яндекс.Метрика