Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) 
Соотечественники, помогите разобраться.

Вопрос: как убрать многократный join по одной и той же таблице исходя из нижеперечисленных условий?

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

Например, есть сущности
WORK (id int, wrkname varchar(100), wrktype varchar(100), price float)
USERFLDTYPE(id,  tablename varchar(100), typename varchar(100))
USERFLD(id int, userfld_type_id int, fld_value varchar(100),fk_work_id int).

Пользователь видит значения этой таблицы + пользовательские поля и значения к ним:
idwrknamepricefld_valuefld_value2fld_value3
1Погрузка1001.10СтроительствоКомплекс 1
2Расстановка товара4002.09СтроительствоКомплекс 1
3Разгрузка на машине5003.1СтроительствоКомплекс 2

И т.д.
Fld_value,Fld_value2,Fld_value3 - берутся из таблицы USERFLD.

САБЖ состоит в следующем:

SELECT w.*,uf1.fld_value,uf2.fld_value,uf3.fls_value
FROM WORK w
  LEFT JOIN USERFLDTYPE uft1 on uft1.tablename = 'WORK' and uft1.id = 3  
  LEFT JOIN USERFLD uf1 on uf1.userlfd_type_id = uft1.id and uf1.fk_work_id = w.id
  LEFT JOIN USERFLDTYPE uft2 on uft2.tablename = 'WORK' and uft2.id = 5  
  LEFT JOIN USERFLD uf2 on uf2.userlfd_type_id = uft2.id and uf2.fk_work_id = w.id
  LEFT JOIN USERFLDTYPE uft3 on uft3.tablename = 'WORK' and uft3.id = 6  
  LEFT JOIN USERFLD uf3 on uf3.userlfd_type_id = uft3.id and uf3.fk_work_id = w.id
WHERE wrktype = 'ABC'


Это был упрощенный вариант. В реале - для каждой сущности в БД есть соотв записи в USERFLD и USERFLDTYPE. Таких сущностей около 20 ну и есть еще одна промежуточная таблица, но это сейчас не суть.

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

Сообщение было отредактировано: 1 ноя 11, 13:36
1 ноя 11, 13:31    [11532563]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
Glory
Member

Откуда:
Сообщений: 104751
notricky
Вопрос все тот же - можно ли избавиться от многократных джойнов (и наверное также без подзапросов) и использовать например группировку или что то еще. Поможете?

Не делать pivot - не будет и джойнов
1 ноя 11, 13:34    [11532589]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
Glory
notricky
Вопрос все тот же - можно ли избавиться от многократных джойнов (и наверное также без подзапросов) и использовать например группировку или что то еще. Поможете?

Не делать pivot - не будет и джойнов

А почитать первый пост более внимательно, особенно то, что подчеркнуто?

Какие-то идеи по существу?
1 ноя 11, 13:46    [11532696]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SELECT w.id,w.wrkname,w.wrktype,w.price,
 MIN(CASE uft.id WHEN 3 THEN uf.fld_value END),
 MIN(CASE uft.id WHEN 5 THEN uf.fld_value END),
 MIN(CASE uft.id WHEN 6 THEN uf.fld_value END)
FROM WORK w
 LEFT JOIN
  USERFLDTYPE uft JOIN USERFLD uf ON uft.id=uf.userlfd_type_id
 ON w.id=uf.fk_work_id AND uft.tablename='WORK' AND uft.id IN(3,5,6)
WHERE w.wrktype='ABC'
GROUP BY w.id,w.wrkname,w.wrktype,w.price;
1 ноя 11, 14:00    [11532835]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
Glory
Member

Откуда:
Сообщений: 104751
notricky
А почитать первый пост более внимательно, особенно то, что подчеркнуто?

Какие-то идеи по существу?

Удивляют такие запросы. Как бы получить что-то, ничего не делая
1 ноя 11, 14:08    [11532921]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
_anybody
Guest
А чем в данной ситуации Вас не устраивают JOIN-ы?
Если хотите, то можете использовать PIVOT + DYNAMIC SQL, хотя это ничуть не лучше.
1 ноя 11, 14:10    [11532933]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
iap
SELECT w.id,w.wrkname,w.wrktype,w.price,
 MIN(CASE uft.id WHEN 3 THEN uf.fld_value END),
 MIN(CASE uft.id WHEN 5 THEN uf.fld_value END),
 MIN(CASE uft.id WHEN 6 THEN uf.fld_value END)
FROM WORK w
 LEFT JOIN
  USERFLDTYPE uft JOIN USERFLD uf ON uft.id=uf.userlfd_type_id
 ON w.id=uf.fk_work_id AND uft.tablename='WORK' AND uft.id IN(3,5,6)
WHERE w.wrktype='ABC'
GROUP BY w.id,w.wrkname,w.wrktype,w.price;


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

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

_anybody , у меня есть некоторое подозрение (возможно не оправданное), что многократное "пробегание" по той же таблице не равно выгрузке общего набора параметров и значений для таблицы и дальнешее разбрасывание по колонкам. (ну вот так если криво изъясняться).
1 ноя 11, 14:21    [11533057]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
notricky,

при правильных индексах JOINы - это то, что надо.
1 ноя 11, 14:23    [11533076]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
notricky,

при правильных индексах JOINы - это то, что надо.
Правда, нам неизвестно, какое отношение между WORK и USERFLD.
Если один-ко-многим, то результаты будут разными.
1 ноя 11, 14:24    [11533100]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
iap
notricky,

при правильных индексах JOINы - это то, что надо.
Правда, нам неизвестно, какое отношение между WORK и USERFLD.
Если один-ко-многим, то результаты будут разными.
То есть, я имел в виду, fk_work_id - уникально, например?
1 ноя 11, 14:26    [11533120]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
iap
iap
пропущено...
Правда, нам неизвестно, какое отношение между WORK и USERFLD.
Если один-ко-многим, то результаты будут разными.
То есть, я имел в виду, fk_work_id - уникально, например?


Это я привел частный случай. На деле же есть просто Fk_id. и к нему приравнивается primary key из любой таблицы. Составной ключ = table_name + fk_id
1 ноя 11, 15:09    [11533545]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
iap, кстати я не совсем понял, а зачем использовать MIN?
1 ноя 11, 15:23    [11533669]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
notricky
iap, кстати я не совсем понял, а зачем использовать MIN?
Уберите MIN и GROUP BY и посмотрите.
1 ноя 11, 15:45    [11533899]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
iap
notricky
iap, кстати я не совсем понял, а зачем использовать MIN?
Уберите MIN и GROUP BY и посмотрите.
Это опытным путем. А теоретическое объяснение?
К тому же для этих целей можно и MAX использовать?
1 ноя 11, 16:09    [11534078]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
notricky
iap
пропущено...
Уберите MIN и GROUP BY и посмотрите.
Это опытным путем. А теоретическое объяснение?
К тому же для этих целей можно и MAX использовать?
Если значение одно, то и AVG тоже!
В последних версиях имеется убогий аналог такой конструкции - называется PIVOT.

В общем, главная идея - обеспечить, чтобы все строки с нужными значениями попали в резалтсет,
а уже в полях SELECTа ненужные значения просто игнорируются.
Агрегирование позволяет объединить значения в разных колонках, соответствующие одной и той же
комбинации группирующих полей, в одну строку.
1 ноя 11, 16:16    [11534139]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
iap
notricky
пропущено...
Это опытным путем. А теоретическое объяснение?
К тому же для этих целей можно и MAX использовать?
Если значение одно, то и AVG тоже!
В последних версиях имеется убогий аналог такой конструкции - называется PIVOT.

В общем, главная идея - обеспечить, чтобы все строки с нужными значениями попали в резалтсет,
а уже в полях SELECTа ненужные значения просто игнорируются.
Агрегирование позволяет объединить значения в разных колонках, соответствующие одной и той же
комбинации группирующих полей, в одну строку.

Хорошо сказано, главное понятно! За это отдельное спасибо и плюсик в карму.
Еще года три назад я бы такое предложение точно не вкурил. То есть без агрегации мы получим ровно столько же строк, сколько бы с одним джойном по таблице и указанием в условии where uft.id in (3,5,6)?
2 ноя 11, 10:26    [11537272]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
notricky
iap
SELECT w.id,w.wrkname,w.wrktype,w.price,
 MIN(CASE uft.id WHEN 3 THEN uf.fld_value END),
 MIN(CASE uft.id WHEN 5 THEN uf.fld_value END),
 MIN(CASE uft.id WHEN 6 THEN uf.fld_value END)
FROM WORK w
 LEFT JOIN
  USERFLDTYPE uft JOIN USERFLD uf ON uft.id=uf.userlfd_type_id
 ON w.id=uf.fk_work_id AND uft.tablename='WORK' AND uft.id IN(3,5,6)
WHERE w.wrktype='ABC'
GROUP BY w.id,w.wrkname,w.wrktype,w.price;


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

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

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

Вот тут возник вопрос такой - отсутствие значения в UserFLD для uft.id=6 - является критерием для отбора в результирующий набор. Записи, у которых есть значения в таблице USERFLD для uft.id=6 - не должны попасть в отбор.
Как изменить условие в запросе? Чую надо прописать как-то хитро where - но пока не получается.
2 ноя 11, 11:14    [11537589]     Ответить | Цитировать Сообщить модератору
 Re: Многократный join по той же таблице  [new]
notricky
Member

Откуда:
Сообщений: 413
notricky
notricky
пропущено...


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

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

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

Вот тут возник вопрос такой - отсутствие значения в UserFLD для uft.id=6 - является критерием для отбора в результирующий набор. Записи, у которых есть значения в таблице USERFLD для uft.id=6 - не должны попасть в отбор.
Как изменить условие в запросе? Чую надо прописать как-то хитро where - но пока не получается.

Сделал пока так:
SELECT w.id,w.wrkname,w.wrktype,w.price,
 MIN(CASE uft.id WHEN 3 THEN uf.fld_value END),
 MIN(CASE uft.id WHEN 5 THEN uf.fld_value END),
 MIN(CASE uft.id WHEN 6 THEN uf.fld_value END)  -- т.к. это признак, то наверное вообще не выводить в селект???
FROM WORK w
 LEFT JOIN USERFLDTYPE uft 
   JOIN USERFLD uf ON uft.id = uf.userlfd_type_id 
 ON w.id=uf.fk_work_id AND uft.tablename='WORK' AND uft.id IN (3,5,6)
WHERE w.wrktype='ABC' and ((uft.id <> 6) or (uf.fld_value is null and uft.id = 6))
GROUP BY w.id,w.wrkname,w.wrktype,w.price;
Как лучше это сделать?
2 ноя 11, 11:25    [11537656]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить