Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > Составление SQL-запросов > Запрос на поиск мин. цены


Автор: lat 17.10.2009, 21:32
Есть таблица

Код

DROP TABLE 'Names';
CREATE TABLE 'Names' (
  id AUTOINCINTEGER,
  description VARCHAR(200),
  name1 VARCHAR(200),
  name2 VARCHAR(200),
  name_new VARCHAR(255),
  price FLOAT,
  file_id INTEGER
);


Товар может повторятся в "name_new". Нужно найти товар с минимальной стоимостью.
Вроде ничего сложного, но пока что мне этот запрос не дается. Да ещё и absolute database (ad) как-то долго обрабатывает вложенные запросы.
Первое что пришло в голову это
Код

SELECT n.description as description, n.name1 as name1, n.name2 as name2, n.name_new as name_new, f.name as file, min(n.price) as price                                 
FROM names as n, files as f 
WHERE n.file_id = f.id
GROUP BY n.description, n.name1, n.name2, n.name_new, f.name

Но это не есть гут, так как поле "f.name as file" разное и его сгруппировать нельзя (в принципе как и все остальные, так как повторятся 100% может только "name_new").

Тогда только так: 
Код

SELECT n.name_new as name_new, min(n.price) as price                                 
FROM names as n
GROUP BY n.name_new

В этом случаи все правильно. Кроме того что теперь не понятно как узнать значения всех остальных полей, для найденного мин. значения цены, кроме name_new???

Автор: Gluttton 17.10.2009, 21:44
Не совсем понятно... Описана одна таблица, а в приведенном запросе упоминается две... Что за таблица file?

По сути:
Что бы отобразить минимальные цены на продукцию, наименование, которой может повторяться, необхоимо произветси поиск минимальных значений с группировкой по ключу, а после выполнить соединение с исходной таблицей (по тому же ключу) и вывести имена с полученым в подзапросе результатом агрегата.
Код

select 
    Names.name_new,
    Sub.price
from Names,
(
    select
        id,
        min(price) as price
    from Names
        group by id
)   as Sub
    where Names.id=Sub.id


Несколько раз перечитывал сообщение, но так до конца не могу понять, что нужно...
Жду ответного сообщения smile , подходит ли предложеное мною решение, и если нет, то что не так?

Если нужно отображать не только name_new, а и name1 или name2, то необходимо внести соответствующие изменения в первую строку...
Код

    Names.name_new, name1, ...

Автор: lat 17.10.2009, 21:59
Цитата(Gluttton @  17.10.2009,  21:44 Найти цитируемый пост)
Не совсем понятно... Описана одна таблица, а в приведенном запросе упоминается две...

Хм, ну вы уже второй кто мне это пишет. Похоже что так и есть ... странно, неужто так коряво объясняю? =) 


Цитата(Gluttton @  17.10.2009,  21:44 Найти цитируемый пост)
 ... и вывести имена с полученым в подзапросе результатом агрегата.


Тот запрос что вы привели поверг меня в ступор ... первый раз такое вижу =) 
Это для какой БД? 
AD на отрез воспротивился ему, и обматерил сам себя таким образом: 
Код

[17.10.2009 21:52:55] Executing Query: 
select  names.name_new,
    Sub.price
from Names,
(
    select
        id,
        min(price) as price
    from names
        group by id
)   as Sub
    where names.id=Sub.id
[17.10.2009 21:52:55] Expression expected, but 'names' found at line 1, column 9 - Native error: 30160

Автор: Gluttton 17.10.2009, 22:05
Цитата(lat @  17.10.2009,  21:59 Найти цитируемый пост)
Хм, ну вы уже второй кто мне это пишет. Похоже что так и есть ... странно, неужто так коряво объясняю? =) 

Ну, как бы не факт, может это суббота на меня так действует smile, и я что то не так понимаю, но всё таки:
Цитата(lat @  17.10.2009,  21:32 Найти цитируемый пост)
FROM names as n, files as f 

таблицы две...

Цитата(lat @  17.10.2009,  21:59 Найти цитируемый пост)
Тот запрос что вы привели поверг меня в ступор ... первый раз такое вижу =) Это для какой БД? 

Чистый ANSI причем если не ошибаюсь, то даже 92-ой... Запрос обязан пойти на любой СУБД!
Я на сервере запрос, не проверял, поэтому ошибка может быть запросто...
Предлагаю искать её совместными усилиями smile ...

Может names зарезервированное слово и его нужно "обнять" кывычками... Или может быть среда в которой Вы работаете регистрозависима и ей не нравиться, что имена таблиц, то с большой буквы, то с маленькой...

Автор: lat 17.10.2009, 22:06
Попробую еще раз объяснить суть задачи.

Таблица Names
Код

CREATE TABLE 'Names' (
  id AUTOINCINTEGER,
  description VARCHAR(200),
  name1 VARCHAR(200),
  name2 VARCHAR(255),
  name_new VARCHAR(255),
  price FLOAT,
  file_id INTEGER
);


Таблица Files 
Код


CREATE TABLE Files (
  id AUTOINCINTEGER,
  name WIDESTRING(1000),
  date_open DATETIME,
  date_change DATETIME
);


Таблицы связанны между собой по ключу Files.id --->> Names.file_id
Пример данных:

Код

...
INSERT INTO 'Names' (id, description, name1, name2, name_new, price, file_id) values (50676, 'Цифровые фотоаппараты', 'CANON', 'CANON IXUS 110 IS Gold', 'canonixus110isgold', 375, 27);
INSERT INTO 'Names' (id, description, name1, name2, name_new, price, file_id) values (50677, 'Цифровые фотоаппараты', 'CANON', 'CANON IXUS 110 IS Gold', 'canonixus110isgold', 370, 28);
...


Задача: "Найти все данные по товару, который имеет наименьшую стоимость?"

Автор: Gluttton 17.10.2009, 22:09
http://forum.vingrad.ru/forum/topic-250915/anchor-entry1812148/15.html у топикстартера сервер возвращал такую же ошибку... Может поможет...

Добавлено через 3 минуты и 10 секунд
Цитата(lat @  17.10.2009,  22:06 Найти цитируемый пост)
Пример данных:КодincludeSyntax('sql');
...INSERT INTO 'Names' (id, description, name1, name2, name_new, price, file_id) values (50676, 'Цифровые фотоаппараты', 'CANON', 'CANON IXUS 110 IS Gold', 'canonixus110isgold', 375, 27);
INSERT INTO 'Names' (id, description, name1, name2, name_new, price, file_id) values (50676, 'Цифровые фотоаппараты', 'CANON', 'CANON IXUS 110 IS Gold', 'canonixus110isgold', 370, 28);...highlightSyntax('sql_ZTQxMm','sql');Задача: "Найти все данные по товару, который имеет наименьшую стоимость?"

Вставляются одинаковый ID, опечатка? Или так и должно быть по логике БД?

Автор: lat 17.10.2009, 22:12
Цитата(Gluttton @  17.10.2009,  22:05 Найти цитируемый пост)
Может names зарезервированное слово и его нужно "обнять" кывычками... Или может быть среда в которой Вы работаете регистрозависима и ей не нравиться, что имена таблиц, то с большой буквы, то с маленькой... 

Цитата(Gluttton @  17.10.2009,  22:09 Найти цитируемый пост)
Вот тут у топикстартера сервер возвращал такую же ошибку... Может поможет... 


Ошибка изменилась =)
Поменял название таблицы "Names" на "Tovar"
Теперь так:

Код

[17.10.2009 22:11:00] Executing Query: 
select  Tovar.name_new, Sub.price from Tovar, (
    select
        id,
        min(price) as price
    from Tovar
        group by id
)   as Sub
    where Tovar.id=Sub.id
[17.10.2009 22:11:00] Ambiguous field reference to 'id' - Native error: 10404

Автор: Gluttton 17.10.2009, 22:14
Цитата(lat @  17.10.2009,  22:12 Найти цитируемый пост)
[17.10.2009 22:11:00] Ambiguous field reference to 'id' - Native error: 10404

Говорит, что нет такой колонки как id ...

Цитата(lat @  17.10.2009,  22:06 )
Цитата

Пример данных:КодincludeSyntax('sql');
...INSERT INTO 'Names' (id, description, name1, name2, name_new, price, file_id) values (50676, 'Цифровые фотоаппараты', 'CANON', 'CANON IXUS 110 IS Gold', 'canonixus110isgold', 375, 27);
INSERT INTO 'Names' (id, description, name1, name2, name_new, price, file_id) values (50676, 'Цифровые фотоаппараты', 'CANON', 'CANON IXUS 110 IS Gold', 'canonixus110isgold', 370, 28);...highlightSyntax('sql_ZTQxMm','sql');Задача: "Найти все данные по товару, который имеет наименьшую стоимость?"


Вставляются одинаковый ID, опечатка? Или так и должно быть по логике БД?

Автор: lat 17.10.2009, 22:18
Цитата(Gluttton @  17.10.2009,  22:14 Найти цитируемый пост)
Говорит, что нет такой колонки как id ...


хм ... с чего бы это.

Цитата(Gluttton @  17.10.2009,  22:14 Найти цитируемый пост)
Вставляются одинаковый ID, опечатка? Или так и должно быть по логике БД?

ой, сорри)))
Это Я копипастил))) Ид разные ... ща исправлю ...

Автор: Gluttton 17.10.2009, 22:26
Цитата(lat @  17.10.2009,  22:06 Найти цитируемый пост)
Таблицы связанны между собой по ключу Names.file_id <---> Files.id

На скольк я понимаю, то таблицы связаны многие к одному... А раз так, то многим записям из таблицы Names будет соответствовать одна запись таблицы Files...
А раз так, то...

1. Находим минимальное значение цены и первичный ключ товара:
Код

select
    id,
    min(price) as price
from Tovar
    group by id


2. Нам необходимо вывести всю информацию, поэтому выполняем само-соединение...
Код

select
    Sub.id,
    Tovar.description,
    Tovar.name1,
    Tovar.name2
    Tovar.name_new,
    Sub.price,
    Tovar.file_id
from Tovar,
(
    select
        id,
        min(price) as price
    from Tovar
        group by id
)   as Sub
    where Tovar.id=Sub.id


3. Добавляем информацию из таблицы Files...
Код

select
    Sub.id,
    Tovar.description,
    Tovar.name1,
    Tovar.name2
    Tovar.name_new,
    Sub.price,
    Files.name
from Tovar, Files,
(
    select
        id,
        min(price) as price
    from Tovar
        group by id
)   as Sub
    where Tovar.id=Sub.id
    and Tovar.id=Files.id


Прошу написать начиная с какого этапа будет ошибка?
Я вот тут подумал, а подзапросы той штуковиной в которой Вы выполняете запросы поддерживаються?

Автор: lat 17.10.2009, 22:38
Цитата(Gluttton @  17.10.2009,  22:26 Найти цитируемый пост)
На скольк я понимаю, то таблицы связаны многие к одному... А раз так, то многим записям из таблицы Names будет соответствовать одна запись таблицы Files...
А раз так, то...


Да, именно так. (стрелочки уже исправил) =)

Код


[17.10.2009 22:29:49] Executing Query: 
select
    id,
    min(price) as price
from Tovar
    group by id
[17.10.2009 22:29:50] Ok.
[17.10.2009 22:29:50]  Execution time: 00:00:00.204


Тупо выдало все поля.

Код


[17.10.2009 22:33:55] Executing Query: 
select
    Sub.id,
    Tovar.description,
    Tovar.name1,
    Tovar.name2
    Tovar.name_new,
    Sub.price,
    Tovar.file_id
from Tovar,
(
    select
        id,
        min(price) as price
    from Tovar
        group by id
)   as Sub
    where Tovar.id=Sub.id
[17.10.2009 22:33:55] Token 'FROM' expected, but '.' found at line 6, column 10 - Native error: 30184



Код

[17.10.2009 22:35:30] Executing Query: 
select
    Sub.id,
    Tovar.description,
    Tovar.name1,
    Tovar.name2
    Tovar.name_new,
    Sub.price,
    Files.name
from Tovar, Files,
(
    select
        id,
        min(price) as price
    from Tovar
        group by id
)   as Sub
    where Tovar.id=Sub.id
    and Tovar.id=Files.id
[17.10.2009 22:35:30] Token 'FROM' expected, but '.' found at line 6, column 10 - Native error: 30184




Цитата(Gluttton @  17.10.2009,  22:26 Найти цитируемый пост)
Прошу написать начиная с какого этапа будет ошибка?
Я вот тут подумал, а подзапросы той штуковиной в которой Вы выполняете запросы поддерживаються? 


Отработал без ошибок только первый запрос. 

AD подзапросы вроде умеет хавать, но не знаю правильно ли он их пережёвывает.

Автор: Gluttton 17.10.2009, 22:45
До меня только сейчас начало доходить smile ...
Что за бред я пишу...
Ищу минимальное значение с группировкой по ключу smile ...
Запрос вернет мне все данные...

Дубль два...

Код

select
    Tovar.id,
    Tovar.description,
    Tovar.name1,
    Tovar.name2
    Tovar.name_new,
    Sub.price,
    Files.name
from Tovar, Files,
( 
    select
        file_id,
        min(price) as price
)   as Sub
    where Tovar.price=Sub.price
    and Tovar.file_id=Sub.file_id
    and File.id=Sub.file_id


Надеюсь теперь то что нужно smile ...

Автор: lat 17.10.2009, 22:48
Код

[17.10.2009 22:46:51] Executing Query: 
select
    Tovar.id,
    Tovar.description,
    Tovar.name1,
    Tovar.name2
    Tovar.name_new,
    Sub.price,
    Files.name
from Tovar, Files,
( 
    select
        file_id,
        min(price) as price
)   as Sub
    where Tovar.price=Sub.price
    and Tovar.file_id=Sub.file_id
    and File.id=Sub.file_id
[17.10.2009 22:46:51] Token 'FROM' expected, but '.' found at line 6, column 10 - Native error: 30184


хм ... либо AD не умеет такое хавать, либо запрос не совсем рабочий)

Автор: Gluttton 17.10.2009, 22:48
А там в комплекте поставки Help'а никакого нету? Если есть то прошу прицепить к сообщению, что бы можно было по кодам ошибок почитать...

Добавлено через 3 минуты и 7 секунд
Предлагаю провести эксперимент...
Выполнить два запроса:

1.
Код

select * from Tovar


2.
Код

select * from Tovar,
(
    select * from Tovar
)   as Sub
    where Tovar.id=Sub.id


Я думаю первый запрос пройдет, а вот интересно, что со вторым будет...

Добавлено через 7 минут и 15 секунд
Второй запрос проверил на Firebird 2.1 на предмет досадных синтаксических ошибок - работает...
Т.е. склонен думать, что если второй запрос не пройдет на AD'e (или в AD'у smile не знаю как правильно), то оно не поддерживает вложеные подзапросы...

Автор: lat 17.10.2009, 22:57
Цитата(Gluttton @  17.10.2009,  22:48 Найти цитируемый пост)
А там в комплекте поставки Help'а никакого нету? Если есть то прошу прицепить к сообщению, что бы можно было по кодам ошибок почитать...


http://www.componentace.com/bde_replacement_database_delphi_absolute_database.htm


Код

[17.10.2009 22:54:35] Executing Query: 
select * from Tovar
[17.10.2009 22:54:35] Ok.


Код

[17.10.2009 22:55:06] Executing Query: 
select * from Tovar,
(
    select * from Tovar
)   as Sub
    where Tovar.id=Sub.id
[17.10.2009 22:55:06] Ambiguous field reference to 'id' - Native error: 10404


Вот откуда ноги росли ... походу AD тупит)

Автор: Gluttton 17.10.2009, 22:58
Ч-ч-черт!

Запятая пропущена же в 6-ой строке!
Он же говорит, что я жду FROM т.к. точка была smile !

Добавлено через 9 минут и 11 секунд
Вот такой вот пример нашел на сайте разработчика:
Код

select 
v1."Venue", 
(
    select count(*) 
    from "Events" e1 
    where e1."VenueNo" = v1."VenueNo"
) as "EventsCount"
    from "venues" v1
    order by "EventsCount" desc

Т.е. коррелированные подзапросы поддерживаются!
Может быть и наш запрос переделать подобным образом...
Нужно подумать...

Добавлено через 13 минут и 16 секунд
Код

select
    Files.name,
    (
        select
            min(Tovar.price) as price
        from Tovar
            where Tovar.file_id=Files.id
    )   as "MinPrice"
from Files


Если такой запрос прокатит, то счастье будет smile ...

Автор: Gluttton 17.10.2009, 23:15
lat, не спать smile !

Вариант с коррелированным подзапросом работает?

Автор: lat 17.10.2009, 23:17
Цитата(Gluttton @  17.10.2009,  22:58 Найти цитируемый пост)
select
    Files.name,
    (
        select
            min(Tovar.price) as price
        from Tovar
            where Tovar.file_id=Files.id
    )   as "MinPrice"
from Files


Ухты!
Показало все поля из таблицы Files )))

Автор: Gluttton 17.10.2009, 23:19
Цитата(lat @  17.10.2009,  23:17 Найти цитируемый пост)
Показало все поля из таблицы Files )))

И всё  smile ? А как же вторая колонка?

Автор: lat 17.10.2009, 23:21
MinPrice есть, но оно везде равно нулю  smile 

Автор: Gluttton 17.10.2009, 23:26
Перед тем как выйти на финишную прямую хочу уточнить правильно ли я понимаю предметную область...

Есть файлы... Каждый файл имеет имя...
Каждому файлу соотвествует (содержиться или как угдно) несколько записей из таблицы товаров...

Нужно отобразить:
Имена всех файлов, а так же информацию о товаре с минимальной ценой соответствующий данному файлу...

Или же:
Имена всех файлов, а так же информацию о всех товарах соответствующих данному файлу и в придачу для них найти ещё минимальную цену...

Или что то другое?

Добавлено через 3 минуты и 47 секунд
Цитата(lat @  17.10.2009,  23:21 Найти цитируемый пост)
MinPrice есть, но оно везде равно нулю   

Предлагаю сделать следующее (что бы я понимал о чем речь) заполнить таблицы буквально двумя-тремя картежами (строками) тестовых данных и выложить сюда. Тогда легче будет понят то или не то возвращает запрос...

Автор: lat 17.10.2009, 23:34
Цитата(Gluttton @  17.10.2009,  23:26 Найти цитируемый пост)
Есть файлы... Каждый файл имеет имя...
Каждому файлу соотвествует (содержиться или как угдно) несколько записей из таблицы товаров...

Ага, верно.


Цитата(Gluttton @  17.10.2009,  23:26 Найти цитируемый пост)
Или что то другое? 


Нужно найти в таблице Tovar (а там находятся все товары из всех файлов) одинаковые товары, выбрать из них товар с минимальной стоимостью и вывести инфу о нем, включая инфу о том в каком файле (прайсе) был взят товар.

ЗЫ прога анализирует прайсы, находит одинаковые товары и выбирает тот который имеет наименьшую стоимость. 

Автор: Gluttton 17.10.2009, 23:43
Код

select
    Tovar.name_new,
    min(Tovar.price) as MinPrice,
    Files.name
from Tovar, Files
    where Tovar.file_id=Files.id
    group by Tovar.name_new, Files.name

Вот так (только ещё нужно вывести остальную информацию о товаре)? 

Автор: lat 17.10.2009, 23:45
запрос дал не правильный результат ((( 

Ну смотри. 
Например в таблице Tovar есть 3000 записи. В таблице Files - всего 2-е (так как все товары были взяты из 2-х прайсов).
Предположим в этих двух прайсах были идентичные записи о товарах, с той лишь разницей что цены у них разные. 
Так вот нужно что бы после запроса, на выходе было 1500 записей с ценой (минимальной) и инфой о файле источнике. 

Так вот тот запрос дал 2800 записей.

Добавлено @ 23:53

Таблички:
http://whitefang.org.ua/incoming/Tovar.SQL
http://whitefang.org.ua/incoming/File.SQL

Может так проще будет задачу решить. Как бы в живую увидеть о чем речь)

Автор: Gluttton 17.10.2009, 23:58
Что бы из 3000 записей было отобрано 1500 нужно что бы каждая запись повторялась ровно два раза...
А раз выводит 2800, то это значит, что среди 3000 записей только 200 повторяются в обоих прайсах, а остальные уникальны (т.е. содержаться только в одном из двух прайсов).
А отсюда резонный вопрос, по какому признаку определять "одинаковость" товара? Я делал по name_new, правильно?

Ещё раз прошу! Предлагаю заполнить БД двумя-тремя строчками данных (показать мне эти строчки) и потом постить что вернул запрос и что от него ожидалось... Так будет легче  smile ...

Ну и  smile 
А вообщето (я уже потихоньку начинаю въезжать в суть БД) тут на лицо ошибка проектирования (по моему)!

В одном файле могут быть разные товары  и в то же время один и тот же товар может находиться в разных файлах? Тогда выходит отношение многие ко многим, а многие ко многим на этапе физического проектирования реализуется внедрением дополнительной "третьей" таблицы...

Т.е. как то так

Товары:
код,
наименование,
характеристики

Товар_в_Прайсе:
код_товара,
код_прайса,
цена

Прайс:
код,
наименование,
фирма

Ну это так, на правах оффтопа smile ...

Автор: lat 18.10.2009, 00:02
Цитата(Gluttton @  17.10.2009,  23:58 Найти цитируемый пост)
Предлагаю заполнить БД двумя-тремя строчками данных (показать мне эти строчки)


приатачил все файлы в пред посту.

Цитата(Gluttton @  17.10.2009,  23:58 Найти цитируемый пост)
Что бы из 3000 записей было отобрано 1500 нужно что бы каждая запись повторялась ровно два раза...
А раз выводит 2800, то это значит, что среди 3000 записей только 200 повторяются в обоих прайсах


в том то и прикол, я делал так что бы повторялось ровно два раза.

Цитата(Gluttton @  17.10.2009,  23:58 Найти цитируемый пост)
А вообщето (я уже потихоньку начинаю въезжать в суть БД) тут на лицо ошибка проектирования (по моему)!

ща обмозгую ...

Добавлено @ 00:07
Код

select
    Tovar.name_new,
    min(Tovar.price) as MinPrice,
    Files.name
from Tovar, Files
    where Tovar.file_id=Files.id
    group by Tovar.name_new, Files.name


Вернуло 3246 из 3796.
Результат приатачил к посту.

Автор: Gluttton 18.10.2009, 00:14
Цитата(lat @  18.10.2009,  00:02 Найти цитируемый пост)
Вернуло 3246 из 3796.Результат приатачил к посту.

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

Добавлено через 10 минут и 48 секунд
Так... Ошибку нашел... Думаю...

Добавлено через 12 минут и 54 секунды
Судя по результатам запроса в БД 3796-3246=? товара по одинаковой цене в обоих прайсах... 

Автор: lat 18.10.2009, 00:28
Файлы урезал до разумного размера =) 
Грузить с того же источника.

Автор: Gluttton 18.10.2009, 00:33
Цитата(lat @  18.10.2009,  00:28 Найти цитируемый пост)
Грузить с того же источника.

ОК! Сейчас перенесу данные к себе в БД и думаю что дела пойдут быстрее, но если честно, то такого ступора у меня уже давно не было smile ...
Что то никак не идет шайба в канадские ворота smile ...

Автор: lat 18.10.2009, 00:45
Та да. Задачка повергла уже как минимум 3 - х людей в некий продолжительно-мучительный ступор =)
Видно это все влияние госпожи субботы ;)

Автор: Gluttton 18.10.2009, 01:02
Так... У меня работает на Firebird 2.1...
Код

select
    "NAMES".name_new,
    "NAMES".price as MinPrice,
    Files.name
from "NAMES", Files
    where "NAMES".file_id=Files.id
    and "NAMES".price=
    (
        select min(SN.price)
        from "NAMES" AS SN
           where "NAMES".name_new=SN.name_new
    )

Вот что возвращает:
user posted image

Добавлено через 1 минуту и 17 секунд
Цитата(lat @  18.10.2009,  00:45 Найти цитируемый пост)
Та да. Задачка повергла уже как минимум 3 - х людей в некий продолжительно-мучительный ступор =)Видно это все влияние госпожи субботы ;)

Не хочу отвлекаться, т.к. уже устал, но я бы с удовольствием покритиковал (конструктивно) структуру, уже ставшей мне родной smile , БД!

Добавлено через 4 минуты и 45 секунд
Или же вот такой результат:
user posted image
Для вот такого запроса:
Код

select
    "NAMES".name_new,
    "NAMES".id,
    "NAMES".description,
    "NAMES".name1,
    "NAMES".name2,
    "NAMES".price as MinPrice,
    FILES.name
from "NAMES", FILES
    where "NAMES".file_id=FILES.id
    and "NAMES".price=
    (
        select min(SN.price)
        from "NAMES" AS SN
           where "NAMES".name_new=SN.name_new
    )

Автор: lat 18.10.2009, 01:09
эх ... у меня на AD возвращает почему - то 1 результат (((
видно придется думать о смене СУБД, другого выхода увы не вижу.

Автор: Gluttton 18.10.2009, 01:10
Так читабельнее:
Код

select
    NM.name_new,
    NM.id,
    NM.description,
    NM.name1,
    NM.name2,
    NM.price as MinPrice,
    F.name
from "NAMES" as NM, Files as F
    where NM.file_id=F.id
    and NM.price=
    (
        select min(SN.price)
        from "NAMES" AS SN
           where NM.name_new=SN.name_new
    )


Добавлено через 2 минуты и 19 секунд
Цитата(lat @  18.10.2009,  01:09 Найти цитируемый пост)
эх ... у меня на AD возвращает почему - то 1 результат (((видно придется думать о смене СУБД, другого выхода увы не вижу.


А исходные данные такие же как и мне были переданы?

Я понимаю, что если бы ошибка была... Но одна строка в результате...

Автор: lat 18.10.2009, 01:14
Цитата(Gluttton @  18.10.2009,  01:10 Найти цитируемый пост)
select
    NM.name_new,
    NM.id,
    NM.description,
    NM.name1,
    NM.name2,
    NM.price as MinPrice,
    F.name
from "NAMES" as NM, Files as F
    where NM.file_id=F.id
    and NM.price=
    (
        select min(SN.price)
        from "NAMES" AS SN
           where NM.name_new=SN.name_new
    )


АААААААААААААААААААА!!!! УУУУУУУУУУУУУУУУУ!!! Счастья наступило!!!
Чэл, огромное те спасибо!
Работает! =)

Автор: Gluttton 18.10.2009, 01:14
Нужно разобраться до конца smile !

Ура!

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

Автор: lat 18.10.2009, 01:15
Мог бы, поставил бы те плюс =)

Добавлено через 36 секунд
Цитата(Gluttton @  18.10.2009,  01:14 Найти цитируемый пост)
Нужно разобраться до конца smile ! 


так а что ещё осталось ... вроде ж работает? )

Автор: Gluttton 18.10.2009, 01:19
 smile 
Цитата(lat @  18.10.2009,  01:14 Найти цитируемый пост)
Чэл, огромное те спасибо!

Пожайлуста smile ...
Цитата(lat @  18.10.2009,  01:15 Найти цитируемый пост)
Мог бы, поставил бы те плюс =)

Это не главное  smile , главное, что задача решена smile ...

Предлагаю закрывать тему, а то модераторы завтра прийдут и надают нам обоим по шапке за то что мы тут флуд на полсотни сообщений развели smile ...

Автор: lat 18.10.2009, 01:23
Цитата(Gluttton @  18.10.2009,  01:19 Найти цитируемый пост)
Предлагаю закрывать тему, а то модераторы завтра прийдут и надают нам обоим по шапке за то что мы тут флуд на полсотни сообщений развели smile ...


Ну так сам же писал "главное что задача решена", а кол-во сообщений это такое ... побочный продукт на пути к дзену) 
Для того форум и существует что б помогать решать задачи =)

Автор: lat 18.10.2009, 02:40
Поспешил с выводами. Задачка решена на 50%, продолжаем дальше мозго-штурм господа.

Когда цены одинаковые для одинаковых товаров, то выводит дубликаты. 

ЗЫ повторил эту ситуацию все в том же файле http://whitefang.org.ua/incoming/Tovar.SQL. Есть 10 записей,  должно вывести 5 (кол-во оригинальных товаров, без дубликатов и с мин. ценой) после запроса. А выводит больше 5-ти, то есть кроме всего остального также показывает товар с одинаковой ценой но с разным источником (прайсом). Так что вот так =)

Автор: Gluttton 18.10.2009, 10:37
Цитата(lat @  18.10.2009,  02:40 Найти цитируемый пост)
Когда цены одинаковые для одинаковых товаров, то выводит дубликаты. 

А по какому критерию определять запись поподающую в выборку при одинаковой цене?

Я так пологаю, что правильно было бы в тех случаях, когда цена минимальна в обоих прайсах выводить в графе с прайсами оба прайса (производить конкатенацию строк). Это можно реализовать рекурсивным запросом. Не все СУБД поддерживают рекурсивные запросы, но вот Firebird например поддерживает.
http://forum.vingrad.ru/forum/topic-273104/kw-слияние-строк-sql-запрос.html и http://forum.vingrad.ru/forum/topic-273313.html несколько примеров.
Firebird бесплатен и имеет embedded версию (т.е. для запуска сервера не обязательно производить установку его установку на ПК конечного пользователя, а достаточно "носить" в одной папке с программой несколько dll-ек). Я это к тому, что если реализация проекта не зашла далеко, то возможно стоит подумать о смене СУБД... Кроме Firebird существуют ещё и MySQL PosgreSQL а так же экспресс версии Oracle, MS SQL Server и многое другое например такое как SQLite...

Ещё раз хочу обратить внимание на то что испльзуемая структура БД неверна! Детально почитать об этом можно спросив у Googl'а про такое: "анамалия вставки", "аномалия удаления", "аномалия обновления". На пальцах приведу примеры недостатков...
1. БД не может хранить информацию о товаре, который отсутствует во всех прайсах.
2. Если характеристики товара изменились, то для обновления информации о товаре необходимо внести изменения во все строках, где он упоминается, а не в одном единственном месте.

А должно быть примерно так:
(ещё раз напомню, что это классическая реализация связи "многие ко многим")
Таблица "Товары" в которой описываются все товары, каждый товар упоминается один раз. Тут можно указать марку, производителя, основные характеристикие.
Таблица "Продавец" в которой описываются все продавцы (будь то фирмы, будь то частные лица или же интернет ресурсы). Тут можно хранить информацию о продавцах.
И "третья" связующая таблица "Товары_у_Продавца" (или же просто "Прайс" или ещё как нибудь назвать) в которой храняться как минимум (это обязательно) внешние ключи как на Товары так и на Продавцев. Так же можно хранить здесь информацию, которая характерна для данного образца Товара у конкретного Продавца это может быть и цена и гарантийный срок и ещё что нибудь...

При такой сруктуре можно будет запросто избежать перечисленных мною выше недостатков. Т.е. можно будет хранить информацию о Товаре, если его не предлагает ни один из Продавцев, а так же в случае необходимости внесения изменений в данные о Товаре (впрочем как и о Продавце) нужно будет изменить всего лишь одну строку в таблице Товар (или Продавец)...

Автор: lat 18.10.2009, 20:57
Цитата(Gluttton @  18.10.2009,  10:37 Найти цитируемый пост)
А по какому критерию определять запись поподающую в выборку при одинаковой цене?


Ниче не понял)))

Цитата(Gluttton @  18.10.2009,  10:37 Найти цитируемый пост)
Ещё раз хочу обратить внимание на то что испльзуемая структура БД неверна! Детально почитать об этом можно спросив у Googl'а про такое: "анамалия вставки", "аномалия удаления", "аномалия обновления". На пальцах приведу примеры недостатков...
1. БД не может хранить информацию о товаре, который отсутствует во всех прайсах.
2. Если характеристики товара изменились, то для обновления информации о товаре необходимо внести изменения во все строках, где он упоминается, а не в одном единственном месте.


Согласен с тем что спроектировано "на скорую руку" и несколько непонятно на первый взгляд. Но есть пару но. 

1. БД и не должна хранить инфу о товаре, который отсутствует  во всех прайсах. Потому что такого быть не может. Программа собирает инфу с прайсов, и не откуда более. И источник у товара всегда будет.  Это первое.
2. Каждый раз при анализе новых/старых прайсов вся инфа сбрасывается. БД очищается. То есть ничего обновлять не нужно. При каждом анализе происходит новое заполнение. И сама БД, в этом случае, это лишь кэш-буфер который неплохо справляется с поиском похожих товаров и "конкатенацией" этих товаров по минимальной цене)))

Для данной программы эта структура БД вполне подходит. А сама СУБД используется лишь с целю нахождения и выделения "оптимальной покупки", как временное хранилище.

ЗЫ знаю что звучит как-то не так. Но поверьте, по соотношение потраченное_время/качество эта схема вполне приемлема. 

Автор: Gluttton 18.10.2009, 21:23
Цитата(lat @  18.10.2009,  20:57 Найти цитируемый пост)
ЗЫ знаю что звучит как-то не так. Но поверьте, по соотношение потраченное_время/качество эта схема вполне приемлема. 

Да я и не против smile ...

Цитата(lat @  18.10.2009,  20:57 Найти цитируемый пост)
Ниче не понял)))

Когда дядя Петя идет на рынок ему тетя Сара говорит: "... купи самой дешёвой картошки". Дядя Петя приходит и видит, что по самой дешевой цене картошка продается в трех точках, тогда он звонит по мобильному тете Саре и спрашивает, какую именно выбрать, а тётя Сара ему говорит: "... из всех картошек по самой дешевой цене купи самую крупную, а если и крупная будет в нескольких точках, то купи ту до которой ближе идти smile ".

Вот и я спрашиваю, как быть в случае если в обоих прайсах есть товар по одинаковой цене, какой из них (товаров) должен попасть в результирующую выборку.
Я например не считаю, тот факт, что попадут оба ошибкой...

Автор: lat 18.10.2009, 21:43
Прикольно ты про Петю )))
Мне понравилось, вот если бы так все рассказывали, лаконично и понятно!)))


Цитата(Gluttton @  18.10.2009,  21:23 Найти цитируемый пост)
Я например не считаю, тот факт, что попадут оба ошибкой...

Я тоже не считаю это ошибкой.

Результаты будут сохраняться в файле, который будет потом передаваться на сайт роботу (не железному, а php ному =). Так вот робот просто не поймет почему ему дали два и более товара с одинаковыми характеристиками (робот не будет знать о источнике товара, только название - цена).

Так что не имеет значения какой из одинаковых товаров попадет в выборку, у них нет приоритета (по крайней мере в данной версии ПО такого не планирую ;)
Ну или " ... то купи ту до которой ближе идти" )))))

Автор: Gluttton 18.10.2009, 22:01
Цитата(lat @  18.10.2009,  21:43 Найти цитируемый пост)
Ну или " ... то купи ту до которой ближе идти" )))))

Самый простой способ реализовать выбор только одного товара - использовать ключевое слово DISTINCT. Использование SELECT DISTINCT позволяет отображать только разные записи. Т.е. для таблицы:
Код

t1:
А     B
1    1
1    1
1    2
2    1

Запрос:
Код

select A from t1

Вернёт:
Код

1
1
1
2

А запрос:
Код

select distinct A from t1

Вернёт:
Код

1
2

Но в то же время запрос:
Код

select distinct A, B from t1

Вернёт:
Код

1    1
1    2
2    1

Я это всё к чему... У нас получается, что у двух товаров все данные одинаковые, кроме имени файла и первичного ключа...
Но т.к.
Цитата(lat @  18.10.2009,  21:43 Найти цитируемый пост)
(робот не будет знать о источнике товара, только название - цена)

То решение лежит на поверхности:
1. Отказываемся от выбора поля с именем файла и с кодом товара.
2. Добавляем в запрос ключевое слово DISTINCT.
Код

select distinct
    NM.name_new,
    NM.description,
    NM.name1,
    NM.name2,
    min(NM.price) as MinPrice
from "NAMES" as NM, Files as F
    group by 
        NM.name_new, 
        NM.description, 
        NM.name1, 
        NM.name2

Опробировал на своей БД - работает...
Вот такой результат:
user posted image
Ну а если мы не может отказаться от полей первичного ключа и имени файла, то начнуться "танцы с бубном" smile ...

Автор: lat 18.10.2009, 22:33
Цитата(Gluttton @  18.10.2009,  22:01 Найти цитируемый пост)
Ну а если мы не может отказаться от полей первичного ключа и имени файла, то начнуться "танцы с бубном" smile ...


угу. пущай начнутся танцы =)

ибо для робота эти поля не нужны, а вот мне они ой как важны)

Добавлено через 10 минут и 9 секунд
1 файл я создаю для робота
2 файл для себя (в нем полная инфа о товарах, в том числе и источник)
так что distinct тут не поможет (

Автор: Gluttton 18.10.2009, 23:29
Услилит  тестовые данные теперь таблицы выглядят так:
user posted image
и
user posted image
Вот такой запрос:
Код

select distinct
    NM.name_new,
    NM.id,
    NM.description,
    NM.name1,
    NM.name2,
    NM.price,
    F.name
from "NAMES" as NM, Files as F
    where NM.file_id=F.id
    and NM.price=
    (
        select min(SN.price)
        from "NAMES" AS SN
           where NM.name_new=SN.name_new
    )
    and not exists
    (
        select
            NMA.name_new,
            NMA.id,
            NMA.description,
            NMA.name1,
            NMA.name2,
            NMA.price
        from "NAMES" as NMA
            where NM.name_new=NMA.name_new
            and NM.id<>NMA.id
            and NM.description=NMA.description
            and NM.name1=NMA.name1
            and NM.name2=NMA.name2
            and NM.price=NMA.price
    )
union all
select distinct
    NM.name_new,
    NM.id,
    NM.description,
    NM.name1,
    NM.name2,
    NM.price,
    F.name
from "NAMES" as NM, Files as F
    where NM.file_id=F.id
    and NM.price=
    (
        select min(SN.price)
        from "NAMES" AS SN
           where NM.name_new=SN.name_new
    )
    and exists
    (
        select
            NMA.name_new,
            NMA.id,
            NMA.description,
            NMA.name1,
            NMA.name2,
            NMA.price
        from "NAMES" as NMA
            where NM.name_new=NMA.name_new
            and NM.id<>NMA.id
            and NM.description=NMA.description
            and NM.name1=NMA.name1
            and NM.name2=NMA.name2
            and NM.price=NMA.price
    )
    and F.id=
    (
        select max(FA.id) as minid
        from Files as FA
            where exists
            (
                select *
                from "NAMES" as NMB
                    where NMB.file_id=FA.id
                    and NMB.name_new=NM.name_new
                    and NMB.price=
                    (
                        select min(NMC.price) as mncminprice
                        from "NAMES" as NMC
                            where NMC.name_new=NMB.name_new
                            and NMC.name_new=NM.name_new
                    )
            )
    )

Вернет вот такие вот данные:
user posted image
Запрос написан на SQL 92, без использования подзапросов поэтому я надеюсь AD его "прожует"...
Запрос расчитан на количество прайсов больше двух, если количество прайсов никогда не будет больше двух, то запрос будет выглядеть проще...

Автор: lat 18.10.2009, 23:41
Да ты зверь Чэл =)

К сожалению выдало
Код

[18.10.2009 23:32:44] Search condition is not applicable - Native error: 30144

Автор: Gluttton 18.10.2009, 23:53
Цитата(lat @  18.10.2009,  23:41 Найти цитируемый пост)
Да ты зверь Чэл =)

Настоящий бы зверь реализовал это десятком строк (а не сотней) smile ...

http://forum.vingrad.ru/act-Print/client/printer/f-88/t-249941.html упоминается та же ошибка...

Предлагаю разбить запрос на две части: до и после union all (без union all)...
Выполнить сначала, то, что выше, а потом, то, что ниже...

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

Автор: lat 19.10.2009, 12:55
В АД запрос не скушало. 
А вот попробовал в MySQL, там на его выполнение ушло более минуты о_0 (это для 3000 запсией). А что будет тогда если записей буде намного больше.

Автор: Gluttton 19.10.2009, 13:25
Цитата(lat @  19.10.2009,  12:55 Найти цитируемый пост)
А что будет тогда если записей буде намного больше.

Что будет, что будет... Сервер упадет и ничего больше не будет smile ...

Цитата(lat @  19.10.2009,  12:55 Найти цитируемый пост)
А вот попробовал в MySQL, там на его выполнение ушло более минуты о_0 (это для 3000 запсией).

Для более продвинутых СУБД относительно AD запрос, можно существенно упростить...

Так что если надумаешь переходить с АD на, что то другое, то пиши, пересмотрю запрос...

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)