T-SQL, йерархии за сортиране
Представете си, че трябва да представите информацията за определена йерархия по сортиран начин. Трябва да поставите родителските елементи преди децата. Също така се нуждаете от контрол върху реда на поставяне на елементи, които са на едно и също ниво. За да създадете тестови данни, използвайте следния код, който създава и попълва таблица с име dbo.Employees (да не се бърка със съществуващата таблица HR.Employees, която съхранява други данни):
Да кажем, че служителите трябва да бъдат представени в йерархичен ред - мениджърът преди подчинените, както и да подредите служителите в реда на empname. Два инструмента могат да се използват за изпълнение на тази задача: функцията ROW_NUMBER и рекурсивният CTE израз. Първо, дефинираме обикновен CTE, наречен EmpsRN, където атрибут, наречен n, се изчислява, представляващ номера на реда, разделен от mgrid и подреден от empname и empid (empid се добавя, ако е необходимо за детерминизъм):
След това се дефинира рекурсивен CTE, наречен EmpsPath, който итеративно изброява служителите едно по едно ниво, започвайки от корена (CEO) и преминавайки нагоре в йерархичната структура на организацията. Можете да изградите двоичен път за всеки служител, който започва с празен път в основата и свързва пътя на мениджъра с двоичния формуляр n (номер на ред) на всяко ниво на отчитане. Обърнете внимание, че минимизирането на размера на пътя изисква толкова байтове, колкото да покрие максималния брой директни доклади, които един мениджър има. Например, ако броят на директните подчинени устройства не надвишава 255, един байт е достатъчен, два байта поддържат до 32 767 директни подчинени устройства и т.н. Да кажем, че в нашия случай имаме нужда от два байта. Можете също да изчислите нивото на служител вдърво (разстояние от корена) чрез присвояване на стойност 0 на корена и добавяне на 1 на всяко ниво.Ето кодът, който изчислява както пътя за сортиране, така и нивото:
Също така трябва да се уверите, че служителите се показват в правилния ред, за което трябва да сортирате по пътя на сортиране. Можете също така да направите отстъп на нивото на служителя в йерархията, като повторите низа n пъти. Ето кода за завършеното решение:
Вижте как резултатът е, че мениджърът винаги е поставен преди подчинените, а служителите на същото ниво са подредени по empname:
Ако се изисква различно подреждане на едно ниво, да речем по заплата, просто променете съответно клаузата за подреждане на прозореца във функцията ROW_NUMBER:
Ето резултата от това запитване:
Красотата на функциите на прозореца никога не спира да ме учудва. Те са предназначени да се справят с някои от недостатъците на по-традиционните SQL конструкции и се поддават добре на оптимизация. В този урок сте виждали отново и отново, че функциите на прозореца могат да се използват за елегантно и ефективно решаване на огромен брой проблеми. Надявам се, че това е само началото и ще намерите свои собствени интересни и изобретателни начини за използване на функциите на прозореца.
Стандартът SQL набляга много на прозоречните функции и в резултат на това в него се появяват все повече функции и техните възможности. Microsoft положи много усилия за прилагане на липсващата преди това поддръжка за прозоречни функции в SQL Server 2012 и мисля, че това ще направи много неща много по-ефективни. Много се надявам, че компанията ще продължи да работи по внедряването на стандарта и поддръжката за други функции на прозореца ще се появи в новите версии на SQL Server.