SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 27.02.2011 22:59:25

dimkainc
Участник
Откуда: Смоленск
Зарегистрирован: 27.02.2011
Сообщений: 8

Помогите составить запрос без комбинации всех возможных результатов.

Здравствуйте.

Бьюсь над запросом, но ни как не получается.


SELECT
  a.`WorkId` AS WorkId,
  b.`CompId` AS CompId,
  c.`DispId` AS DispId,
  d.`UserId` AS UserId,
  e.`PrintId` AS PrintId,
  f.`ScanId` AS ScanId,
  TRIM(IF(ISNULL(a.`NetName`), SPACE(60), a.`NetName`)) AS WS_NetName,
  TRIM(IF(ISNULL(a.`Position`), SPACE(240), a.`Position`)) AS WS_Position,
  TRIM(IF(ISNULL(a.`Comment`), SPACE(250), a.`Comment`)) AS WS_Comment,
  TRIM(IF(ISNULL(b.`Model`), SPACE(120), b.`Model`)) AS Cmp_Model,
  TRIM(IF(ISNULL(b.`SN`), SPACE(60), b.`SN`)) AS Cmp_SN,
  TRIM(IF(ISNULL(b.`PN`), SPACE(60), b.`PN`)) AS Cmp_PN,
  TRIM(IF(ISNULL(b.`MadeDate`), SPACE(10), b.`MadeDate`)) AS Cmp_Date,
  TRIM(IF(ISNULL(b.`IN`), SPACE(60), b.`IN`)) AS Cmp_IN,
  TRIM(IF(ISNULL(b.`Licens`), SPACE(60), b.`Licens`)) AS Cmp_Licens,
  TRIM(IF(ISNULL(b.`Comment`), SPACE(250), b.`Comment`)) AS Cmp_Comment,
  TRIM(IF(ISNULL(c.`Model`), SPACE(120), c.`Model`)) AS Ds_Model,
  TRIM(IF(ISNULL(c.`SN`), SPACE(60), c.`SN`)) AS Ds_SN,
  TRIM(IF(ISNULL(c.`PN`), SPACE(60), c.`PN`)) AS Ds_PN,
  TRIM(IF(ISNULL(c.`MadeDate`), SPACE(10), c.`MadeDate`)) AS Ds_Date,
  TRIM(IF(ISNULL(c.`Comment`), SPACE(250), c.`Comment`)) AS Ds_Comment,
  TRIM(IF(ISNULL(d.`Name`), SPACE(120), d.`Name`)) AS Us_Name,
  TRIM(IF(ISNULL(d.`Profession`), SPACE(120), d.`Profession`)) AS Us_Prof,
  TRIM(IF(ISNULL(d.`Phone`), SPACE(60), d.`Phone`)) AS Us_Phone,
  TRIM(IF(ISNULL(d.`Comment`), SPACE(250), d.`Comment`)) AS Us_Comment,
  TRIM(IF(ISNULL(e.`NetName`), SPACE(60), e.`NetName`)) AS Pr_NetName,
  TRIM(IF(ISNULL(e.`Model`), SPACE(120), e.`Model`)) AS Pr_Model,
  TRIM(IF(ISNULL(e.`SN`), SPACE(60), e.`SN`)) AS Pr_SN,
  TRIM(IF(ISNULL(e.`PN`), SPACE(60), e.`PN`)) AS Pr_PN,
  TRIM(IF(ISNULL(e.`MadeDate`), SPACE(10), e.`MadeDate`)) AS Pr_Date,
  TRIM(IF(ISNULL(e.`IN`), SPACE(60), e.`IN`)) AS Pr_IN,
  TRIM(IF(ISNULL(e.`Comment`), SPACE(250), e.`Comment`)) AS Pr_Comment,
  TRIM(IF(ISNULL(f.`Model`), SPACE(120), f.`Model`)) AS Sc_Model,
  TRIM(IF(ISNULL(f.`SN`), SPACE(60), f.`SN`)) AS Sc_SN,
  TRIM(IF(ISNULL(f.`PN`), SPACE(60), f.`PN`)) AS Sc_PN,
  TRIM(IF(ISNULL(f.`MadeDate`), SPACE(10), f.`MadeDate`)) AS Sc_Date,
  TRIM(IF(ISNULL(f.`IN`), SPACE(60), f.`IN`)) AS Sc_IN,
  TRIM(IF(ISNULL(f.`Comment`), SPACE(250), f.`Comment`)) AS Sc_Comment
 FROM `WorkState` AS a
  LEFT JOIN `Computers` AS b ON a.`WorkId` = b.`WorkId`
  LEFT JOIN `Display` AS c ON a.`WorkId` = c.`WorkId`
  LEFT JOIN `Users` AS d ON a.`WorkId` = d.`WorkId`
  LEFT JOIN `Printers` AS e ON a.`WorkId` = e.`WorkId`
  LEFT JOIN `Scanners` AS f ON a.`WorkId` = f.`WorkId`
 


Что мне нужно от него:
Получить выходную таблицу, где будут выведены все значения из таблиц, связанных по ключевому полю, но только каждое значение должно быть выведено в одном экземпляре и, желательно, чтобы в результате получилось наименьшее возможное количество строк.
Во вложенном файле я привёл для примера набор данных, что именно хочу получить, и что сейчас получаю. Так же привёл вариант с использованием union. К сожалению, union тоже меня не устраивает.
Простите, что вордовский документ, но более компактно несколько таблиц я не нашёл в чём представить.


Прикрепленные файлы:
Attachment Icon DataExample.rar, Размер: 12,682 байт, Скачано: 635

Неактивен

 

#2 27.02.2011 23:20:32

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Помогите составить запрос без комбинации всех возможных результатов.

oowriter 3.2 падает на этом файле wink

Попробуйте представить в виде нескольких текстовых файлов. Обнаружите, что
это дико удобно wink

Неактивен

 

#3 27.02.2011 23:36:21

dimkainc
Участник
Откуда: Смоленск
Зарегистрирован: 27.02.2011
Сообщений: 8

Re: Помогите составить запрос без комбинации всех возможных результатов.

Ну чтож. Если Вы считаете, что так удобнее, вот.

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

Отредактированно dimkainc (27.02.2011 23:38:18)


Прикрепленные файлы:
Attachment Icon DataExampleTxt.rar, Размер: 1,361 байт, Скачано: 622

Неактивен

 

#4 28.02.2011 00:30:55

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Помогите составить запрос без комбинации всех возможных результатов.

Хм. А Вы не пробовали пользоваться mysqldump для создания текстовых файлов?
Не нужно было бы тратить столько усилий Вам, и (с моей точки зрения — главное)
не нужно было бы тратить столько усилий мне, чтобы загрузить эти данные в тесто-
вую базу smile

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

Код:

Исходные данные:
WorkId  CompId  DispId  PrintId ScanId  UserId
1       1       1       пусто   1       1
1       1       1       пусто   1       2
1       1       1       пусто   1       3
1       1       2       пусто   1       1
1       1       2       пусто   1       2
1       1       2       пусто   1       3

Желаемое:
1       1       1       пусто   1       1
1       пусто   2       пусто   пусто   2
1       пусто   пусто   пусто   пусто   3

Альтернативное желаемое:
1       1       2       пусто   1       1
1       пусто   1       пусто   пусто   2
1       пусто   пусто   пусто   пусто   3

Я добавил раздел «альтернативное желаемое», которое точно так же следует из
тех же самых данных и полностью удовлетворяет Вашим требованиям желаемого.
Более того, этих альтернативных желаемых много. Именно поэтому Вам не удается
сформулировать запрос — его нельзя сформулировать.

Соответственно, решение — подумать, какую действительно задачку Вы решаете.
И решать реальную задачку, а не эту. Например, я допускаю, что Вас устроит
решение с GROUP_CONCAT по всем полям, кроме первого, и группировкой по первому
полю.

Неактивен

 

#5 28.02.2011 00:37:39

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Помогите составить запрос без комбинации всех возможных результатов.

Да, я придумал костыльное решение:

SELECT @CompId := 0, ...; -- инициализировать переменные
SELECT WorkId, IF(@CompId = CompId, NULL, @CompId := CompID) AS CompID, ... -- вывести данные

Оно будет работать в случае, если Вы будете всегда подсовывать независимые
таблицы (они же у Вас независимые!) в виде подзапросов с правильной сорти-
ровкой (т.е. FROM ... JOIN (SELECT CompId FROM Computers WHERE WorkId = WorkState.WorkId ORDER BY CompId) AS Computers)

Но это правда костыль над костылем и костылем погоняет. Подумайте лучше над
правильной формулировкой решаемой задачи.

Неактивен

 

#6 28.02.2011 03:46:50

Tsvetkov
Участник
Зарегистрирован: 08.01.2011
Сообщений: 14

Re: Помогите составить запрос без комбинации всех возможных результатов.

dimkainc - Вы не правильно нарисовали связи. Все связи привязаны к рабочему месту(к одной точке объединения).
А должно быть в виде дерева.

Неактивен

 

#7 28.02.2011 06:22:35

dimkainc
Участник
Откуда: Смоленск
Зарегистрирован: 27.02.2011
Сообщений: 8

Re: Помогите составить запрос без комбинации всех возможных результатов.

Эх. Спасибо за разъяснения. К сожалению исходные данные и способ привязки уже не изменить.
Что касается исходных данных, то их столько, что тот запрос, который приведен в начале выдаёт 15 миллионов строк, в то время как общее число строк должно получиться порядка 300. Соответственно я не пытался из выгружать, а привёл для наглядности упрощённый вариант.
Костыль, о котором Вы говорите, наверно будет работать по времени так же, как и текущий запрос, то есть более 15 минут на получение результата.
Да, меня бы устроил любой альтернативный вариант, но, видимо без связки sql+внешний модуль обработки результата не обойтись.

Неактивен

 

#8 28.02.2011 06:34:08

dimkainc
Участник
Откуда: Смоленск
Зарегистрирован: 27.02.2011
Сообщений: 8

Re: Помогите составить запрос без комбинации всех возможных результатов.

Tsvetkov написал:

dimkainc - Вы не правильно нарисовали связи. Все связи привязаны к рабочему месту(к одной точке объединения).
А должно быть в виде дерева.

А что делать, если так оно и есть. Любое оборудование может быть установлено на конкретное рабочее место, и от замены оборудования, работоспособность за рабочим местом не изменится. То же самое и про пользователей - они работают в разные смены, но за одним рабочим местом. Нет ни чего закреплённого друг за другом, а только за этим самым рабочим местом. Соответсвенно резерв хранится на складе и, по мере поломок, со склада берут замену.

Неактивен

 

#9 28.02.2011 08:05:28

dimkainc
Участник
Откуда: Смоленск
Зарегистрирован: 27.02.2011
Сообщений: 8

Re: Помогите составить запрос без комбинации всех возможных результатов.

Хм... в принципе вариант:


WorkId  CompId  DispId  PrintId ScanId  UserId
1       1       1,2     пусто   1       1,2,3
 

меня вполне бы устроил...

Отредактированно dimkainc (28.02.2011 08:06:36)

Неактивен

 

#10 28.02.2011 10:58:01

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Помогите составить запрос без комбинации всех возможных результатов.

Ну так и группируйте тогда? smile

Неактивен

 

#11 28.02.2011 12:06:01

Tsvetkov
Участник
Зарегистрирован: 08.01.2011
Сообщений: 14

Re: Помогите составить запрос без комбинации всех возможных результатов.

dimkainc написал:

Tsvetkov написал:

dimkainc - Вы не правильно нарисовали связи. Все связи привязаны к рабочему месту(к одной точке объединения).
А должно быть в виде дерева.

А что делать, если так оно и есть. Любое оборудование может быть установлено на конкретное рабочее место, и от замены оборудования, работоспособность за рабочим местом не изменится. То же самое и про пользователей - они работают в разные смены, но за одним рабочим местом. Нет ни чего закреплённого друг за другом, а только за этим самым рабочим местом. Соответсвенно резерв хранится на складе и, по мере поломок, со склада берут замену.

Вы подключаете монитор к компьютеру , а не к рабочему месту. И когда Вы производите
соединение через WorkState  таблици Computers и Display,  то происходит перемножение.
И вместо 2-х записей получаете 4 записи для 1 рабочего места.

Т.е. неправильно спроектировали базу и из-за этого происходят проблемы .

Неактивен

 

#12 28.02.2011 15:41:50

dimkainc
Участник
Откуда: Смоленск
Зарегистрирован: 27.02.2011
Сообщений: 8

Re: Помогите составить запрос без комбинации всех возможных результатов.

Tsvetkov написал:

Вы подключаете монитор к компьютеру , а не к рабочему месту. И когда Вы производите
соединение через WorkState  таблици Computers и Display,  то происходит перемножение.
И вместо 2-х записей получаете 4 записи для 1 рабочего места.

Т.е. неправильно спроектировали базу и из-за этого происходят проблемы .

На рабочих местах и стоит по одному компьютеру, и одному дисплею или двум дисплеям. Даже если есть один компьютер и 2 дисплея, я получаю две записи, а не 4, как Вы, уважаемый, утверждаете. Но комбинации начинаются тогда, когда не один тип устройства, а несколько разных могут быть использованы с одним компьютером.
- К коммпьютеру можно подключить 2 дисплея, или по вашему нельзя?
- К компьютеру можно подключить 2 и более принтеров, сканеров, или опять таки нельзя?
- За одним компьютером могут работать 2 и более человека в разное время, или опять скажете, что нельзя?

Теперь рассмотрите следующие комбинации:
- компьютер, первый дисплей, пользователь 1,
- компьютер, первый дисплей, пользователь 2,
- компьютер, второй дисплей, пользователь 1,
- компьютер, второй дисплей, пользователь 2
Хотите строго указать каждому из пользователей пользоваться только "своим" дисплеем?

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

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

К сожалению GROUP_CONCAT в купе с DISTINCT не совсем правильные даёт результаты для не ключевых полей.
Я остановился на использовании UNION, как самом быстром получении данных и последующей обработкой результата вне SQL.

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

Спасибо, большое, за участе!

Отредактированно dimkainc (28.02.2011 17:14:36)

Неактивен

 

#13 28.02.2011 16:56:55

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Помогите составить запрос без комбинации всех возможных результатов.

Они не лишние, они единственно разумные ведь? Если монитор привязан к рабочему
месту 1, а мышка привязана к тому же рабочему месту, то она привязана к рабочему
месту 1 smile

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson