OFF: Эмуляция иерархии #422509


#0 by Ненавижу 1С
есть табличка, типа есть группы в них группы или товары (как иерархический Справочник в 1С): CREATE TABLE GOOD ( //товары       ID Integer NOT NULL, //первичный ключ       LEFTBOUND Integer NOT NULL,       RIGHTBOUND Integer NOT NULL,       PARENT Integer,//сылка на папку владельца, для первого уровня NULL       NAME Varchar NOT NULL, //наименование группы или товара       ISGROUP Integer NOT NULL //признак группы: 0,1 ); правило: если товар А лежит в группе Б а та в группе С то: C.LEFTBOUND<B.LEFTBOUND<A.LEFTBOUND=A.RIGHTBOUND<=B.RIGHTBOUND<=C.RIGHTBOUND то есть диапозон LEFTBOUND ... RIGHTBOUND групп охватывает вложенные записи (групп и товаров) для товаров (не групп) верно: LEFTBOUND=RIGHTBOUND требуется написать запрос, упорядочивающий записи по иерархии, а среди записей одного уровня должны вначале идти группы (в 1С "группы сверху"), например: Группа 1  Группа 1.1   Товар 1.1.1   Товар 1.1.2  Товар 1.2  Товар 1.3 Группа 2  Группа 2.1  Группа 2.2 смог построить запрос по-иерархии, но не добился чтобы группы в одном уровне были сверху: select g.ID, g.Name, g.ISGROUP, count(g2.ID) as glevel from GOOD g left join GOOD g2 on (g.LEFTBOUND between g2.LEFTBOUND and g2.RIGHTBOUND) group by g.ID, g.Name, g.ISGROUP, g.LEFTBOUND order by g.LEFTBOUND поле glevel отвечает за уровень вложенности записи
#1 by Ненавижу 1С
UP, что ж я в LIFE то поместил? можно на ИТ исправить?
#2 by Ненавижу 1С
выход видится один - при вставке новых групп анализировать и сдвигать LEVELBOUND вниз у элементов-не-групп
#3 by AquaKosh
А такая конструкция не канает: "order by g.LEFTBOUND, g.ISGROUP"?
#4 by Ненавижу 1С
не канает, сортировка по полю ISGROUP не работает, так как LEFTBOUND фактически уникальным получается
#5 by Ненавижу 1С
облом, внутри одной группы нужно сортировать более хитро: сначала группы потом остальные записи, но и те и те по алфавиту
#6 by Жан Пердежон
а что тут хитрого? order by isgroup desc, name
#7 by Ненавижу 1С
Up Если бы можно было это реализовать, то запросы с иерархией работали бы в такой структуре быстрее. Запись конечно была бы медленнее, но справочники не оперативная информация
#8 by Ненавижу 1С
получим сначала все группы (без учета иерархии), потом элементы, а нужна иерархия также
#9 by Жан Пердежон
сделай код составной и по нему сортируй или поле Lvl - уровень вложенности) а вообще про иерархические структуы и sql дофига инфы в нете
#10 by Ненавижу 1С
составной код ничем не лучше LEFTBOUND Lvl я тоже могу вычислить без труда, смотри в glevel
#11 by Ненавижу 1С
В общем задача такая: какая наиболее оптимальная структура таблицы справочника, чтобы наиболее просто получать иерархический список (упорядоченность по иерархии и доп. полю, например наименованию или признаку группы + наименованию) Соответственно, достаточно просто получать агрегатные функции из других таблиц с учетом иерархии. Достаточно просто - это одним запросом и без рекурсии хранимых процедур.
#12 by Ненавижу 1С
Когда в практике ты "ноль" можно по теоретизировать. Итак, пусть есть таблица представляющая собой некое множество записей, на котором хотим задать отношение полного порядка (а попросту, упорядочить каким-то способом). Обозначим A>>B если в нашем упорядочивании запись A идет раньше B. Введем функцию r(A,B) =1, если A>>B и =0  иначе. Очевидно, что r(A,A)=0; r(A,B)=1-r(B,A); если r(A,B)=r(B,C)=1 тогда r(A,С)=1. Тогда функция R(A)=Sum(r(A,B),B-пробегает все множество) принимает различные значения 0,1,...,Count(*)-1 и задает тот же порядок на целых числах. Переводя в SQL, получаем запрос "универсального" упорядочивания: SELECT A.Х FROM T as A LEFT JOIN T as B on (1=1) GROUP BY A.X ORDER BY SUM(r(A,B)) DESC --запись формальная естественно или тоже самое SELECT A.Х FROM T as A LEFT JOIN T as B on (r(A,B)=1) GROUP BY A.X ORDER BY COUNT(B.Х) DESC естественно, не стоит применять такой запрос при простом упорядочивании. Я вообще не знаю где его можно применить ))). Вернемся к текущей задаче. Необходимо упорядочить множество по-иерархии (обозначим >>) и внутри одной ветки иерархии по другому признаку, например паре IsGroup,Name (обозначим >). Итак, для каждой записи можно указать "родительскую", на которую ссылается Parent. Для каждой записи A можно записать родительский ряд: A=A0<<A1<<...<<An=NULL где Ai.Parent = A(i+1). Для пары (A,B) минимальным предком X назовем запись наибольшего уровня вложенности, одновременно встречающееся в родительских рядах A и B (возможно NULL). Если A и B не расположены в одном родительском ряду, то минимальные индивидуальные родители относительно A и B это элементы в родительских рядах An<<X и Bm<<X (предшествующие X непосредственно). Тогда введенная ранее функция r(A,B) равна 1 тогда и только тогда, когда: 1. B!=A и A лежит в родительском ряду B. либо 2. An>Bm (верно второе упорядочивание). Как видно из написанного нам понадобится использование LEFTBOUND и RIGHTBOUND, но придется использовать многократное соединение таблицы самой с собой. А именно: найти минимального предка, найти индивидуальных предков и сравнить их. Но, зачастую, практика это не теория, надеюсь на более простое решение задачи.
#13 by Ненавижу 1С
Надеюсь на то, что Гуру SQL откликнуться. Смотрел профайлером запрос генерируемый 1С, но видимо 1С потом "корректирует" полученный SQL запрос: ВЫБРАТЬ    Тест.Наименование КАК Наименование ИЗ    Справочник.Тест КАК Тест УПОРЯДОЧИТЬ ПО    Тест.Ссылка ИЕРАРХИЯ,    Тест.ЭтоГруппа,    Наименование АВТОУПОРЯДОЧИВАНИЕ превращается в: SELECT _Reference19_Q_000_T_001._Description AS _sf_4, _Reference19_Q_000_T_001._IDRRef AS _sf_2RRef, _Reference19_Q_000_T_001._ParentIDRRef AS _sf_1RRef, CASE WHEN NOT _Reference19_Q_000_T_001._Folder = 0x01 THEN 0x01 WHEN _Reference19_Q_000_T_001._Folder = 0x01 THEN 0x00 END AS _sf_3 FROM _Reference19 _Reference19_Q_000_T_001 WITH(NOLOCK) ORDER BY _Reference19_Q_000_T_001._ParentIDRRef, _Reference19_Q_000_T_001._IDRRef, CASE WHEN NOT _Reference19_Q_000_T_001._Folder = 0x01 THEN 0x01 WHEN _Reference19_Q_000_T_001._Folder = 0x01 THEN 0x00 END, _Reference19_Q_000_T_001._Description ну и, естественно, выполнив его на SQL сервере получаем несколько не то
#14 by Ненавижу 1С
н-да, без специфики серверов, то есть максимально переносимо между СУБД вышло вот так: SELECT T.ID1 ID ,T.NAME1 NAME, COUNT(DISTINCT GLEV.ID) LEV FROM (SELECT G1.ID ID1, G2.ID ID2, MAX(MP.LEFTBOUND) MPL, MIN(MP.RIGHTBOUND) MPR, G1.LEFTBOUND GL1, G2.LEFTBOUND GL2, G1.NAME NAME1 FROM GOOD G1 LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID) LEFT JOIN GOOD MP ON ((G1.LEFTBOUND BETWEEN MP.LEFTBOUND AND MP.RIGHTBOUND) AND (G2.LEFTBOUND BETWEEN MP.LEFTBOUND AND MP.RIGHTBOUND)) GROUP BY G1.ID, G2.ID, G1.LEFTBOUND, G2.LEFTBOUND, G1.NAME) T LEFT JOIN GOOD MP0 ON (MP0.LEFTBOUND = T.MPL) LEFT JOIN GOOD GI1 ON ((GI1.PARENT = MP0.ID) OR ((MP0.ID IS NULL) AND (GI1.PARENT IS NULL))) AND (T.GL1 BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND) LEFT JOIN GOOD GI2 ON ((GI2.PARENT = MP0.ID) OR ((MP0.ID IS NULL) AND (GI2.PARENT IS NULL))) AND (T.GL2 BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND) LEFT JOIN GOOD GLEV ON (T.GL1 BETWEEN GLEV.LEFTBOUND AND GLEV.RIGHTBOUND) GROUP BY T.ID1, T.NAME1 ORDER BY      SUM(      CASE        WHEN T.ID1=MP0.ID THEN 1        WHEN T.ID2=MP0.ID THEN 0        WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1        WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0                WHEN (GI1.NAME<GI2.NAME) THEN 1                ELSE 0      END) DESC убого, конечно интересно насколько хуже рекурсии?
#15 by Ненавижу 1С
жаль, что получился монолог потока сознания
#16 by Ненавижу 1С
+ поправить упорядочивание на: ORDER BY      COUNT( DISTINCT      CASE        WHEN T.ID1=MP0.ID THEN ID2        WHEN T.ID2=MP0.ID THEN NULL        WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN ID2        WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN NULL                WHEN (GI1.NAME<GI2.NAME) THEN ID2                ELSE NULL      END)
#17 by Ненавижу 1С
улучшил: SELECT G1.ID ID ,G1.NAME NAME,      SUM(      CASE WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 1           ELSE 0      END) LEV FROM GOOD G1 LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID) LEFT JOIN GOOD GI1 ON (G1.LEFTBOUND BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND) LEFT JOIN GOOD GI2 ON (G2.LEFTBOUND BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND) WHERE     ((GI1.PARENT=GI2.PARENT) OR ((GI1.PARENT IS NULL)AND(GI2.PARENT IS NULL))) AND     (NOT(GI1.ID=GI2.ID) OR (G1.ID=GI1.ID) OR (G2.ID=GI2.ID))   GROUP BY G1.ID, G1.NAME ORDER BY     SUM(     CASE       WHEN G2.LEFTBOUND BETWEEN G1.LEFTBOUND AND G1.RIGHTBOUND THEN 1       WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 0       WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1       WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0               WHEN (GI1.NAME<GI2.NAME) THEN 1               ELSE 0     END) DESC
#18 by Ненавижу 1С
Наконец, допустим есть таблица продаж (очень упрощенно) CREATE TABLE SALE (       ID Integer NOT NULL,       GOOD Integer NOT NULL,       ACOUNT Numeric(15,3) NOT NULL,       CONSTRAINT PK_SALE PRIMARY KEY (ID) ); ALTER TABLE SALE ADD CONSTRAINT FK_SALE_1 FOREIGN KEY (GOOD) REFERENCES GOOD(ID); И нам необходимо вывести продаваемые продукты по-иерархии с требуемой сортировкой: SELECT T.ID ID ,T.NAME NAME, T.LEV LEV, SUM(S.ACOUNT) ACOUNT FROM SALE S INNER JOIN GOOD G ON (S.GOOD=G.ID) INNER JOIN (SELECT G1.ID ID ,G1.NAME NAME, G1.LEFTBOUND LEFTBOUND, G1.RIGHTBOUND RIGHTBOUND,      SUM(      CASE WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 1           ELSE 0      END) LEV,     SUM(     CASE       WHEN G2.LEFTBOUND BETWEEN G1.LEFTBOUND AND G1.RIGHTBOUND THEN 1       WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 0       WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1       WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0               WHEN (GI1.NAME<GI2.NAME) THEN 1               ELSE 0     END) ORD FROM GOOD G1 LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID) LEFT JOIN GOOD GI1 ON (G1.LEFTBOUND BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND) LEFT JOIN GOOD GI2 ON (G2.LEFTBOUND BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND) WHERE     ((GI1.PARENT=GI2.PARENT) OR ((GI1.PARENT IS NULL)AND(GI2.PARENT IS NULL))) AND     (NOT(GI1.ID=GI2.ID) OR (G1.ID=GI1.ID) OR (G2.ID=GI2.ID))   GROUP BY G1.ID, G1.NAME, G1.LEFTBOUND, G1.RIGHTBOUND) T      ON ((G.ID=T.ID) OR (G.LEFTBOUND BETWEEN T.LEFTBOUND AND T.RIGHTBOUND)) GROUP BY T.ID,T.NAME,T.LEV, T.ORD       ORDER BY T.ORD DESC
#19 by Ненавижу 1С
апну, вдруг кто-то посмотрит свежим взглядом
Тэги:
Ответить:
Комментарии доступны только авторизированным пользователям

В этой группе 1С