Форум программистов «Весельчак У»
  *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

  • Рекомендуем проверить настройки временной зоны в вашем профиле (страница "Внешний вид форума", пункт "Часовой пояс:").
  • У нас больше нет рассылок. Если вам приходят письма от наших бывших рассылок mail.ru и subscribe.ru, то знайте, что это не мы рассылаем.
   Начало  
Наши сайты
Помощь Поиск Календарь Почта Войти Регистрация  
 
Страниц: [1]   Вниз
  Печать  
Автор Тема: Составить сложный запрос  (Прочитано 23564 раз)
0 Пользователей и 1 Гость смотрят эту тему.
Joiner
Интересующийся

ru
Offline Offline

« : 19-09-2016 10:08 » 

Здравствуйте, уважаемые форумчане!
Дублирую вопрос. Пробовал в ветке баз данных, результат нулевой, а время поджало. Вчера еще надо было.
Суть такова.
Есть база с таблицами работ- Work, материалов-Mat и привязки материалов к определенным работам -WorkMat. Связь между Mat и Work через WorkMat многие-ко-многим. Т.е. для некоторых работ есть материалы и любой материал может использоваться для разных работ. Пример в Access вложен с примером моего пробного запроса.

На форме две таблицы - вверху работы, внизу материалы. При переходе по строкам работ внизу должен появиться полный список ВСЕХ материалов без повторов и пропусков, а в присоединенных столбцах данные из WorkMat только для ОДНОЙ выбранной вверху работы. Пробовал и LEFT JOIN и UNION инструкции, то повторяющиеся записи появляются, то, наоборот часть записей пропадает. Пробовал сложные запросы конструировать, только больше запутался.
В сокращенном варианте база такая:

Поля Работы (Work) - ID_Work(ключ), Name, Union, Price
Поля Материалов (Mat) - ID_Mat(ключ), Name, Union, Price
Связывающая таблица (WorkMat)  - ID_Work(ключ), ID_Mat(ключ), Rashod (Расход материала для конкретной работы)

Связь один-ко-многим Work.ID_Work -> WorkMat.ID_Work
Связь один-ко-многим Mat.ID_Mat -> WorkMat.ID_Mat

Реально в таблице Work и Mat полей больше, но, думаю, это не принципиально. Мне наводка на правильный запрос нужна. С полями, думаю, разберусь. Я пробовал комбинировать и объединение, и присоединение полей, ставил EXISTS, пробовал применять DISTINCT, но у меня либо повторение материала по названию происходит, либо не полный список материалов.

Запрос пока в таком виде:
Код: (SQL)
SELECT Mat.ID_Mat, Mat.[Name], Mat.[Unit], WorkMat.*
FROM Mat LEFT JOIN WorkMat ON Mat.ID_Mat = WorkMat.ID_Mat
WHERE WorkMat.ID_Work=2 OR WorkMat.ID_Work IS NULL
ORDER BY Mat.[Name];

При таком запросе выводятся не все строки материалов. Те, которые ID_Work<>2 не видны

Пробовал добавить:

Код: (SQL)
UNION ALL
SELECT Mat.ID_Mat, Mat.[Name], Mat.[Unit]
FROM Mat LEFT JOIN WorkMat ON Mat.ID_Mat = WorkMat.ID_Mat
WHERE WorkMat.ID_Work<>2

Приходится добавлять пустые поля в таблицу Mat, чтобы выровнять, а толку нет - появляются повторяющиеся строки материалов.

Заранее благодарен.

Добавлено через 11 минут и 8 секунд:
Пример забыл подцепить.

* AVSm.rar (52.94 Кб - загружено 698 раз.)
« Последнее редактирование: 19-09-2016 10:19 от Joiner » Записан
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #1 : 19-09-2016 15:00 » 

От перемены мест результат не изменится.

https://forum.shelek.ru/index.php/topic,30633.0.html
Что в этой теме не так?
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Joiner
Интересующийся

ru
Offline Offline

« Ответ #2 : 19-09-2016 15:07 » 

Ну, тут, вроде, НЕОТЛОЖКА. А состояние уже критическое, скоро взрыв будет. Неделю тыркаюсь. Учителей нет, самоучкой. И со свободным временем очень плохо. Не успеваю изучить вопрос.
Записан
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #3 : 19-09-2016 17:30 » 

Быстрее не будет. Предлагаю слить это в одну тему.
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Joiner
Интересующийся

ru
Offline Offline

« Ответ #4 : 19-09-2016 17:37 » 

Спасибо. Топики одинаковые, может лучше удалить один. Какой не знаю. Оставьте пожалуйста, где, по Вашему мнению, лучше будет.
Записан
Sla
Модератор

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #5 : 19-09-2016 20:53 » 

Рисуйте картинку, где что должно быть
Записан

Мы все учились понемногу... Чему-нибудь и как-нибудь.
Joiner
Интересующийся

ru
Offline Offline

« Ответ #6 : 19-09-2016 21:40 » 

Вроде все подробно описал.

Не пойму как картинки вставить в текст. Прикрепил.

На первой, как это примерно выглядит.
На второй - запрос с ошибками, надо, чтобы список материалов был весь и без повторений по наименованию, а в присоединенных столбцах расход только по одной работе.



* pic1.png (32.68 Кб - загружено 1243 раз.)
* pic2.png (12.23 Кб - загружено 1039 раз.)
« Последнее редактирование: 20-09-2016 06:07 от Алексей++ » Записан
Aether
Специалист

ru
Offline Offline
Пол: Мужской

« Ответ #7 : 20-09-2016 09:05 » 

Я думаю, Sla имел ввиду не эти картинки, а описание цели, структуры и связей.

Если цель сформировать каталог услуг и материалов, то можно вообще обойтись одной таблицей для всего, сделать поле булевого типа для пояснения о чём речь о цене материала или услуги и всё.

Если цель формировать заказы, то таблиц лучше сделать три: на описание заказов, на цену работ и на материалы. Например:
Заказ ID 1 :
Грунтовка пола 10кв м по 15р за кв м = 150р
(Расходные материалы : грунт, кисть, ведро...)
Укладка плитки 5кв м по 24р за кв м = 120...

Если цель - управление складом, то тут сложнее, и больше вариантов для творчества.

Если цель учебная: привязать абстрактно материалы к услугам - это другое дело, например:
маляр использует кисть и разнорабочий использует кисть.

Добавлено через 15 минут и 8 секунд:
Ещё, нюансы:
Почему там где измерение идёт в шт. использовано дробное число? У каждого материала должна быть стандартная упаковка для списания. Например, мешок цемента бывает 25, 40, 50кг. Если в работе нужно 15кг, то в расход нужно поместить 1шт. 25кг. Если нужно для работ 30кг, то списать надо 40кг 1шт. Дальше необходимо комбинировать, чтобы добиваться лучшей материалоёмкости.
В описании габаритов лучше использовать не знак "*", а "х" - привычнее прорабам и лучше читается на бумаге, например, 2х1,5х3,5, а не 2*1,5*3,5.
« Последнее редактирование: 20-09-2016 09:20 от Aether » Записан
Joiner
Интересующийся

ru
Offline Offline

« Ответ #8 : 20-09-2016 09:24 » 

Ребята, я, конечно, правила почитал и понял, что Вы добрые и ленивые. Но у меня в стартовом топике все подробно описано, и структура таблиц, и связи. А цель блока программы - это настройки.
Пользователь перемещается по списку работ вверху, а внизу появляется полный список материалов. Он может перейти вниз и выбрать материал который он хочет привязать к работе (это происходит в столбце Расход (_Rashod)) . При этом, естественно, он должен видеть привязки только к той работе, которую он выбрал, а список материалов, должен быть весь, чтобы он мог выбрать нужный материал из списка. Надеюсь понятно донес.    

Добавлено через 6 минут и 42 секунды:
По поводу единиц измерения Вы не переживайте. Во-первых, это я ткнул куда глаза глядят. Мне не важно что там выбралось. Мне важно, что бы правильно вывелось на форму. Во-вторых, там есть и канистры и рулоны и штуки, которые считаются долями на площадь. Это проблема сметчика, он может написать полбанки на кв.м. Они в чем покупают в том и меряют.
« Последнее редактирование: 20-09-2016 09:30 от Joiner » Записан
Aether
Специалист

ru
Offline Offline
Пол: Мужской

« Ответ #9 : 20-09-2016 09:59 » 

Цель: фильтр для таблицы материалов по видам работ.
Как связаны между собой обе таблицы? В чём суть WorkMat?
Можешь сделать кадр запроса по всем таблицам, оформить соответственно:
Инструкция: DESCRIBE smf_settings;
Результат:
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| variable | varchar(255) | NO   | PRI |         |       |
| value    | text         | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
Инструкция: SELECT variable, value FROM smf_settings;
+------------------------------+-----------------------------------------+
| variable                     | value                                   |
+------------------------------+-----------------------------------------+
| smfVersion                   | 2.0.11                                  |
| news                         | SMF - Just Installed!                   |
| compactTopicPagesContiguous  | 5                                       |
| compactTopicPagesEnable      | 1                                       |
| enableStickyTopics           | 1                                       |
| todayMod                     | 1                                       |
| karmaMode                    | 0                                       |
| karmaTimeRestrictAdmins      | 1                                       |
| enablePreviousNext           | 1                                       |
| pollMode                     | 1                                       |
| enableVBStyleLogin           | 1                                       |
Тут важны не все поля, но значащие. Ну и строки не все, но достаточные, чтобы потом понять то, как лучше связать всё это.
Записан
Joiner
Интересующийся

ru
Offline Offline

« Ответ #10 : 20-09-2016 10:14 » 

Связь между Work и Mat многие-ко-многим. Для работы может быть несколько материалов. И материал может быть использован для нескольких работ.  Суть таблицы WorkMat - обеспечить эту связь. Мне нужен запрос, который выводит всю таблицу материалов из Mat и расход только для одной работы из таблицы WorkMat. И этот расход должен быть в строке соответствующего материала. Таблица Work в запросе не учавствует, она нужна, чтобы получить ID работы для запроса. Я ее для понимания сути показал.

Добавлено через 18 минут и 41 секунду:
Вот еще для понимания.

Делаем такой запрос. Выдергиваем из WorkMat записи только для одной работы и назовем результат QQQ.

Код: (SQL)
SELECT * FROM WorkMat WHERE WorkMat.ID_Work=2;

Делаем следующий запрос

Код: (SQL)
SELECT Mat.*, QQQ.*
FROM Mat LEFT JOIN QQQ ON Mat.ID_Mat = QQQ.ID_Mat;

Как объединить два этих запроса в один.
« Последнее редактирование: 20-09-2016 10:33 от Joiner » Записан
Aether
Специалист

ru
Offline Offline
Пол: Мужской

« Ответ #11 : 20-09-2016 10:47 » 

Что из себя будет представлять QQQ физически?
Записан
Joiner
Интересующийся

ru
Offline Offline

« Ответ #12 : 20-09-2016 11:09 » 

В Access это запрос и ему можно присвоить имя, потом использовать в другом запросе. А как физически, даже не задумывался, думаю так и хранится в виде строки запроса, просто результат для пользователя показывается в виде таблицы. В этом то и беда. В Access его нужно перед использованием обновлять, либо в ручку запускать, либо в макросе, если программно использовать. А вот как из внешней программы достать не знаю. А мне как раз из внешней надо. Есть, конечно, обходные пути. Сделать запрос, результат в буфер, потом программно расставить все по местам и вывести на экран,  но не хочется огород городить.
Записан
Aether
Специалист

ru
Offline Offline
Пол: Мужской

« Ответ #13 : 20-09-2016 11:23 » 

Суть в том, что таблицу связей можно реализовать по разному, например, сделать два столбца, один с ID работы, а другой типа строки переменной длины и туда записывать перечень ID материалов. Можно, вместо строки использовать BLOB - двоичные массивы, можно пробовать двумерный массив... Эти детали реализации важны для правильного понимания и соответственно для правильного составления запроса.
Не знаю про Access, а так в SQL можно использовать временные переменные и временные таблицы для хранения промежуточного результата. В некоторых версиях доступен вложенный запрос. Существует работа со строками.
Может быть вообще пересмотреть систему - добавить столбцы флагов материала в таблицу работ или строку с перечислением ID материалов. Соответственно, программа запрашивает у БД перечень материалов для конкретного вида работ, потом формирует запрос на материалы. "Короткий путь - чаще тот, который знаешь."

Добавлено через 1 минуту и 32 секунды:
Вот, для размышления:
CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);


INSERT INTO person VALUES (NULL, 'Antonio Paz');

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());


INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());



SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

« Последнее редактирование: 20-09-2016 11:24 от Aether » Записан
Joiner
Интересующийся

ru
Offline Offline

« Ответ #14 : 20-09-2016 11:48 » 

Как использовать WHERE со сложными фильтрами я прекрасно представляю.
Хорошо попробую сформулировать абстрактно по другому. Есть две таблицы со связью один-к-многим. Мне нужно объединить их в одну. Используем LEFT JOIN. Левая (таблица1) целиком без изменений, а в правой (таблица2), естественно, записи будут подцеплятся по условию, что сходятся ключи в выражении ON. Проблема в том,что мне надо чтобы кроме совпадения ключей для второй таблицы выполнялось еще одно условие, например Таблица2.поле1=2. LEFT JOIN не позволяет ставить дополнительное условие.   

Добавлено через 24 минуты и 5 секунд:
В моем случае и в простейшем варианте

Код: (SQL)
SELECT Mat.*, WorkMat.*
FROM Mat LEFT JOIN WorkMat ON Mat.ID_Mat = WorkMat.ID_Mat

Что и где мне здесь пределать, чтобы в выражении LEFT JOIN дополнительно отфильтровало из WorkMat строки с ID_Work=2.
« Последнее редактирование: 20-09-2016 12:13 от Joiner » Записан
Aether
Специалист

ru
Offline Offline
Пол: Мужской

« Ответ #15 : 20-09-2016 12:26 » 

Для обработки по условию, как раз и нужно WHERE.

Если предположить, что WorkMat - список вида:
ID, ID_Mat, ID_Work.

Что мешает:
SELECT * FROM Mat, WorkMat WHERE Mat.ID_Mat = WorkMat.ID_Mat AND WorkMat.ID_Work = 2;
Записан
Joiner
Интересующийся

ru
Offline Offline

« Ответ #16 : 20-09-2016 12:41 » 

Тогда я получу, только записи, где ID_Work=2. А мне нужно, чтобы таблица Mat была вся прився прився. Поэтому я и использую внешнее присоединение  LEFT JOIN. Это еще называется горизонтальное объединение, когда к одной таблице подцепляются столбцы из другой таблицы по общему ключу. Только нужно из второй таблицы отфильтровать записи, что бы ID_Work=2.

Добавлено через 14 минут и 43 секунды:
Суть в том, что таблицу связей можно реализовать по разному, например, сделать два столбца, один с ID работы, а другой типа строки переменной длины и туда записывать перечень ID материалов. Можно, вместо строки использовать BLOB - двоичные массивы, можно пробовать двумерный массив... Эти детали реализации важны для правильного понимания и соответственно для правильного составления запроса.
Не знаю про Access, а так в SQL можно использовать временные переменные и временные таблицы для хранения промежуточного результата. В некоторых версиях доступен вложенный запрос. Существует работа со строками.
Может быть вообще пересмотреть систему - добавить столбцы флагов материала в таблицу работ или строку с перечислением ID материалов. Соответственно, программа запрашивает у БД перечень материалов для конкретного вида работ, потом формирует запрос на материалы. "Короткий путь - чаще тот, который знаешь."

Тут дело не в Access, я просто использую его как базу данных для программы. Я уже в принципе подумывал, чтобы делать запрос с фильтром, записать результат во временную таблицу, а потом уже присоединять эту временную таблицу. В этих таблицах будет строк по 500. Это не беда. А вот дальше будет продолжение программы, где таких строк в других таблицах будет много. А нужно будет сделать похожие запросы.  Очень не хочется городить такой огород. Как вариант можно вообще сбросить все в listbox и там отсортировать и отфильтровать. Но думаю есть нормальное решение одним запросом и все.
« Последнее редактирование: 20-09-2016 12:56 от Joiner » Записан
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #17 : 20-09-2016 13:55 » 

Joiner, т.е. ты хочешь получить именно Mat и подтянуть строки одной работы?

FROM Mat m LEFT JOIN WorkMat wm ON wm.ID_Mat = m.ID_Mat AND wm.ID_Work = 2
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Joiner
Интересующийся

ru
Offline Offline

« Ответ #18 : 20-09-2016 13:59 » 

Joiner, т.е. ты хочешь получить именно Mat и подтянуть строки одной работы?

FROM Mat m LEFT JOIN WorkMat wm ON wm.ID_Mat = m.ID_Mat AND wm.ID_Work = 2


такая конструкция "ON wm.ID_Mat = m.ID_Mat AND wm.ID_Work = 2" не прокатывает я пробовал. Ругается что нельзя в JOIN использовать объединение.

Добавлено через 2 минуты и 1 секунду:
Но слава богу хоть нашелся человек, который понял суть.
« Последнее редактирование: 20-09-2016 14:01 от Joiner » Записан
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #19 : 20-09-2016 16:54 » 

Просто у тебя необычный подход.

Ты борешься не с SQL, а с Access. Смотри его доку, что он позволяет, а что нет. Если не позволяет задать wm.ID_Work = 2 в LEFT JOIN, попробуй через подзапрос (если Access это умеет):

FROM Mat m LEFT JOIN (SELECT * FROM WorkMat WHERE wm.ID_Work = 2) wm ON wm.ID_Mat = m.ID_Mat

Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Joiner
Интересующийся

ru
Offline Offline

« Ответ #20 : 20-09-2016 17:04 » 

Так я пробовал. Access подзапросы поддерживает. Но в Access какая-то заморочка с алиасами. По мануалу вроде есть, но по другому объявляется, типа SELECT Mat AS M.  Предложенную Вами конструкцию должен проглатывать, но выдает ошибку в JOIN. Я решил, что что-то не так делаю. Спасибо за ответ.

Добавлено через 45 минут и 51 секунду:
Спасибо RXL

Код: (SQL)
SELECT Mat.*, WM.*
FROM Mat LEFT JOIN (SELECT WorkMat.* FROM WorkMat WHERE WorkMat.ID_Work=2) AS WM ON Mat.ID_Mat = WM.ID_Mat
ORDER BY Mat.[Name];

Вот такой запрос заработал.
« Последнее редактирование: 20-09-2016 17:50 от Joiner » Записан
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #21 : 21-09-2016 09:17 » 

AS для алиасов - штука стандартная, но необязательная в большинстве SQL-диалектов.
Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
HandKot
Молодой специалист

ru
Offline Offline

« Ответ #22 : 05-10-2016 10:06 » 

Код:
SELECT
SetMat.ID_Mat
, SetMat.[_Name]
, SetMat.[_UnitMat]
, SetMat.[_Price]
, SetMat.[_Group]
, SetMat.[_Free]
, SetMat.[_Select]
, SetWorkMat.[_Rashod]
, SetWorkMat.ID_Work
FROM
SetMat
INNER JOIN SetWorkMat ON SetMat.ID_Mat = SetWorkMat.ID_Mat
UNION ALL
SELECT
SetMat.ID_Mat
, SetMat.[_Name]
, SetMat.[_UnitMat]
, SetMat.[_Price]
, SetMat.[_Group]
, SetMat.[_Free]
, SetMat.[_Select]
, 0
, SetWork.ID_Work
FROM
SetWork, SetMat
WHERE
NOT EXISTS (SELECT * FROM SetWorkMat WHERE SetWorkMat.ID_Work = SetWork.ID_Work AND SetMat.ID_Mat = SetWorkMat.ID_Mat)
Записан

I Have Nine Lives You Have One Only
THINK!
Страниц: [1]   Вверх
  Печать  
 

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines